Search Criteria as a range

Question

I am trying to find a way to use a range as a search criteria.

The problem is as follows:
Below is a range of names in NAMES!A2:A.

Names 
Benjamin Douglas
Caitlin Enriquez
Cristina Butler
Emily Furse
Jenny Ford
Liana Fowler
Lowri Donald
Rachel McBride
Rochelle Guy
Samina Swanson
Sol Wills
Sonya Cantu

On another sheet, I allow input of multiple names in another sheet in the range SEARCH!B:B. Upon entering a range of names, I would like it to return one string that contains any string within SEARCH!B2:B into SEARCH!A2

Searching Criteria
Jennifer
Jennie
Jenny
Jenni
Jenn
Jen

And following the search criteria the formula would return in SEARCH!A2 the string Jenny Ford
I have tried multiple formulas including INDEX and FILTER but none of them accept a range as a search criteria.
I have included an example sheet if needed: link

@Osm i have tried ```=VLOOKUP(B2:B,NAMES!A1:A,1,1)``` as well alongside ```=FILTER(NAMES!A1:A,NAMES!A1:A=B1:B)``` I cannot find my formula on index. — emmy, Oct 01 '22 at 17:14

Osm · Accepted Answer · 2022-10-01T17:33:22.267

0

Use this

=INDEX( NAMES!A2:A,MATCH(TRUE, REGEXMATCH(NAMES!A2:A, TEXTJOIN("|",1,C2:C)),0),1)

Help links

INDEX - MATCH - REGEXMATCH - TEXTJOIN

edited Oct 01 '22 at 17:33

answered Oct 01 '22 at 17:19

Osm

2,699
2
4
26

Thank you very much, I will put this in :)\ – emmy Oct 01 '22 at 17:19

score 0 · Answer 2 · answered Oct 01 '22 at 17:19

0

try:

=FILTER(NAMES!A2:A, REGEXMATCH(NAMES!A2:A, "(?i)"&TEXTJOIN("|", 1, C2:C)))

answered Oct 01 '22 at 17:19

player0

124,011
12
67
124

2

`(?i)` to get the match reardless if upper or lower case – Osm Oct 01 '22 at 17:24

Search Criteria as a range

2 Answers2