Forum Discussion

Muza16's avatar
Muza16
Copper Contributor
Mar 16, 2022

Index match

Hello, I am trying to do the following using 3 different sheets. The first sheet (see picture 1) contains the info on ID and Number. The second file contains the info on Number and Chain (see picture 1), and the third file (see picture 3) contains the columns of chain and code. I need to get to the column "Code" from the third file by matching it to the ID column in the first file, and automatically add the value to the first file (see picture 1, column "code"). Is that possible to do? Thanks in advance!

 

 

excel
Excel on Mac
Formulas and Functions

7 Replies

  • vijayks's avatar
    vijayks
    Copper Contributor
    Mar 17, 2022

    Muza16 

    I used a double vlookup formula and got the right result in my sample data. See below the snippet of the formula used and the sample dataset.

     

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Mar 17, 2022

    Muza16 Perhaps a double VLOOKUP will do what you need. 

    I've used some named ranges to avoid long and direct references including sheet names. File attached.

     

    Edit: Attached wrong file and screenshot. Fixed now.

  • amands_1's avatar
    amands_1
    Copper Contributor
    Mar 17, 2022

    Muza16 

    Hello!

     

    If I have understood your requirement correctly then this file might be useful to you!

     

    Feel free to ask for any clarifications in the formula.

     

    Sheet 1

    • Muza16's avatar
      Muza16
      Copper Contributor
      Mar 17, 2022
      amands_1 Hello. Thanks for the help. But for me it returns the same value for all the rows of Code while I need it to match the chain and number values accordingly. Also, my data in the excel files are not in the same order, they vary, so I need to filter to find each of them in different sheets.
      • amands_1's avatar
        amands_1
        Copper Contributor
        Mar 17, 2022

        Muza16 

        In below screenshot I have entered a number '0' but since number '0' is not available in database in SHEET 2, excel will show error. If number entered in a row of SHEET 1 is present in database in SHEET 2 it will extract chain valve from SHEET 2 using INDEX function and then using chain value and VLOOKUP function it will show corresponding code from SHEET 3.

         

        Also since INDEX and VLOOKUP are used, sequence of data should not matter. 

         

        SHEET 1SHEET 2SHEET 3

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Mar 16, 2022

    Muza16 

    =INDEX(sheet3!$B$2:$B$7,MATCH(1,(sheet1!B2=sheet2!$A$2:$A$7)*(sheet2!B2=sheet3!$A$2:$A$7),0))

     Maybe with this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

    • Muza16's avatar
      Muza16
      Copper Contributor
      Mar 17, 2022

      OliverScheurichunfortunately, this function doesn't work in my table. It returns the same "code" value for all the rows while I need it to return the exactly matching code values for each row. Also, please note that the data in the excel files are not in the same order, they vary, so I need to filter to find each of them in different sheets.

Resources