Using Conditional Formatting to remove #N/A & 0 on Vlookup

[levanoj]

I'm trying to using the Conditional Formatting feature on Excel 2007 to have the text within the cells change to a White Font anytime the Vlookup formula within that cell returns a value of "#N/A" or "0".
I'm basically just trying to hide the "#N/A" or "0" errors when the vlookup doesn't find any value.

 

[mikerickson]

The Conditional Formatting formula =OR(ISNA(A1),A1="") setting the font color to white should do what you want.

(I prefer to use light blue, it allows me to be aware of what is being returned and also concentrate on the non-null values.)

 

[prabby25101981]

In Conditional Formatting criterion, select Formula is and enter this in the criteria -

=OR(ISERROR(A2),A2=0)

Format it in white font. I have taken A2 here. You will have to give reference to the cell where you are applying this format.

 

[levanoj]

I tried your formula mikerickson and it didn't work.

I tried yours also prabby25101981 and it only partially worked.
The font changed to White only when the returned vlookup value was "0" but not when it was "#N/A".

Am I doing something wrong?

 

[nancymk]

Can you just change your formula not to show the #n/a?

I do this all the time.

=if(isna(vlookup(value,range,col,false)),"",vlookup(value,range,col,false))

This formula says if the the vlookup gives results in #n/a, then put a blank, otherwise, put the vlookup. You can change the "" to be whatever you result you want, if you don't want a blank.

 

[levanoj]

thanks nancymk, I appreciate this formula as well but would there be any way to tweek it to also give me a blank when the result is "0"?

 

[mikerickson]

Did you want Conditional Formatting or a formula to put a value in a cell?

 

[levanoj]

well a vlookup formula will definitely be within the cell so which ever method (conditional formatting or formula) that can give me blank cell anytime the vlookup returns a "#N/A" or "0" value I'm fine with.

 

[Jonmo1]

My preferred method would be to use the formula nancymk gave to remove the #N/A

AND

Click Tools - Options - View and UNcheck "Zero Values"

 

[levanoj]

That did the trick, thanks Jonmo1