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.