error #N/A

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a lookup formula that sometime returns a #N/A error. I would like a 0
to appear if the lookup result is n/a.

Here is my formula: =VLOOKUP(E3,'Source Pivot Master'!$A$3:$W$60000,7,FALSE)

Thank you.
 
In general, you can use
=IF(ISNA(expression),0,expression)
but that involves evaluating the expression twice. If the lookup table is
large, you may prefer to simply hide the error value with conditional
formatting.

Jerry
 
Thank you so much I appreciate your help.

Jerry W. Lewis said:
In general, you can use
=IF(ISNA(expression),0,expression)
but that involves evaluating the expression twice. If the lookup table is
large, you may prefer to simply hide the error value with conditional
formatting.

Jerry
Click to expand...
 
If you have Excel 2007, you can use the IFERROR function, which is much
neater. I'm not sure if 2007 was the first version to have IFERROR.
 
Downside to using ISERROR is that other inadvertant errors will also be
hidden by use of that function. Jerry's ISNA solution is a safer one to
use.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------­------------------------------­----------------
 
Yes it was

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------­------------------------------­----------------
 
Unless of course there are other formulas depending on those cells...... Hi
Jerry :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------­------------------------------­----------------
 
Many thanks.

But if you are using the result is referenced in another cell, this work well.
 
Back
Top