Results 1 to 5 of 5

Thread: Vlookup only returning the last of duplicate values
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2011
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Vlookup only returning the last of duplicate values

    I have a sheet with data similar to the following. (Sheet1)

    ........Col1 .................Col2
    1 25145 Allure........ =VLOOKUP(A1,Sheet2!$A$1:$B$5,2)
    2 25146 Allure
    3 25147 Oak
    4 25147 Pine
    5 25147 Ash

    The first column of the second sheet (Sheet2) contains the street address (common field) and other data I would like to merge over to the first using the Vlookup formula on the first page in column 3.

    .........Col1 ...........Col2
    1 25145 Allure ....POBox459
    2 25146 Allure ....POBox125
    3 25147 Oak ......POBox156
    4 25147 Pine ......POBox784
    5 25147 Ash ......POBox365

    The formula works fine except where there are values that are partial duplicates such as 25147 Oak and 25147 Ash in which case the Vlookup returns the last (bottom) value for all of the rows like this:

    ........Col1 ..........Col2
    1 25145 Allure ..... POBox 459
    2 25146 Allure...... POBox 125
    3 25147 Oak ........ POBox 365
    4 25147 Pine ....... POBox365
    5 25147 Ash .........POBox 365

    Rows are formatted as text and are sorted A-Z and the common fields between sheets appear to be identical (no spaces or other weirdness). Can anybody help me figure out how to correct this issue? It appears that the formula is only looking at the first numerical part of each entry.
    Many Thanks

  2. #2
    Board Regular
    Join Date
    Jun 2002
    Posts
    2,128
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup only returning the last of duplicate values

    Hello, you have omitted the last part of the vlookup formula. By doing so, you are telling it to find an exact or partial match. Change your formula to:

    =VLOOKUP(A1,Sheet2!$A$1:$B$5,2,False)

    If you don't enter this argument, it defaults to True, which is partial match, so always end your vlookups with False (or you can just put "0" without quotes, it's faster)

    =VLOOKUP(A1,Sheet2!$A$1:$B$5,2,0)

  3. #3
    New Member
    Join Date
    May 2011
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup only returning the last of duplicate values

    I've tried adding the additional FALSE argument to the end. Unfortunately, this changes all results to #N/A.

  4. #4
    Board Regular
    Join Date
    Jun 2002
    Posts
    2,128
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup only returning the last of duplicate values

    Hmm, that says no match is found, which implies the spelling is slightly different, but you said you checked that already....

    Sometimes Vlookups struggle with text vs. numbers. Try putting an apostrophe in front of two or three sample items. One that you use as the lookup, and the other in the lookup list. See if that works.

  5. #5
    New Member
    Join Date
    May 2011
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup only returning the last of duplicate values

    Problem Solved. One column contained an extra space between the numerical and text portions of the address. Used a find/replace to remove the extra spaces and voila! Thanks for jogging my brain.

Some videos you may like

User Tag List

Tags for this Thread

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
  •