Page 1 of 2 12 LastLast
Results 1 to 10 of 14

vlookup values matching on a partial text string

This is a discussion on vlookup values matching on a partial text string within the Excel Questions forums, part of the Question Forums category; I have two worksheets, and I'm trying to use vlookup to retrieve a value from Worksheet 2 by matching on ...

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    44

    Default vlookup values matching on a partial text string

    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

  2. #2
    Board Regular
    Join Date
    Nov 2003
    Posts
    132

    Default Re: vlookup values matching on a partial text string

    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.

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    44

    Default Re: vlookup values matching on a partial text string

    Quote Originally Posted by fzhou
    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.

  4. #4
    Board Regular shades's Avatar
    Join Date
    Mar 2002
    Location
    Near the Land of Oz
    Posts
    1,550

    Default Re: vlookup values matching on a partial text string

    Quote Originally Posted by vantilian
    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
    - old, slow, and confused
    ... but at least I'm inconsistent -

    (retired Excel 2003 user, 3.28.2008)

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    44

    Default Re: vlookup values matching on a partial text string

    Quote Originally Posted by shades
    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.

  6. #6
    Board Regular
    Join Date
    Dec 2002
    Posts
    1,199

    Default Re: vlookup values matching on a partial text string

    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.
    Lift up your eyes, round about, and see

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    44

    Default Re: vlookup values matching on a partial text string

    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.

  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    44

    Default Re: vlookup values matching on a partial text string

    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.

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,767

    Default Re: vlookup values matching on a partial text string

    See whether this

    http://www.mrexcel.com/pc07.shtml

    might help?

  10. #10
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default Re: vlookup values matching on a partial text string

    Along the lines proposed by Aladin, see also Alan's fuzzy match UDF set.
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com