Vlookup possible errors

[macro_user]

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!

 

[JamesW]

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.

 

[dave3009]

#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

 

[GlennUK]

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.