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
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