Remove #N/A value from Excel Sheet

[kime1974]

I am using Excel 2003 and have a vlookup formula -

=VLOOKUP(A3,'3rdQuarter'!$B$2:$D$60,3,FALSE)

When no results are found it returns #N/A on the sheet. The user does not want to see this. I found where you can change how it prints to blank or -, but need to change how it is displayed.

Thanks!
Kim

 

[bluedragon2]

embed your formula in an IF statement like:

=IF(ISNA(VLOOKUP(A3,'3rdQuarter'!$B$2:$D$60,3,FALSE)),"",VLOOKUP(A3,'3rdQuarter'!$B$2:$D$60,3,FALSE))


[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

Or...

you can use conditional formatting to shade the font of cells containg NA() the same as the cell interior shade (white in most cases).

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[yogia]

Hi Kim:

Or you can use ...

=IF(COUNTIF($B$2:$B$60,A3),VLOOKUP(A3,'3rdQuarter'!$B$2:$D$60,3,FALSE),"")

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[kime1974]

Thanks all!