Forum Discussion
Complex INDEX MATCH - Need assistance
Hello,
I am struggling to build a formula to look up a specific value on one sheet by comparing the people's last names, and comparing codes. For example, on sheet 1 codes I use are 6LIFE and 6LIFEO which correspond to the LIFE column on sheet 2.
Here is what it looks like on sheet 1
Last name Code Deduction
Smith 6LIFE 5.00
Smith 6LIFEO 0.00
Here is what it looks like on sheet 2
Last name LIFE ADD LTI
Smith 5.00
I need it to be able to tell me what the value is in the LIFE, ADD or LTI column if it matches either of those codes listed on sheet 1.
Any help would be great! Thanks!
Monica,
So, if Sheet1
and Sheet2
in C2 here is array formula (Ctrl+Shift+Enter)
=IFERROR(
INDEX(Sheet1!$C:$C,
MATCH(1,
(Sheet1!$A:$A=Sheet2!$A3)*(ISNUMBER(FIND(Sheet2!B$1,Sheet1!$B:$B))),0),1),0)IFERROR returns zero if nothing is found
ISNUMBER(FIND... returns array with TRUE (1) if B1 is found and 0 if not
Before it simular array for names, both of them with AND condition show us where are the records with both the name and code are as in Sheet2.
When we copy down and to the right the cell with formula.
Above formula is not good from performance point of view. Better to limit the range (e.g. $A$1:$A$100, etc.) and even more better to transform the range in Sheet1 into the Table. That gives us the array formula
=IFERROR(INDEX(Table1[Value],MATCH(1, (Table1[EE Last Name]=Sheet2!$A2)*(ISNUMBER(FIND(Sheet2!B$1,Table1[Code]))),0),1),0)
Sample files are attached
30 Replies
Hi Monica,
Please clarify bit more. For the same last name you'd like to find the record where the value is second column (Code) of the Sheet1 matches the name of the column in Sheet2 (LIFE, or ADD, or LTI) and put Deduction value from Sheet1 into the cell of Sheet2 where the column name matches the code.
Like this or not? Not clear what do you mean under "matches". In your example you have two codes, no one of them is equal to column name (LIFE) in second sheet. If LIFE shall be somewhere inside the code when not clear why first code matches, and second not.
Hi thank you for your response!
To clarify:
Here is what it looks like on sheet 1
Last name Code Deduction VALUE ON SHEET 2
Smith 6LIFE 5.00 This is where the formula would go
Smith 6LIFEO 0.00
Smith 6ADD 0.00
Smith 6ADD1 3.00
Here is what it looks like on sheet 2
Last name LIFE ADD LTI
Smith 5.00
I need it to be able to tell me that Smith's LIFE column says 5.00
And why Smith's LIFE colums says 5.0 for the code 6LIFE and nothing (0.0) for the code 6LIFEO?
