Vlookup possible errors
Results 1 to 4 of 4

Thread: Vlookup possible errors
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2009
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Vlookup possible errors

    hi,

    i am trying to use VLOOKUP to populate a cell's value. I noticed that i get different types of error fields populated when a value is wrong/not found. Can someone explain to me the reasons why we get these different types of error fields:

    #N/A
    #NAME?
    #REF?
    =VLOOKUP(B2,F:G,2,FALSE)

    in the 4th case, Im sure that a value is present in the column where i am looking in but the formula doesn't seem to have gotten executed. It just displays the formula the same way.

    Appreciate the help.

    Thanks!
    Last edited by macro_user; Dec 9th, 2010 at 05:20 AM. Reason: typo

  2. #2
    Board Regular JamesW's Avatar
    Join Date
    Oct 2009
    Location
    Basingstoke, England
    Posts
    1,197
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup possible errors

    Hey,

    #N/A means that the value you are trying to find in your table has not been found.
    #NAME usually means that the function has been typed in incorrectly (VLOKUP for example)
    #REF means you are trying to lookup from a cell that doesn't exist, it could be that you moved the function from one cell to another and the auto calc thing that moves the cells in the function has moved to a row above row 1 (which is not possible).

    As for number 4. Make sure the cell is formatted correctly (number/date etc) and then once formatted click on it, press F2 and then enter.
    HTH, James

    Light travels faster than sound. This is why some people appear bright before you hear them speak.

    Time is an illusion. Lunchtime doubly so.


  3. #3
    Board Regular dave3009's Avatar
    Join Date
    Jun 2006
    Location
    Inverclyde, Scotland
    Posts
    6,996
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Vlookup possible errors

    #N/A is probably the more common error with VLOOKUP, it just means the value you're looking for does not exist.

    #NAME means you've typed in an incorrect name

    #REF means the range you are referencing no longer exists

    The last error probably occurs if your cells are formatted as text, I get this when I import CSV files.

    HTH


    Dave
    Please state your version of Excel, I use Excel 2007 on Win 10.
    Back up all data and test VBA code on a COPY workbook, and please use [code][/code] tags.
    HTML Maker | HTML Maker - FAQ

  4. #4
    Board Regular
    Join Date
    Jul 2002
    Location
    Milton Keynes, England
    Posts
    11,044
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Vlookup possible errors

    You'll get #N/A when a value isn't found.
    You'll get #NAME? if you've misspelled something in the formula or the source area.
    You'll get #REF! if you try to reference something that doesn't exist, for example choosing column 3 of a table when the table only has 2 columns.
    And you'll get the formula instead of the result when the cell is formatted as text ... reformat as General ( or number ) and then reenter the formula.
    Cheers, Glenn.

    Beauty is in the eye of the beer-holder.

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
  •