+ Reply to Thread
Results 1 to 6 of 6

vlookup returning value one cell above what it should?

  1. #1
    Registered User
    Join Date
    11-12-2009
    Location
    United States
    MS-Off Ver
    Excel 2010 (or Excel 2003)
    Posts
    24

    vlookup returning value one cell above what it should?

    I've got a sheet with a bunch of names (first last in one cell) sorted in alphabetical order. It's data for NFL players.

    The table has several columns of values.

    On another section of the sheet, I have a place to type in some of the player names, and then VLOOKUP functions to retrieve a value from the table, so you can compare specific player statistics.

    Problem is, VLOOKUP is looking through the table and returning the value in the cell 1 spot ABOVE the reference it should be.

    So, if I had:

    Dave Jones, and asked Vlookup("Dave Jones", Table, Column 2)

    And the table is:

    Adam Smith 3
    Dave Jones 2
    Mark Wilson 5

    It would be returning value 3.

    The table is more complex than this, but it's weird it would return the value above. Any suggestions? I can post a screen shot if it will help.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: vlookup returning value one cell above what it should?

    Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook. Don't post screen shots.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    11-12-2009
    Location
    United States
    MS-Off Ver
    Excel 2010 (or Excel 2003)
    Posts
    24

    Re: vlookup returning value one cell above what it should?

    Quote Originally Posted by JBeaucaire View Post
    Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook. Don't post screen shots.
    Ok...here it is. I'm sure I'm making some stupid mistake, but I can't figure it out. I created a more simple table and VLookup worked just fine.

    qbs.xls

    Notice, for example -- Aaron Rodgers value is returned #n/a, as it's off the table (one cell above what it should be).

    [edit] If I shorten the names into their initials, it works. Does Excel freak out if the lookup value is too long a name?

    Hmm -- I guess that's the answer. The lookup values in the 1st column of the table can't have spaces. Dang.

    "When searching text values in the first column of table_array, ensure that the data in the first column of table_array does not have leading spaces, trailing spaces, inconsistent use of straight ( ' or " ) and curly ( ‘ or “) quotation marks, or nonprinting characters. In these cases, VLOOKUP may give an incorrect or unexpected value. For more information on functions that you can use to clean text data, see Text and Data functions."
    Last edited by thumper300zx; 11-24-2010 at 02:55 PM.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: vlookup returning value one cell above what it should?

    Change you B11 formula to:

    =VLOOKUP(A11&"*",$E$8:$W$40,4)


    ...or get rid of the errant spaces.

  5. #5
    Registered User
    Join Date
    11-12-2009
    Location
    United States
    MS-Off Ver
    Excel 2010 (or Excel 2003)
    Posts
    24

    Re: vlookup returning value one cell above what it should?

    Thanks! This method worked great for the small example. It's still screwy with a bigger table. But with the spaces removed, it's flawless.
    Last edited by thumper300zx; 11-25-2010 at 12:17 PM.

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: vlookup returning value one cell above what it should?

    Small correction:

    =VLOOKUP(A11&"*",$E$8:$W$40,4,FALSE)


    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1