INDEX MATCH with multiple criteria

i have a workbook that has employee names, spouses and children. I am attempting to make a list of employees based on the relationship column and the package column. The list would encompass every employee that has the relationship "1" and a MEDICAL package. When I write a INDEX MATCH formula, it only retrieves the the first employee and not any of the other employees.

Relationship meaning:
1 = Employee
2 = Spouse
3 = Children

The next question is some employees have multiple children that is signified with a 3. The 3 is not unique making it hard to write a formula to retrieve the children's birthday. How can I make the 3 unique for families that have multiple children?

I have attached the sample workbook along with my desired result.

Thank you for helping!

Try these:

G3 in post #2

H3 =IFERROR(INDEX(C:C,MATCH(G3,A:A,0)),"")

I3 =IFERROR(INDEX(C$2:C$19,INDEX(MATCH("*"&MID(G3,FIND(" ",G3),LEN(G3))&2,A$2:A$19&D$2:D$19,0),0)),"")

J3 =IFERROR(INDEX($C:$C,SMALL(IF(ISNUMBER(SEARCH(MID($G3,FIND(" ",$G3),LEN($G3)),$A$2:$A$19))*($D$2:$D$19=3)*($E$2:$E$19="MEDICAL"),ROW($A$2:$A$19)),COLUMNS($A:A))),"") Ctrl Shift Enter

Drag J3 over through K3 then drag all of the formulas down as far as needed.

There is a column in the data I deleted because I didnt think it was helpful so that is my mistake, but it links all the family members by the employees name. I would still run into the issue of multiple children since the 3 is not unique. Once I had the employee list, I was thinking of just doing a simple vlookup to populate the remaining fields.

Did you try the suggestion in post #3? It returns the desired output listed in the sample from post #1.

It goes off of last name.

Using the workbook from post #4, you can use these:

J3 =IFERROR(INDEX(D$2:D$19,INDEX(MATCH(H3&2,A$2:A$19&E$2:E$19,0),0)),"")

K3 =IFERROR(INDEX($D:$D,SMALL(IF(($A$2:$A$19=$H3)*($E$2:$E$19=3)*($F$2:$F$19="MEDICAL"),ROW($A$2:$A$19)),COLUMNS($A:A))),"") Ctrl Shift Enter

These formulas work perfectly, thank you! Sending Rep!

Happy to help. Thanks for the rep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Hello again, you had sent me the following PM:

how would you rewrite the formula for the children DOB's without the MEDICAL part of the formula? I updated my spreadsheet to generate a list of employees with relationship 1 only instead of relationship 1 & MEDICAL. Not every employee will have a medical package in column F.

Please Login or Register  to view this content.

thank you and appreciate your help!

I do not work privately so we can continue here.

Have you tried removing the *($F$2:$F$19="MEDICAL") part of the formula? If that doesn't work as expected, consider sharing a small representative sample of your data along with the desired output.

When I remove the *($F$2:$F$19="MEDICAL") part of the formula, Children date of births are duplicated due to some having both MEDICAL & DENTAL packages. I have attached my workbook with desired results and a table showing what the formula is doing.