lookup help

[Kieren20101]

Hi All,

New to the lookup formula and I'm trying to copy data from sheet 2 to sheet 1.  I know how to use the lookup function but I'm struggling with the Table/lookup array & return array. For the table/lookup array I'm wanting to look at column B:B & J:J with it returning the value from either column A:A or I:I from sheet 2.

Is this even the right formula to use?

Thanks

kieren

[#2]

Hello @Kieren20101 

lookup function couldn't handle multiple criteria for the lookup array. You can use a combination of the INDEX and MATCH function for that.
Here's an example formula that you can adjust to your needs:

=INDEX(Sheet2!A:A, MATCH(1, (YourLookupValue1=Sheet2!B:B) * (YourLookupValue2=Sheet2!J:J), 0))

in this formula:

• Sheet2!A:A is the range where you want to return a value from.
• YourLookupValue1 is the value you’re looking for in column B:B on Sheet 2.
• YourLookupValue2 is the value you’re looking for in column J:J on Sheet 2.
• The MATCH function is looking for a row where both conditions are true (hence the multiplication, which acts as an AND condition).
• The 0 at the end of the MATCH function specifies that you want an exact match.

*Note: this is an array (in case of you did not know), you'll need to do CTRL + Shift + Enter after typing or pasting the formula into the cell.

[#3]

Hi @Rr_ ,

Sorry still learning to use this as well.

What I'm wanting to do is look up each asset in asset condition A:A in Borefield B:B and in Borefield J:J and return the value from borefield A:A or borefield I:I depending on where it is sitting so that if i change its location it auto updates in the asset condition sheet.

Thanks

Kieren

[#4]

@Kieren20101 
You can still use the INDEX and MATCH functions in an array formula. Here's how you can set it up:

1. Create a Unique Identifier: In the ‘Borefield’ sheet, create a unique identifier by concatenating the values of columns B and J. You can do this in a new column (let’s say column K) with the following formula: 

=B4&J4

2. Lookup Formula: In the ‘Asset Condition’ sheet, enter the following array formula to look up the asset and return the value from the ‘Borefield’ sheet’s column A or I: 

=IFERROR(INDEX(Borefield!A:A, MATCH(A2&Borefield!B:B, Borefield!K:K, 0)), INDEX(Borefield!I:I, MATCH(A2&Borefield!J:J, Borefield!K:K, 0)))

Explanation:

• A2 is the cell in the ‘Asset Condition’ sheet containing the asset you want to look up.
• Borefield!A:A and Borefield!I:I are the columns from which you want to return the value if there’s a match.
• Borefield!B:B and Borefield!J:J are the columns you’re searching for the lookup value.
• Borefield!K:K is the new column with the unique identifier.
• MATCH function searches for the concatenated value of A2 and the respective column in the ‘Borefield’ sheet.
• INDEX function returns the value from the corresponding row in Borefield!A:A or Borefield!I:I.
• IFERROR function tries the first INDEX-MATCH combination; if it fails (i.e., no match is found or an error occurs), it moves on to the second INDEX-MATCH combination.

[#5]

@Rr_ 

As column K is in use i have tried doing the UI in Q. Should this be =B4&J4? Do i then just click and drag the bottom right corner down to do the rest? I have tried doing this and still having no luck.

Does this look right?

[#6]

@Kieren20101 

Hmmmm, after pasting/typing the formula, did you press Enter key? That's an array formula so you should press CTRL + SHIFT + ENTER AFTER you paste the formula to make it work.
it should be like this:

with a BRACKET on the beginning and the end of the formula.
Refer here: How to enter ARRAY Formula

[#7]

@Rr_ ,

You're an absolute legend. It wasn't working for me when i did the ctrl+shift+enter but that was just because of how i had the program opened. I've now managed to get it to work but it seems the =B4&J4 is what is stuffing me up now as they're joined together on the Q column.

[#8]

@Kieren20101 
Try this formula:

=IFERROR(INDEX(Borefield!A:A, MATCH(A2, Borefield!Q:Q, 0)), INDEX(Borefield!I:I, MATCH(A2, Borefield!Q:Q, 0)))

No need to press CTRL + SHIFT + ENTER.
Just press Enter key only

[#9]

The same results. Am i able to set it so in Q4 its =B4 and R4 is =J4 and I'm able to to get A2 to look in Borefields Q:Q and R:R?

[#10]

could you upload your file here? or thru drive. without sensitive data

[#11]

@Rr_ ,

Sorry not really sure how to do that?

[#12 · Accepted solution]

@Kieren20101 

I have attached a sample file based on the screenshots you provided.
Borefield sheet:

Asset Condition sheet:

the formula used in Asset Condition sheet to lookup your asset/generator to Borefield sheet in column B and I.
=IFERROR(IFERROR(INDEX(Borefield!$A$4:$A$30,MATCH($A2,Borefield!$B$4:$B$30,0)),INDEX(Borefield!$I$4:$I$30,MATCH($A2,Borefield!$J$4:$J$30,0))),"Not Found")

[#13]

@Rr_ ,

You're a legend! Thanks so much for your help i would have never got that.