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

levanoj

Active Member
Joined
Oct 25, 2007
Messages
308
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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,022
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

Active Member
Joined
Jul 28, 2010
Messages
348
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

Active Member
Joined
Oct 25, 2007
Messages
308
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

Board Regular
Joined
Jul 24, 2008
Messages
106

ADVERTISEMENT

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

Active Member
Joined
Oct 25, 2007
Messages
308
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,022

ADVERTISEMENT

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

levanoj

Active Member
Joined
Oct 25, 2007
Messages
308
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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"
 

Watch MrExcel Video

Forum statistics

Threads
1,133,278
Messages
5,657,822
Members
418,414
Latest member
ECMdusty

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top