#N/A Workaround

**mcantrell** · 08-12-2010, 03:00 PM

I have a Vlookup that is returning the value #N/A. Is there a way to enter a conditional format:
=If(a1="#N/A",1,0)
I tried this equation and it did not work.
Is there anyway to work around this?

**oldchippy** · 08-12-2010, 03:13 PM

Hi,

Explore this

http://www.contextures.com/xlfunctions02.html#Trouble

**teylyn** · 08-12-2010, 08:40 PM

There is the ISNA() function you can use to check if a cell contains that error

=IF(ISNA(A1),1,0)

**mcantrell** · 08-16-2010, 03:53 PM

used an IFERROR statement...thx

**teylyn** · 08-16-2010, 11:08 PM

mcantrell, using IfError may not be such a good idea, because IfError does not distinguish between the different kinds of errors. #N/A means that the lookup value can not be found. #Ref! or #Value! or #Div/0 would also be masked by IfError and you may have a hard time debugging if things go wrong.

cheers

**arthurbr** · 08-17-2010, 02:35 AM

You might also adapt your vlookup as follows

=if(countif(b1:b100,a1),vlookup(a1,b1:d100,col, false),"")

where b1:b100 is the first col of your lookup table, a1 the value to lookup, b1:d100 the lookuptable and col the column nr where to look up.