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?
For some other people, it is coded to 6LIFEO instead of 6LIFE but they both correspond to the LIFE colum on sheet 2. It depends which carrier they use.
When how we know for Smith to take value with code 6LIFE or with code 6LIFEO if both exists?
OK, what if I do it this way?
SHEET 1
EE Last Name Code Value
Smith 6ADD 6.96
Smith 6LIFE 34.59
Smith 7CRILL 3.12
Jones 6LIFEO 45.00
SHEET 2
EE Last Name LIFE Sheet 1 Value ADD Sheet 1 Value
Smith 36.95 This is where it needs to pull 34.59
Jones 36.95 This is where it needs to pull 45.00