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