Index match

[Muza16]

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!

[#2]

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

[#3]

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

[#4]

@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

[#5]

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

[#6]

@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

[#7]

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

[#8]

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