I have a range within a spreadsheet that shows the date of births for employees and any dependents, whether they are a spouse or child, within a column labeled Relationship. The possible relationship values can be Spouse, Ex-Spouse or Domestic Partner and the challenge with the INDEX SMALL formula I created is it only looks for "Spouse", but I am looking for a way to replace this part of the formula to have it search the list and if true, provide that value.
IFERROR(INDEX($D$1:$D$11,SMALL(IF(($A$1:$A$11=H2)*($C$1:$C$11="Spouse"),ROW($B$1:$B$11)),1*1)),"")
This formula works great if there is a "Spouse", but for any other variation (Ex-spouse, Domestic Partner) it will provide a blank, which makes sense.
The question is how do I update this part of the formula to look at the list in F2:F4 and if there is a match for that employee, then provide that word match meaning if there is an Ex-spouse, then the formula would give Ex-Spouse as the answer?
Workbook is attached - thank you for your help!!
Bookmarks