vlookup gives me a #N/A

D

dave

When I try to look with vlookup for a value that is not in the table
indicated, I get the message #N/A which indicates that the value cannot be
found (this is normal).

I would like to change this message #N/A into a value 0 or a blanc ""
because you cannot calculate with this value.

Is this possible??
 
A

arno

Is this possible??

only with something like:

=if(iserror(your vlookup);"";your vlookup)

where "your vlookup" is the formula you are currently using.

arno
 
R

Richard Buttrey

When I try to look with vlookup for a value that is not in the table
indicated, I get the message #N/A which indicates that the value cannot be
found (this is normal).

I would like to change this message #N/A into a value 0 or a blanc ""
because you cannot calculate with this value.

Is this possible??
Click to expand...

Just wrap your vlookup in an If statement. i.e.

If(isna(vlookup(blah_blah)),0,vlookup(blah_blah))

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
A

Aladin Akyurek

If you have this VLOOKUP formula, say, in 1000 or more cells, it might
be better to restort to formulas like:

=SUMIF(Range,"<>#N/A") for summing;

{=AVERAGE(IF(ISNUMBER(Range),Range,""))} for averaging;

and comparable formulas for other statistics than trying to suppress
#N/A's by formulas that compute the same thing twice like:

=IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...)).
When I try to look with vlookup for a value that is not in the table
indicated, I get the message #N/A which indicates that the value cannot be
found (this is normal).

I would like to change this message #N/A into a value 0 or a blanc ""
because you cannot calculate with this value.

Is this possible??
Click to expand...

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
 
B

Bob Phillips

=IF(ISNA(vlookup_formula),"",vlookup_formula)

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
A

Alamo

Excel automatically corrects this formula with 3 ))) at the end of the formula.

In my case it is:
=IF(ISNA(VLOOKUP(B44,itemlist!$A16:$B126,2)),0,(VLOOKUP(B44,itemlist!$A16:$B126,2)))

I do not want the 0 to show up on the form - how can it be blank?

Mike Boehmer
San Antonio, TX
Home of the NBA Champs - The SPURS-
 
D

Dave Peterson

=if(isna(vlookup(...)),"",vlookup(...))


Excel automatically corrects this formula with 3 ))) at the end of the formula.

In my case it is:
=IF(ISNA(VLOOKUP(B44,itemlist!$A16:$B126,2)),0,(VLOOKUP(B44,itemlist!$A16:$B126,2)))

I do not want the 0 to show up on the form - how can it be blank?

Mike Boehmer
San Antonio, TX
Home of the NBA Champs - The SPURS-
Click to expand...
 
Top