Find list of words in range for array formula

[Stuepef]

I have a spreadsheet that I am trying to populate by using an array formula. Right now, I have a formula that looks in a range and identifies if the word "Spouse" is within it. However, I would like to update the formula to search for "Spouse" & "Ex-Spouse" within the same range, but my current attempts are not working.

I made a named range for the list of Spouse & Ex-Spouse labeled Marriage hoping that would help, but it looks to find combinations of Spouse & Ex-Spouse together instead of separately.

I have tried the formulas: SEARCH, FIND, ISNUMBER, SUMPRODUCT, but it seems that works better when looking in a singular cell rather than a range.

Here is the current formula using "Spouse":

INDEX($D$2:$D$17,SMALL(IF((B2:$B$17=A21)*($C$2:$C$17="Spouse"),ROW($E$2:$E$17)),1*1))

 

[BigBeachBananas]

Try

INDEX($D$2:$D$17,SMALL(IF((B2:$B$17=A21)*($C$2:$C$17="*Spouse*"),ROW($E$2:$E$17)),1*1))

 

[Stuepef]

Try

INDEX($D$2:$D$17,SMALL(IF((B2:$B$17=A21)*($C$2:$C$17="*Spouse*"),ROW($E$2:$E$17)),1*1))

This formula provides a #NUM error.

 

[BigBeachBananas]

How about this?

=INDEX($D$2:$D$17,SMALL(IF(($B$2:$B$17=A21)*(($C$2:$C$17="Spouse")+($C$2:$C$17="Ex-Spouse")),ROW($E$2:$E$17)),1*1))

 

[Stuepef]

How about this?

=INDEX($D$2:$D$17,SMALL(IF(($B$2:$B$17=A21)*(($C$2:$C$17="Spouse")+($C$2:$C$17="Ex-Spouse")),ROW($E$2:$E$17)),1*1))

This formula provides the following:

 

[BigBeachBananas]

It

This formula provides the following:
View attachment 100005

It's because there are no Spouse or Ex-Spouse in the data set for that EmpID. You can show blank if nothing is found by

=IFERROR(INDEX($D$2:$D$17,SMALL(IF(($B$2:$B$17=A21)*(($C$2:$C$17="Spouse")+($C$2:$C$17="Ex-Spouse")),ROW($E$2:$E$17)),1*1)),"")

You can put whatever you want to say inside the last double quotes when not found.

 

[Stuepef]

It

It's because there are no Spouse or Ex-Spouse in the data set for that EmpID. You can show blank if nothing is found by

=IFERROR(INDEX($D$2:$D$17,SMALL(IF(($B$2:$B$17=A21)*(($C$2:$C$17="Spouse")+($C$2:$C$17="Ex-Spouse")),ROW($E$2:$E$17)),1*1)),"")

You can put whatever you want to say inside the last double quotes when not found.

The formula output is providing the date of births of the child(ren) by employee ID and not the spouse and Ex-spouse.

 

[Peter_SSs]

Does this do what you want?

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

23 10 10.xlsm
	A	B	C	D
1
2		123	Employee	27/04/1981
3		123	Spouse	13/03/1980
4		123	Child	29/06/2021
5		456	Employee	15/05/1980
6		456	Spouse	8/12/1981
7		111	Employee	12/10/1996
8		122	Employee	21/11/1964
9		144	Employee	4/06/1979
10		144	Ex-Spouse	31/12/1982
11		144	Child	29/06/1999
12		144	Child	29/06/1999
13		805	Employee	14/06/1984
14		805	Ex-Spouse	13/04/1976
15		805	Child	25/05/2006
16		805	Child	15/12/2009
17		805	Child	27/01/2014
18
19
20
21	123	13/03/1980
22	456	8/12/1981
23	111
24	122
25	144	31/12/1982
26	805	13/04/1976
27
Spouse

Cell Formulas
Range	Formula
A21:A26	A21	=UNIQUE(B2:B17)
B21:B26	B21	=LET(d,MAXIFS(D2:D17,B2:B17,A21#,C2:C17,"*Spouse"),IF(d=0,"",d))
Dynamic array formulas.