SOLVED

# lookup help

Copper Contributor

# lookup help

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

12 Replies

# Re: lookup help

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.

``=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.

# Re: lookup help

Hi @Rodrigo_ ,

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

# Re: lookup help

@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.

# Re: lookup help

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?

# Re: lookup help

@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

# Re: lookup help

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.

# Re: lookup help

@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

# Re: lookup help

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?

# Re: lookup help

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

# Re: lookup help

Sorry not really sure how to do that?

best response confirmed by Kieren20101 (Copper Contributor)
Solution

# Re: lookup help

@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.

# Re: lookup help

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

1 best response

Accepted Solutions
best response confirmed by Kieren20101 (Copper Contributor)
Solution

# Re: lookup help

@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.