error #N/A

[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.

 

[Guest]

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

 

[Guest]

Thank you so much I appreciate your help.

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

 

[David North]

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.

 

[Chris]

Try ISERROR() formula to solve the problem:
=if(iserror(VLOOKUP(E3,'Source Pivot Master'!$A$3:$W$60000,7,FALSE),0,
VLOOKUP(E3,'Source Pivot Master'!$A$3:$W$60000,7,FALSE))
Regards

url:http://www.ureader.com/msg/10302308.aspx

 

[Ken Wright]

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
------------------------------­------------------------------­----------------

 

[Ken Wright]

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
------------------------------­------------------------------­----------------

 

[Ken Wright]

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
------------------------------­------------------------------­----------------

 

[Dove Red Robin]

Many thanks.

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