Zeros problem in LOOKUP?
 

I have a working LOOKUP function in a spreadsheet referencing another page,
but when I copy it, or even retype it in the other rows, it returns the #N/A
error. The only difference that I can see is that for some of the other rows,
there are 0 amounts- could that be messing it up? This is what the working
one is:
=VLOOKUP($AK7,Sheet5!D3:CE34,80,FALSE), and this the non working in the
next row:
=VLOOKUP($AK8,Sheet5!D3:CE34,80,FALSE)

Zeros problem in LOOKUP?
 

To answer your question, probably.

If you want to suppress #N/A errors, then do the following:
=IF(ISNA(VLOOKUP([your vlookup criteria]),"",VLOOKUP([your vlookup criteria]))

This basically says, in plain English "IF the VLOOKUP returns a #N/A error,
THEN return an empty string, ELSE do the VLOOKUP"

Dave
--
Brevity is the soul of wit.


"Danopnu" wrote:

I have a working LOOKUP function in a spreadsheet referencing another page,
but when I copy it, or even retype it in the other rows, it returns the #N/A
error. The only difference that I can see is that for some of the other rows,
there are 0 amounts- could that be messing it up? This is what the working
one is:
=VLOOKUP($AK7,Sheet5!D3:CE34,80,FALSE), and this the non working in the
next row:
=VLOOKUP($AK8,Sheet5!D3:CE34,80,FALSE)

Zeros problem in LOOKUP?
 

This would work, but the correct value is out in the range- its just not
bringing back the value like its supposed to- what I am trying to say is that
it should not be pointing to something that would return the #N/A error- it
has a solid number to look for

"Dave F" wrote:

To answer your question, probably.

If you want to suppress #N/A errors, then do the following:
=IF(ISNA(VLOOKUP([your vlookup criteria]),"",VLOOKUP([your vlookup criteria]))

This basically says, in plain English "IF the VLOOKUP returns a #N/A error,
THEN return an empty string, ELSE do the VLOOKUP"

Dave
--
Brevity is the soul of wit.


"Danopnu" wrote:

I have a working LOOKUP function in a spreadsheet referencing another page,
but when I copy it, or even retype it in the other rows, it returns the #N/A
error. The only difference that I can see is that for some of the other rows,
there are 0 amounts- could that be messing it up? This is what the working
one is:
=VLOOKUP($AK7,Sheet5!D3:CE34,80,FALSE), and this the non working in the
next row:
=VLOOKUP($AK8,Sheet5!D3:CE34,80,FALSE)

Zeros problem in LOOKUP?
 

If the list you are looking up from is not in alpha or numerical order it can
cause the #NA error.

"Danopnu" wrote:

This would work, but the correct value is out in the range- its just not
bringing back the value like its supposed to- what I am trying to say is that
it should not be pointing to something that would return the #N/A error- it
has a solid number to look for

"Dave F" wrote:

To answer your question, probably.

If you want to suppress #N/A errors, then do the following:
=IF(ISNA(VLOOKUP([your vlookup criteria]),"",VLOOKUP([your vlookup criteria]))

This basically says, in plain English "IF the VLOOKUP returns a #N/A error,
THEN return an empty string, ELSE do the VLOOKUP"

Dave
--
Brevity is the soul of wit.


"Danopnu" wrote:

I have a working LOOKUP function in a spreadsheet referencing another page,
but when I copy it, or even retype it in the other rows, it returns the #N/A
error. The only difference that I can see is that for some of the other rows,
there are 0 amounts- could that be messing it up? This is what the working
one is:
=VLOOKUP($AK7,Sheet5!D3:CE34,80,FALSE), and this the non working in the
next row:
=VLOOKUP($AK8,Sheet5!D3:CE34,80,FALSE)

Zeros problem in LOOKUP?
 

If you're retyping the formula, then this isn't the problem.

But if you're copying (or even dragging down the column), then that range on
Sheet5 will change with every row you drag down.

For instance, I get this on the second row after I drag it down:

=VLOOKUP($AK8,Sheet5!D4:CE35,80,FALSE)

Notice that the range on Sheet5 now points at D4:C35. If the matching data was
above that range, you'll get #n/a's.

I'd use:

=VLOOKUP($AK7,Sheet5!$D$3:$CE$34,80,FALSE)

so that those references on sheet5 don't change when I copy the formula.





Danopnu wrote:

I have a working LOOKUP function in a spreadsheet referencing another page,
but when I copy it, or even retype it in the other rows, it returns the #N/A
error. The only difference that I can see is that for some of the other rows,
there are 0 amounts- could that be messing it up? This is what the working
one is:
=VLOOKUP($AK7,Sheet5!D3:CE34,80,FALSE), and this the non working in the
next row:
=VLOOKUP($AK8,Sheet5!D3:CE34,80,FALSE)

--

Dave Peterson

Zeros problem in LOOKUP?
 

Two other things to look out for:

if you are looking up numbers, ensure that you really do have numbers
and not text values that look like numbers (i.e. may appear that the
value is in the table, but if one is text then a match will not be
found).

if you are looking up text values, ensure that you do not have leading
or trailing spaces (which are not visible, so the entry may "look" the
same).

Hope this helps.

Pete

Danopnu wrote:
This would work, but the correct value is out in the range- its just not
bringing back the value like its supposed to- what I am trying to say is that
it should not be pointing to something that would return the #N/A error- it
has a solid number to look for

"Dave F" wrote:

To answer your question, probably.

If you want to suppress #N/A errors, then do the following:
=IF(ISNA(VLOOKUP([your vlookup criteria]),"",VLOOKUP([your vlookup criteria]))

This basically says, in plain English "IF the VLOOKUP returns a #N/A error,
THEN return an empty string, ELSE do the VLOOKUP"

Dave
--
Brevity is the soul of wit.


"Danopnu" wrote:

I have a working LOOKUP function in a spreadsheet referencing another page,
but when I copy it, or even retype it in the other rows, it returns the #N/A
error. The only difference that I can see is that for some of the other rows,
there are 0 amounts- could that be messing it up? This is what the working
one is:
=VLOOKUP($AK7,Sheet5!D3:CE34,80,FALSE), and this the non working in the
next row:
=VLOOKUP($AK8,Sheet5!D3:CE34,80,FALSE)

Zeros problem in LOOKUP?
 

Vlookup sometimes gives some errors like this. Give a 'name' to your range
and put the name in place of (Sheet5!D3:CE34). This will work if it has a
solid number to look for.

Regards,

Jaleel

"Danopnu" wrote:

This would work, but the correct value is out in the range- its just not
bringing back the value like its supposed to- what I am trying to say is that
it should not be pointing to something that would return the #N/A error- it
has a solid number to look for

"Dave F" wrote:

To answer your question, probably.

If you want to suppress #N/A errors, then do the following:
=IF(ISNA(VLOOKUP([your vlookup criteria]),"",VLOOKUP([your vlookup criteria]))

This basically says, in plain English "IF the VLOOKUP returns a #N/A error,
THEN return an empty string, ELSE do the VLOOKUP"

Dave
--
Brevity is the soul of wit.


"Danopnu" wrote:

I have a working LOOKUP function in a spreadsheet referencing another page,
but when I copy it, or even retype it in the other rows, it returns the #N/A
error. The only difference that I can see is that for some of the other rows,
there are 0 amounts- could that be messing it up? This is what the working
one is:
=VLOOKUP($AK7,Sheet5!D3:CE34,80,FALSE), and this the non working in the
next row:
=VLOOKUP($AK8,Sheet5!D3:CE34,80,FALSE)