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:
Help links