Looking up partial text from a column

**bronkista** · 07-11-2013, 08:15 AM

Hi Guys,

Need your help on a formula for looking up "inexact" hotel names from a list of hotel names.
If there is also a chance that it can be looked up even if the inexact name is misspelled.

I have attached a spreadsheet of the desired results.

Hotel Data Matching.xls

Appreciate the help!
Thanks!

**codeslizer** · 07-11-2013, 08:25 AM

hmm.. am sorry, but am really not able to understand your objectives fully. Are you trying to make sure people don't mis-spell the hotel names when they enter the data?? If yes, you could create a named range with the list of available hotels and put that into the validations of the cells where they'll be entering the hotel names.

If no, I'll need to understand your needs a little more..

**bronkista** · 07-11-2013, 08:33 AM

Sorry if it was unclear.
Let's just say that since the hotel names are user-entered, its error prone.
My goal here is to match these user entered hotel names (misspelled and/or inexact) to the closest hotel name from the given list, if it's possible.

Hope that makes more sense.

**codeslizer** · 07-11-2013, 09:17 AM

Hmm.. if you'd like to fully remove the errors while input, you will of course reduce the amount of codes and calculations - which means more savings on CPU. You should try putting validations for this job. It suits the best as per my experiences. If users enter the right input, you'll always get right data for calculations. Hence, perfect solutions!

Named Ranges basically are of two types - Static and Dynamic. I prefer dynamic for if am not sure, that the list might get longer over a period of time or not. Considering your example, I think it might.

Following are my examples on how you can create a named range
1. Static : http://postimg.org/image/h78q9zrod/
2. Dynamic (using your file for example) : http://postimg.org/image/dlqcdfl5p/

Formula used for creating the named range is as follows:

Please Login or Register  to view this content.

**Debraj Roy** · 07-11-2013, 09:45 AM

FuzzyLookup is best in this case...
Google it..

**bronkista** · 07-11-2013, 10:31 PM

@codeslizer

Unfortunately, my goal is not to create a data validation to prevent any input errors, sorry if you don't understand my objective, thanks for the suggestion hough.
The goal is to simply match the input data to the given list, but the challenge is that the input data is almost always inexact and/or misspelled.

@Debraj
I had encountered FuzzyLookup as a solution to this problem and I agree that it can definitely help.
But I am at a setting where I can't download and install add-ins to my excel, so if there's any formula that can mimic the function of FuzzyLookup, it will definitely help.