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?
--
Thanks,
Mike

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.

--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"Mike" wrote:

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?
--
Thanks,
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




--
Mike


"Pranav Vaidya" wrote:

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.

--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"Mike" wrote:

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?
--
Thanks,
Mike

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.
--
David Biddulph

"Mike" wrote in message
...
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

"Pranav Vaidya" wrote:

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.

--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"Mike" wrote:

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?
--
Thanks,
Mike

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


"Mike" wrote in message
...
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?
--
Thanks,
Mike