Forum Discussion
Complex INDEX MATCH - Need assistance
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
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?