Mar 16 2022 03:25 PM
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!
Mar 16 2022 03:35 PM
=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.
Mar 17 2022 01:16 AM - edited Mar 17 2022 01:20 AM
@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.
Mar 17 2022 01:58 AM
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.
Mar 17 2022 02:08 AM
Mar 17 2022 02:44 AM
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.
Mar 17 2022 02:52 AM - edited Mar 17 2022 02:57 AM
@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.
Mar 17 2022 05:05 AM
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.