[Solved] How to Remove #N/A from IF VLOOKUP result

[GUNTTURSRINIWAS]

Hi !!!!
I searched all posts by subject which I felt were relevant to my subject mentioned problem and did not find one. I have given the sample workbook as attachment for your reference. I have tried all the tricks available up my sleeve but ended up in failure. Kindly sort out the problem if anyone can. It was possible to remove when I used single worksheet.

Thanks,
Regards
Sriniwas
Delhi,India

[Villeroy]

What is wrong with error #N/A
It indicates that the value could not be looked up.

[GUNTTURSRINIWAS]

Ur right Villeroy but I just want a 0(zero) instead of #NA for such cells. Is it possible with the kind of function Iam using else please guide me but I would like to replace #NA with zero for further analysis.

[Villeroy]

=IF(ISNA(X1);0;X1) where X1 is your VLOOKUP
But now you can not distinguish between a missing value and a looked up zero.

[GUNTTURSRINIWAS]

Villeroy could you incorporate your formula in my worksheet and revert back,so that I can asses whether the result suffices my requirement. If not you can help me advice me further.
Regards

[FJCC]

I wrapped your VLOOKUP on the MCX sheet in another IF() so a zero is returned rather than an #NA. The new part of the formula is

Code: Select all

IF(ISNA(VLOOKUP(A2;$MCX.$A$2:$G$300;5;0));0;VLOOKUP(A2;$MCX.$A$2:$G$300;5;0))

[Villeroy]

Code: Select all

=N(VLOOKUP(A2;$MCX.$A$2:$G$300;5;0))

works in this case where no text is expected to be returned. N(value) returns zero for any text or #N/A error.