Make #N/A blank if no value is returned

B

Babs in Ohio

Using VLOOKUP some of my values are #N/A. The formula I am using is:

=VLOOKUP(1,$X$10:$Z$17,3,FALSE)

If the value returned is #N/A how can I make it display blank or null?
 
C

Chris Clarke

use if and isna

=if(isna(VLOOKUP(1,$X$10:$Z$17,3,FALSE)),VLOOKUP(1,$X$10:$Z$17,3,FALSE),)
 
S

swati

you can also use conditional formatting in the spreadsheet..
use the formula =iserror(ref) where ref is the reference to the cells in the
worksheet. With this, use the formatting as text color white. You can
actually apply this to the entire worksheet and not just for the specific
#N/A cells. it would work coz the formula and formatting would make the cells
having #N/A blank.
 
P

Pete_UK

You've probably already had your answer to this, as Google Groups
hasn't updated since about 2pm yesterday, but in case you haven't then
try this:

=IF(ISNA(VLOOKUP(1,$X$10:$Z$17,3,FALSE)),"",VLOOKUP(1,$X$10:$Z
$17,3,FALSE))

Hope this helps.

Pete
 
S

Santipong

Using VLOOKUP some of my values are #N/A. The formula I am using is:

=VLOOKUP(1,$X$10:$Z$17,3,FALSE)

If the value returned is #N/A how can I make it display blank or null?
Click to expand...


Try

=If(Countif($X$10:$X$17,1)>0,Vlookup(1,$X$10:$Z$17,3,FALSE),"")

Enter
 
K

krcowen

Babs

One way is:

=if(VLOOKUP(1,$X$10:$Z$17,1)=1,=VLOOKUP(1,$X$10:$Z$17,3) ,"")

Good luck.

Ken
Norfolk, Va
 
A

arogg

Using VLOOKUP some of my values are #N/A. The formula I am using is:

=VLOOKUP(1,$X$10:$Z$17,3,FALSE)

If the value returned is #N/A how can I make it display blank or null?
Click to expand...


Try this:

=IF(ISNA(VLOOKUP(1,$A$10:$C$17,3,FALSE))," ",VLOOKUP(1,$A$10:$C
$17,3,FALSE))

Allan Rogg
 
M

Mark Lincoln

Try this (not tested):

=IF(ISNA(VLOOKUP(1,$X$10:$Z$17,3,FALSE)),"",VLOOKUP(1,$X$10:$Z
$17,3,FALSE))

Mark Lincoln
 
L

lvms

You could try

isna(vlookup....)

or

iserror(vlookup...) for including other errors such as #REF!
 
J

jiwolf

the easiest way is =
if(iserror(VLOOKUP(1,$X$10:$Z$17,3,FALSE)),"",(VLOOKUP(1,$X$10:$Z$17,3,FALSE))

This way a "blank" is displayed
 
Top