Forum Discussion

Kieren20101's avatar
Kieren20101
Copper Contributor
Jan 15, 2024
Solved

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

excel
  • Rodrigo_'s avatar
    Rodrigo_
    Jan 17, 2024

    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")

12 Replies

  • Rodrigo_'s avatar
    Rodrigo_
    Iron Contributor
    Jan 15, 2024

    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.

    • Kieren20101's avatar
      Kieren20101
      Copper Contributor
      Jan 15, 2024

      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

      • Rodrigo_'s avatar
        Rodrigo_
        Iron Contributor
        Jan 15, 2024

        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.

Resources