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
You keep only first names in your dataset, or have more columns to identify the person (at least last name)?
Now it returns first found record with same name and code.
- Nevermind, I was missing a bracket. It is working now. Thanks!
Hi Sergei,
I'm not sure why it isn't working for me. I've attached what the spreadsheet looks like from my end.
Monica,
Based on our sample file you may use
=SUMPRODUCT( (Sheet1!$B$2:$B$1000=Sheet2!$B2)*(ISNUMBER(FIND(Sheet2!C$1,Sheet1!$C$2:$C$1000)))*Sheet1!$D$2:$D$1000)
where the range starts from row 2 and for sure within first 1000 records.
First two multipliers filter the records by ID and CODE and the third one sum filtered records. Please note - that's not an array formula.
Much better of you convert your records onto the Excel Table, let name it Records, when the formula will be
=SUMPRODUCT( (Records[ID]=Sheet2!$B2)*(ISNUMBER(FIND(Sheet2!C$1,Records[Code])))*Records[Value])
Both variants are in the attached file
- Hi Sergei,
Yes you are correct. ID 101 has a LIFE value of 25. Monica, please clarify a bit. Did i understand correctly, each ID could have more than one CODE in each category and you'd like to have sum of values for each ID in category?
For example
ID CODE Value 101 6LIFE1 15 101 6LIFE2 10
and the result for 101 in category LIFE shall be 25
Hi Sergei,
Sorry to bug you again with this formula.
I am working on a second spreadsheet with our other carrier. My problem is that for the dental benefits, everyone has 2 codes. Is there a way to incorporate this into the formula so that it adds the values together? Thanks!
Formula you providedCoding
Okay, good
- Hi Sergei,
I figured out that some of the IDs had spaces either before or after the number so it was recognizing it as part of the text stream (I think). Once I clicked the check mark to validate it, it worked. When i need bit more details.
- do i understood correctly if you copy the cell which works and paste it to next cell down it doesn't work;
- are you sure all your ID:s are numbers (both first in second sheet), or all text? You may check by ISNUMBER ID for the record which doesn't work (in both sheets)
- alternatively what returns =(Sheet1!<ID> = Sheet2!<ID>)
- please be sure in the first record formula is
Sheet1!$B:$B=Sheet2!$B2 (not Sheet1!$B:$B=Sheet2!$B$2)
- It isn't working in my fle
Monica, in my sample it works for all records
Or it doesn't work in your file?
- Hi Sergei, I copied what you provided, and it worked for Smith 101 but none of the subsequent rows
I changed on ID, it works
=IFERROR( INDEX(Sheet1!$D:$D, MATCH(1, (Sheet1!$B:$B=Sheet2!$B2)*(ISNUMBER(FIND(Sheet2!C$1,Sheet1!$C:$C))),0),1),0)Please see attached
- Okay thank you
Let me check the file, i don't remember details