Home > OS >  Search Criteria as a range
Search Criteria as a range

Time:10-02

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: enter image description here

Help links

enter image description here

  • Related