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

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

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

change your formula as..

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

HTH,
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"Mr. Low" wrote:

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

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

Test for the error in the formula:

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

--
Gary''s Student - gsnu200747


"Mr. Low" wrote:

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

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

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

On Wed, 26 Sep 2007 07:52:03 -0700, Pranav Vaidya
wrote:

change your formula as..

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

HTH,

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

Hello Pranav,

Your formula works well.

Many thanks.

Low


--
A36B58K641


"Pranav Vaidya" wrote:

change your formula as..

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

HTH,
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"Mr. Low" wrote:

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

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

Hello Gord,

Your formula works well.

Many thanks.

Low

--
A36B58K641


"Gord Dibben" wrote:

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

On Wed, 26 Sep 2007 07:52:03 -0700, Pranav Vaidya
wrote:

change your formula as..

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

HTH,

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

Hello Garry's Student,

Your formula works well.

Many thanks.

Low

--
A36B58K641


"Gary''s Student" wrote:

Test for the error in the formula:

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

--
Gary''s Student - gsnu200747


"Mr. Low" wrote:

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