Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Vlookup gives me #ref error

  1. #1
    Board Regular
    Join Date
    Oct 2007
    Location
    Detroit area
    Posts
    996
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Vlookup gives me #ref error

    I have a list of unique numbers (assignment numbers) on a work sheet, this list SHOULD match another worksheet I have open . . but they don't always, and I want to know what the value is in the column that should match (column B).

    So I did a Vlookup . ....

    =VLOOKUP(A35,'[December EXPORT HTS TRACKER.xls]Export Tracker'!$A$2:$A$500,2,0)

    (A35 is the first row on my spreadsheet where this function lives. Column A on both worksheets is the same value, both sorted ascending.)

    I get a Ref# error . . . .if I change the formula to ...$A$2:$A$500,1,0 it returns the correct assignment #, and all cells are populated.

    thoughts?

  2. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup gives me #ref error

    You're lookup range needs to be at least as many columns wide as your colref # (2).

    so your range should cover columns A & B (2 columns) since you put 2 as the colref.

    =VLOOKUP(A35,'[December EXPORT HTS TRACKER.xls]Export Tracker'!$A$2:$B$500,2,0)

    If colref was 3, it would have to be

    =VLOOKUP(A35,'[December EXPORT HTS TRACKER.xls]Export Tracker'!$A$2:$C$500,3,0)
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  3. #3
    Board Regular
    Join Date
    May 2007
    Posts
    181
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup gives me #ref error

    Because your only looking at column 'A'. When you do (,2,false) you are looking in the 2nd column which is B. You will get a ref error. When using vlookup your column that you look for the data in should be as many columns in your lookup table.


    So, if you do vlookup(value,A1:A500,1,false) this is OK, then if you want the 2nd column you have to do (value, A1:B500,2,false)

  4. #4
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,092
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup gives me #ref error

    The column index for VLOOKUP is 2, yet the lookup table A2:A500 only consists of only one column, which necessarily returns #REF!. If you'd like to return the corresponding value in Column B, change...

    $A$2:$A$500

    to

    $A$2:$B$500

    Hope this helps!

  5. #5
    Board Regular
    Join Date
    Oct 2007
    Location
    Detroit area
    Posts
    996
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup gives me #ref error

    AHHHH, duh, I knew that after I read your reply twice!! I guess I should have asked my self, "how can I find a SECOND column, if i am only searching ONE columb ($A2:A500). when I changed the A500 to L500, it works fine. THANKS!!!! I love you guys!!!

Some videos you may like

User Tag List

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
  •