Excel 2003 -#N/A
 

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.

Excel 2003 -#N/A
 

Maybe this

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

Mike

"C" wrote:

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.

Excel 2003 -#N/A
 

Try this approach:

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

Hope this helps.

Pete

On Jan 9, 2:54*pm, C wrote:
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.

Excel 2003 -#N/A
 

=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" wrote in message
...
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.

Excel 2003 -#N/A
 

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

"Mike H" wrote:

Maybe this

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

Mike

"C" wrote:

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.

Excel 2003 -#N/A
 

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

"Mike H" wrote:

Maybe this

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

Mike

"C" wrote:

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.

Excel 2003 -#N/A
 

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)

"C" wrote in message
...
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.