Make #N/A blank if no value is returned

[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?

 

[Chris Clarke]

use if and isna

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

 

[Gav123]

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

 

[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.

 

[Dave Peterson]

And if you're using xl2007, take a look at =iferror() in Excel's help.

 

[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

 

[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?

Try

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

Enter

 

[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

 

[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?

Try this:

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

Allan Rogg

 

[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

 

[lvms]

You could try

isna(vlookup....)

or

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

 

[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