Excel 2003 -#N/A

[C]

I have a VLookup which is returning #N/A, how can I have the formula return a
zero "0" in its place? I need to sum and the sheet is very large. Thanks for
any help.

 

[Mike H]

Maybe this

=IF(ISNA(VLOOKUP(C1,A1:B6,2,FALSE)),"",VLOOKUP(C1,A1:B6,2,FALSE))

Mike

 

[Pete_UK]

Try this approach:

=IF(ISNA(your_formula),0,your_formula)

Hope this helps.

Pete

 

[Bob Phillips]

=IF(ISNA(vlookup_formula),0,vlookup_formula)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[C]

Mike
Thanks, but the formula is showing invalid, it has a problem with ,"", and
if I take that out I am still getting #N/A.
C

 

[C]

Mike, Thank you! I had a problem with the parentheses, and it now works.

 

[Chip Pearson]

Others have shown the correct workaround formula:
=IF(ISNA(your_formula),0,your_formula). This has the drawback that
your_formula is typically evaluated twice -- first to test for NA and again
for the result in the False clause of the IF function. This is unavoidable
and may cause slower calculations. Just FYI and for anyone else reading
this, Excel 2007 introduced a new function named IFERROR. This eliminates
the need for the second your_formula:

=IFERROR(your_formula,0)

Here, your_formula is evaluated only once, not twice. If your_formula
doesn't return an error, its result is returned. If your_formula does result
in an error, 0 is returned. Alas, this is not available in 2003 and earlier.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLC
www.cpearson.com
(email on the web site)