Excel 2002: Can I convert #N/A to 0 in the VLOOKUP( ) formula ?

[Mr. Low]

Dear Sir,

When VLOOKUP formula fails to find the perfect match it always return with
#N/A.

Usually #N/A disable me to do any computation that involve that cell. The
result of any additional function always return with #N/A as well.

To overcome this problem. I always convert the formulas to values, sort it
out and clear those #N/A cells and enter zero to them.

Just take an example of the formula = VLOOKUP(B2, K24: Q120, 6,FALSE), may
In know if I could manipulate the formula to enable it to show 0.00 instead
of #N/A when match could not be found ?


Thanks

Low

A36B58K641

 

[Pranav Vaidya]

change your formula as..

=IF(ISERROR(VLOOKUP(B2, K24: Q120, 6,FALSE)),0,VLOOKUP(B2, K24: Q120,
6,FALSE))

HTH,

 

[Gary''s Student]

Test for the error in the formula:

=IF(ISERROR(VLOOKUP(19,B1:B8,1,FALSE)),0,VLOOKUP(9,B1:B8,1,FALSE))

 

[Gord Dibben]

Which will mask all errors, not just the #NA

Better to use the ISNA function to trap.

=IF(ISNA(vlookup)),0,vlookup))


Gord Dibben MS Excel MVP

 

[Mr. Low]

Hello Pranav,

Your formula works well.

Many thanks.

Low

 

[Mr. Low]

Hello Gord,

Your formula works well.

Many thanks.

Low

 

[Mr. Low]

Hello Garry's Student,

Your formula works well.

Many thanks.

Low