Conditional Formatting #N/A

[Jman1018]

I have a VLOOKUP looking up a value on another sheet. I works fine but when
it can't find the value it shows a #N/A. I don't want to see the #N/A in any
of the feilds on this sheet and I know I need to do a conditional format and
turn the text to the same color as the backgroud color. I just don't know
the correct "Formula Is" statement.

 

[Dave Peterson]

With A1 the activecell, you could use:
=isna(a1)

You may want to modify your =vlookup() formula, too:

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

 

[John C]

You could just change your formula.
=IF(ISNA(VLOOKUP(...,...,...,FALSE)),"",VLOOKUP(...,...,...,FALSE))

 

[Chip Pearson]

In the Conditional Formatting dialog, change "Cell Value Is" to
"Formula Is" and use a formula like the following and select your
formatting options.

=IF(ISERROR(E4),IF(ERROR.TYPE(E4)=7,TRUE,FALSE),FALSE)

The formula tests if cell E4 has an error and if so, then tests
whether it is a #N/A error (type = 7). Note that you do need the
ISERROR function as shown. The ERROR.TYPE function itself returns an
error if the referenced cell does not contain an error.

Change the references to E4 to the appropriate cell.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Jman1018]

Works great! Thank you.
--
Jman1018

You could just change your formula.
=IF(ISNA(VLOOKUP(...,...,...,FALSE)),"",VLOOKUP(...,...,...,FALSE))

 

[ShaneDevenshire]

Hi,

If you are using 2007 a better formula would be

=IFERROR(VLOOKUP(A1,C1:N100,3,0),"")

Not only is it shorter but it uses less computer power, so it runs faster.

A short conditional format would be to choose Formula is and enter
=ISNA(B1) and choose a font color of white.
This approach has somewhat limited usefulness because if the cell is
formatted with a background you need to adjust the format depending on the
cell fill color.