vlookup values matching on a partial text string

vantilian

New Member
Joined
Mar 3, 2002
Messages
44
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
You can create a table whcih lists all your businesses and assign them each with a unique number. So you can use the vlookup to look for the unique numbers.

Hope this helps.
 
Upvote 0
fzhou said:
You can create a table whcih lists all your businesses and assign them each with a unique number. So you can use the vlookup to look for the unique numbers.

Actually, that is the crux of the problem. Worksheet 1 is data is pulled direct from customer database and it does contain the unique customer number for the Name values. Problem is, Worksheet 2 is obtained from an external source which cannot include the customer number, and thus the only comparable field to match on is the Name, which has slight differences between the two. Thanks for your response though.
 
Upvote 0
vantilian said:
Problem is, Worksheet 2 is obtained from an external source which cannot include the customer number, and thus the only comparable field to match on is the Name, which has slight differences between the two.

Will the same differences always show up in this Worksheet2? that is, if it is listed as Company AaA in Worksheet1 and Company AAa in Worksheet2, will it always appear as Company AAa in Worksheet2? If so, the following link might prove helpful.

http://216.92.17.166/board2/viewtopic.php?t=73594
 
Upvote 0
shades said:
Will the same differences always show up in this Worksheet2? that is, if it is listed as Company AaA in Worksheet1 and Company AAa in Worksheet2, will it always appear as Company AAa in Worksheet2? If so, the following link might prove helpful.

http://216.92.17.166/board2/viewtopic.php?t=73594

I can't say that would always be the case. Since Worksheet 2 is coming from an external source it will probably not be consistent. There will usually be a common word that allows matching in the Name Column between the two sheets. I realize there will be some 'no matches' given the variables of the situation, but I'm just trying to get the most matches possible. Thanks for your response.
 
Upvote 0
Assuming that there is too much data to do this manually, here is what I suggest:

Design a set of rules that determine whether two names are matches. One example of a rule would be if there are 3 or more words and only the last word is different (inc. vs. co.). Then write a user defined function in VBA which accepts two values and returns a true or false based on the rules. It won't be perfect but there is no way to be perfect on this one aside from having naming conventions adopted by both your sources.
 
Upvote 0
Each Worksheet has about 1800 rows of data. With a standard vlookup function on the Name matching the cell contents alone, I can get about a 50% hit rate on matches. The other 50% are really only off by a word or so, such as "Inc" versus "Corp", etc. I tried to replicate some of the worst case scenarios in my example. To simplify the problem at hand I guess a start would be if I could have the function act as follows: look at the first 15 characters in text string in Worksheet 1 / Column A, compare to any part of the text string in Worksheet 2 / Column A cells to see if a match. Ideally, if within the first 15 characters of text string Worksheet 1, if it didn't necessarily have to find the full 15 string to match it would be best. Such as if, hypothetically, characters 4 through 9 within that 15 character text string in Worksheet 1, were found to match characters 6 through 11 within the text string in Worksheet 2 / Column A, that would be ideal. I guess something to the effect of a "Like String" or wildcarding? Thanks, and I appreciate your response.
 
Upvote 0
I should also add, although usually only one word difference between the two Name fields, there would be too many different scenarios to allow for establishing rules. The variations on Inc. versus Corp. alone are many: Incorporated, Inc., Inc, Corp., Corp, Corporation, LLC, LP, Co., Co, ad infinitum.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top