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

levanoj

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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,270
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
311
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
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
311
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,270
Did you want Conditional Formatting or a formula to put a value in a cell?
 

levanoj

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

Forum statistics

Threads
1,176,351
Messages
5,902,643
Members
434,985
Latest member
jessika2105

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