Find list of words in range for array formula

Stuepef

Board Regular
Joined
Oct 23, 2017
Messages
128
Office Version
  1. 2021
Platform
  1. Windows
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":
Excel Formula:
INDEX($D$2:$D$17,SMALL(IF((B2:$B$17=A21)*($C$2:$C$17="Spouse"),ROW($E$2:$E$17)),1*1))

1696883570015.png
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Try
Excel Formula:
INDEX($D$2:$D$17,SMALL(IF((B2:$B$17=A21)*($C$2:$C$17="*Spouse*"),ROW($E$2:$E$17)),1*1))
 
Upvote 0
How about this?
Excel Formula:
=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))
 
Upvote 0
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

Excel Formula:
=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.
 
Upvote 0
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

Excel Formula:
=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.
 
Upvote 0
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
ABCD
1
2123Employee27/04/1981
3123Spouse13/03/1980
4123Child29/06/2021
5456Employee15/05/1980
6456Spouse8/12/1981
7111Employee12/10/1996
8122Employee21/11/1964
9144Employee4/06/1979
10144Ex-Spouse31/12/1982
11144Child29/06/1999
12144Child29/06/1999
13805Employee14/06/1984
14805Ex-Spouse13/04/1976
15805Child25/05/2006
16805Child15/12/2009
17805Child27/01/2014
18
19
20
2112313/03/1980
224568/12/1981
23111
24122
2514431/12/1982
2680513/04/1976
27
Spouse
Cell Formulas
RangeFormula
A21:A26A21=UNIQUE(B2:B17)
B21:B26B21=LET(d,MAXIFS(D2:D17,B2:B17,A21#,C2:C17,"*Spouse"),IF(d=0,"",d))
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,223,879
Messages
6,175,148
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top