Forum Discussion
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.
=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")
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.
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
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&J42. 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.
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?
