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

VLOOKUP Question on text strings

This is a discussion on VLOOKUP Question on text strings within the Excel Questions forums, part of the Question Forums category; I have a worksheet that has a column of names in it. I'm trying to VLOOKUP to another sheet, column ...

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Arkansas
    Posts
    157

    Default

    I have a worksheet that has a column of names in it. I'm trying to VLOOKUP to another sheet, column A, and returning the value in Column B if it finds the lookup.

    All columns are text columns with names.

    When I use the 'true' option in the vlookup, the results are about 50% accurate, and if I use 'false'then it's only about 5% accurate. The problem is that the spelling varies between the two columns. Is there an accurate way to do this?

    Say on sheet1 in column B I have the vlookup:

    =(C1,'sheet'!A1:B800,2,false) or
    =(C1,'sheet'!A1:B800,2,true)

    Can you vlookup with a partial string? I have about 17000 lines that I need to populate with this.

  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    Hi boydr:

    In reference to your question regarding use of partial strings in using VLOOKUP function, YES! you can -- see the following simulation:

    ******** LANGUAGE="JavaScript" ************************************************************************>
    Microsoft Excel - y030115h1.xls___Running: xl97 : OS = Windows 98
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    1
    ******
    2
    MrExcel100200*oyd201
    3
    boydr101201*I*used*a*partial*string*from*boydr
    *entry*to*lookup*the*value*in*the
    *third*column*of*the*lookup*table
    4
    PaddyD102202*
    5
    JoeWas103203*
    6
    Maxflia10104204***
    Sheet7*

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.



    Please post back if it works for you -- otherwise explain a little further and let us take it from there.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Arkansas
    Posts
    157

    Default

    Yogi,

    Thank you for responding. I think that is what I need, but can you explain what the 0 represents at the end of the command? I'm only familiar with true or false.

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    On 2003-01-15 23:31, boydr wrote:
    Yogi,

    Thank you for responding. I think that is what I need, but can you explain what the 0 represents at the end of the command? I'm only familiar with true or false.
    Hi boydr:

    We are talking about the same thing -- You can use 1 for TRUE ; 0 for FALSE
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    Arkansas
    Posts
    157

    Default

    And,

    Your vlookup worked on part of it.

    Can you tell me why this might not work?
    ==VLOOKUP("*"&LEFT(B4,3)&"*",Sheet2!A5:B6,2,0)

    On sheet2:
    Vlookup array table looks simplar to:
    Column A Column B
    IBM Corp. IBM Corporation
    IBM Inc. IBM Corporation
    IBM LLC IBM Corporation
    Jimmy Dean Sara Lee
    State Fair Sara Lee
    ETC...

    Source in Column B sheet1
    IBM LLC
    Jimmy Dean
    State Fair
    Etc.
    Formula is in Column A sheet1
    (I want it to pull from vlookup array)



  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    And,

    Your vlookup worked on part of it.

    Can you tell me why this might not work?
    ==VLOOKUP("*"&LEFT(B4,3)&"*",Sheet2!A5:B6,2,0)
    Hi boydr:

    1. What is the complete entry that makesup the LOOKUP value?
    2. What string are you using with what wild cards in the VLOOKUP formula?
    3. What is your range for the LOOKUP table?

    If you can supply that we can look at where the problem might be -- I don't think the LookupTable being in another sheet is the issue.


    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,362

    Default

    On 2003-01-15 23:49, boydr wrote:
    And,

    Your vlookup worked on part of it.

    Can you tell me why this might not work?
    ==VLOOKUP("*"&LEFT(B4,3)&"*",Sheet2!A5:B6,2,0)

    On sheet2:
    Vlookup array table looks simplar to:
    Column A Column B
    IBM Corp. IBM Corporation
    IBM Inc. IBM Corporation
    IBM LLC IBM Corporation
    Jimmy Dean Sara Lee
    State Fair Sara Lee
    ETC...

    Source in Column B sheet1
    IBM LLC
    Jimmy Dean
    State Fair
    Etc.
    Formula is in Column A sheet1
    (I want it to pull from vlookup array)
    Although it is possible to do partial matching using VLOOKUP, it will not be a full-fledged "fuzzy match"...

    By the way, if it's possible for you to use the morefunc.xll, you can make the retrieval task a lot faster.

    Consider the following lookup table...

    ******** ******************** ************************************************************************>
    Microsoft Excel - aaPartialMatchVlookup boydr.xls___Running: xl2000 : OS = Windows NT 4
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    4
    ****
    5
    IBM*Corp.IBM*Corporation**
    6
    IBM*Inc.IBM*Corporation**
    7
    IBM*LLCIBM*Corporation**
    8
    Jimmy*DeanSara*Lee*1**
    9
    State*FairSara*Lee*2**
    10
    The*IBM*corporationIBM*Corporation**
    11
    ****
    12
    ****
    Sheet2*

    [HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    What follows shows how you can set up VLOOKUP for partial matching...

    ******** ******************** ************************************************************************>
    Microsoft Excel - aaPartialMatchVlookup boydr.xls___Running: xl2000 : OS = Windows NT 4
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    2
    ****
    3
    *Lookup*values*Result
    4
    *IBM*LLCIBMIBM*Corporation
    5
    *Jimmy*DeanJimmySara*Lee*1
    6
    *State*FairStateSara*Lee*2
    7
    *IBMIBMIBM*Corporation
    8
    ****
    9
    ****
    10
    ****
    Sheet1*

    [HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.





  8. #8
    Board Regular
    Join Date
    Apr 2002
    Location
    Arkansas
    Posts
    157

    Default

    On 2003-01-16 00:08, Yogi Anand wrote:
    And,

    Your vlookup worked on part of it.

    Can you tell me why this might not work?
    ==VLOOKUP("*"&LEFT(B4,3)&"*",Sheet2!A5:B6,2,0)
    Hi boydr:

    1. What is the complete entry that makesup the LOOKUP value?
    2. What string are you using with what wild cards in the VLOOKUP formula?
    3. What is your range for the LOOKUP table?

    If you can supply that we can look at where the problem might be -- I don't think the LookupTable being in another sheet is the issue.



    Yogi,
    1)The lookup range varies (have 30 sheets to run this on) but the first range is on sheet1
    B2:B838
    2) ==VLOOKUP("*"&LEFT(B4,3)&"*",Sheet2!A5:B6,2,0)
    3) Lookup range is sheet2:A2:B1288

  9. #9
    Board Regular
    Join Date
    Apr 2002
    Location
    Arkansas
    Posts
    157

    Default

    Aladin,

    Thanks for the response, but I can't use that addin (unfortunately.) I'm doing this at work and can't install programs. I think you may be right in that I'm using fuzzy logic, and it's very difficult to get the rate match in my situation.

  10. #10
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default

    Yogi,
    1)The lookup range varies (have 30 sheets to run this on) but the first range is on sheet1
    B2:B838
    2) ==VLOOKUP("*"&LEFT(B4,3)&"*",Sheet2!A5:B6,2,0)
    3) Lookup range is sheet2:A2:B1288
    Hi boydr:

    1. Can you post what is housed in cell B4 in your VLOOKUP formula (that should be on sheet1 --right?)
    2. Can you post what is indeed the looked up value (which comprises the string in cell B4 of sheet1)
    3. Can you post the contents of the Lookup table A5:B6 -- these should be from sheet2 -- right?

    This way we can determine what you are looking at and analyze what is breaking down.

    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

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