If function formula to look for "#N/A" in cell

[S. H. Drew]

I am trying to create an "IF" formula that will return a value of zero if a
VBLOOKUP formula returns "#N/A". I cannot get the IF function to recognize
when "#N/A" is what's returned by the lookup formula. Below is if the
formula I am trying to make work, with no success thus far. Anyone know how
to get an IF function to recognize when a lookup formula returns "#N/A"?

=IF((VLOOKUP(A3,'H:\Marketing\Sales Planning\Excel\2004 Monthly\[Unshipped
order by part #.xls]Jane''s CS, GB, SC
Dom'!$A$2:$B$41,2,FALSE))="#N/A",0,(VLOOKUP(A3,'H:\Marketing\Sales
Planning\Excel\2004 Monthly\[Unshipped order by part #.xls]Jane''s CS, GB, SC
Dom'!$A$2:$B$41,2,FALSE)))

 

[Frank Kabel]

Hi
try
=IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...))

 

[Alex]

I think you should try using the =ISNA function.

As an example...

=IF(ISNA(VLOOKUP(6,B1:C5,2,FALSE)),"0","")

Here if the VLOOKUP function returns #N/A, then the IF
function will return a "0".

So in general you need to parenthesise your VLOOKUP
function with ISNA i.e ISNA(...VLOOPKUP...)

See how you go...

Alex

 

[Paul G]

The one i have used in the past is =IF(ISERROR(VLOOKUP(...)),0,VLOOKUP(...))

Hi
try
=IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...))

--
Regards
Frank Kabel
Frankfurt, Germany

I am trying to create an "IF" formula that will return a value of zero if a
VBLOOKUP formula returns "#N/A". I cannot get the IF function to recognize
when "#N/A" is what's returned by the lookup formula. Below is if the
formula I am trying to make work, with no success thus far. Anyone know how
to get an IF function to recognize when a lookup formula returns "#N/A"?

=IF((VLOOKUP(A3,'H:\Marketing\Sales Planning\Excel\2004 Monthly\[Unshipped
order by part #.xls]Jane''s CS, GB, SC
Dom'!$A$2:$B$41,2,FALSE))="#N/A",0,(VLOOKUP(A3,'H:\Marketing\Sales
Planning\Excel\2004 Monthly\[Unshipped order by part #.xls]Jane''s CS, GB, SC
Dom'!$A$2:$B$41,2,FALSE)))