VLOOKUP With Lookups that Don't Match

simonswes

New member
Joined
Jul 14, 2014
Messages
1
Reaction score
0
Points
0
I apologize if my title is misleading, I was trying to be as clear and as succinct as possible. I have a list of hotels and corresponding hotel IDs. I would like to be able to use VLOOKUPS to pull the hotel IDs based on hotel names that clients provide us. However, the hotels names that the clients use are not always exactly the same as the names on our list. Here is what I have done so far:

1. Pull out the keywords in the hotel name and replace the spaces with wildcards, and then use the Find function (this works about 98% of the time, but is really slow and cannot be replicated to my knowledge):
Hilton Phuket Arcadia Resort and Spa --> *Hilton*phuket*arcadia*

2. I have tried pulling out the first 40 characters, replacing the spaces with wildcards and then using VLookUp, but that does not appear to work. I also tried replacing the spaces with &"*"&, so that the vlookup would be looking for:
"*"&Hilton&"*"&phuket&"*"&arcadia&"*"

3. I tried pulling each of the words out and putting them into their own cells:
a3=Hilton
c3=phuket
d3=arcadia
and then i used the cell reference as opposed to the actual word in the formula:
"*"a3&"*"&c3&"*"&d3"*"

None of these appeared to work. I would like to be able to paste the names of the hotels from the client into a column, and then have the formula return the hotel id that corresponds. It does not need to work for every single item as sometimes the client name is vastly different than the name we have on our list. Any help that you can provide would be great!
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
Google "FuzzyFind Function", there is a UDF (User Defined Function) out there somewhere that I've seen referenced in similar forum questions that could be of interest.
 

Rizky

New member
Joined
Apr 28, 2014
Messages
44
Reaction score
0
Points
0
You have your sample workbook? try to upload at the forum..

Thanks
 
Top