Forum Discussion
Vlookup, find if a shorter string (in my array) is contained in a longer string (the searched cell)
| place - | Miles to - | |||
| Belfast | 4 | VLOOKUP (or other kinds of searches) | ||
| New York | 20 | ABC Podunk Anytown | #N/A | |
| Tokyo | 60 | Podunk, upper district, Fire Station | #N/A | |
| Podunk | 9 | Building site A, Podunk location | #N/A | |
| Madeup | 21 | |||
| London | 10 | |||
| Cheyenne | 9 | |||
| Planto | 5 | |||
| Anchorage | 7 |
Patrick2788 thank you so much for the reply, I'm having issues attaching a xlsx spreadsheet so show you more clearly, however I hope this little table can clarify my question. I'm in need of a "reverse wildcard" whereas your response allowed me to search for a general location (in the searched cell) and find its match in list of longer locations (the search array). I need to take a longer location, "Podunk Fire Station" or "ABC Podunk Anytown", and then search the array for the result of 9 miles.
the hope is duplicate words across these locations will not be searched such as "Podunk Fire Station" & "Tokyo Fire Station" as the word fire/station isnt contained in the array, but the main location would be searched. hope this clarifies.
Regards Ben
You'd need something like this:
=LET(results,IFERROR(SEARCH($A$2:$A$10,D3),""),r,MATCH(1,results,-1),INDEX($B$2:$B$10,r))