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

levanoj

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

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,770
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
298
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
298
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
23,770

ADVERTISEMENT

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

levanoj

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

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,509
Messages
5,511,714
Members
408,862
Latest member
sidneybc

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top