Forum Discussion
Vlookup, find if a shorter string (in my array) is contained in a longer string (the searched cell)
I'm working on a rates calculator for a given address, and I've got an array with standard location names that I want to expand with more standard locations as we get them.
But when given an address or location name by a customer, which tend to be laid out differently to one another, I want to check whether or not the location in question is caught in the larger area I've already got written down.
IE, if my list were to contain a "town name", and my quoting sheet supplied to me had things like "town name" fire station or Generic garage "town name" or a full address containing the "town name" something along those lines.
Whilst its not a super hard thing to manually fill out it would make it incredibly more streamlined for those maybe not so good at excel, who would be doing most the quoting work in my company, to just copy and paste a formula that can draw this information
Right now I'm using Vlookup as I've only been working with excel for a week or so by now, however if there are any suggestions to transition away from Vlookup that would also be good.
this spreadsheet will be used on our quoting sheeting and needs to be modular and easily modifiable to allow for the variation in supplied addresses.
=VLOOKUP(L15,$C$3:$E$33,2,FALSE)
Ive tried many permutations of string manipulation to see if any of the strings within my array are contained with in a supplied address/location name in the $C$3:$E$33 part, with no luck
Many thanks,
Ben - a total beginner in excel
3 Replies
You may use VLOOKUP with a wildcard:
=VLOOKUP("*"&L15&"*",$C$3:$E$33,3,0)or XLOOKUP w/wildcard:
=XLOOKUP("*"&L15&"*",$C$3:$C$33,$E$3:$E$33,,2)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))
