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
>Solution :
Use this
=INDEX( NAMES!A2:A,MATCH(TRUE, REGEXMATCH(NAMES!A2:A, TEXTJOIN("|",1,C2:C)),0),1)
Help links
INDEX – MATCH – REGEXMATCH – TEXTJOIN