How do I replace a "#N/A" formula result with a blank in excel?

[Guest]

As a result of a Vlookup formula a cell gives #N/A. The formula and the data
that it looks up is good. It should give a #N/A. I need the cell instead of
displaying #N/A to display nothing (blank) without deleting the formula.

 

[Barb Reinhardt]

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

 

[Anne Troy]

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

************
Hope it helps!
Anne Troy
www.OfficeArticles.com

 

[Guest]

if(isna(your lookup formula),"",your lookup formula)

 

[Max]

An alternative to direct error-trapping would be to use conditional
formatting to mask the display, with the active cell formula, eg: =
ISNA(A1), then choosing a font color to match the cell's fill color
(eg applying a white font color, which blends with the default cell fill of
"no colour")

 

[Max]

Example:

Assuming the VLOOKUPs are say, in A1:A10

To mask the possible returns of #N/As
within the VLOOKUP range with conditional formatting ..

Select A1:A10
(ensure A1 is the active cell)

Click Format > Cond Formatting
Formula is: =ISNA(A1)
Click Format button > Font tab > Font color white*? > OK
Click OK at the main dialog
*choose a font color which matches the cell's fill color


---

 

[Guest]

=IF(ISNA(VLOOKUP)),"",VLOOKUP())