Vlookup #N/A Error

[Mike]

The Vlookup formula returns #n/a instead of a number in several rows in my
worksheet. If I retype the lookup number or add a ' in front of the lookup
number, the formula will work. However, I have hundreds of rows of data and
would have many #N/A's to correct using this method. Does anybody know how
to correct the formula, etc. to prevent this?

 

[Pranav Vaidya]

One common error is referencing.
Check if the grid that you are referring to is absolute, that is does it
have $ before the row and column.

$A$1:$A$1000 is the correct method.

Also, if you are referring to numbers, if the numbers are stored as text you
get #N/A.

 

[Mike]

Thanks. I tried both but to no avail. For simplicity, below is what my
spreadhseet is showing:

63311 40813 #N/A

where the N/A should read "40813" and my formula is
=VLOOKUP(A3,B3,3,FALSE). Also, the worksheet and each cell have been
formatted as numbers. Any ideas?

Thanks,
Mike

 

[David Biddulph]

The second argument for VLOOKUP should be an array. You've given it a
single cell reference. I'm surprised that it gives you an answer at all, as
I would expect it to object to the invalid syntax.

 

[Stephen Russell]

When you add the " ' " it changes to text. You can't compare a text number
to a number. Check your format.