vlookup values matching on a partial text string

[vantilian]

I have two worksheets, and I'm trying to use vlookup to retrieve a value from Worksheet 2 by matching on a partial text string. Situation is as follows:

Worksheet 1:

Column A
Elliot & Sons Restaurants
Jan Thomas Company
Taco El Ray

Worksheet 2:

Column A:
El Ray Taco's
Elliot & Sons, Inc.
Jan Thomas Restaurants, LLC

Column B:
15
20
25

I would like to vlookup using the Worksheet 1 / Column A Values, which are text strings, and where there is a partial match to Worksheet 2 / Column A text strings, return the value in Worksheet 2 / Column B. As you can see in the example, the tricky part is getting the vlookup formula to match "Taco El Ray" in Worksheet 1 to "El Ray Taco's" in Worksheet 2.

Another tricky part is trying to avoid matching "Elliot & Sons Restaurants" in Worksheet 1 with "Jan Thomas Restaurants" in Worksheet 2. I guess I will need to restrict the lookup to trying to match the first 10 characters or so of the text string in Worksheet 1 / Column A, to any part of text string in Worksheet 2 / Column A.

As always your help is greatly appreciated.

Cheers,

Russell

 

[vantilian]

Thanks for all the suggestions. I'm using Alan's fuzzyvlookup function and it is working fine. I appreciate you all directing me to those solutions.

Cheers,

Russell

 

[vantilian]

One last question concerning use of this fuzzyvlookup function: Is there a way to refine the LookupValue (Entry To Be Looked Up) to only considering the first 15 characters of the text string in LookupValue when comparing to the TableArray (Lookup Range)?

 

[just_jon]

Without going into the code, easiest way is to create a new column via =LEFT(OldCell,15) and then apply the UDF to that data set.

 

[vantilian]

Great suggestion. Thanks again.