| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2002: Vlookup formula not working | Excel Discussion (Misc queries) | |||
| Excel 2002 : Why VLOOKUP formula does not show blanks or N/A ? | Excel Discussion (Misc queries) | |||
| Vlookup formula - Excel Version 2002 | Excel Worksheet Functions | |||
| Vlookup formula Excel version 2002 | Excel Discussion (Misc queries) | |||
| VLookup Formula Excel Version 2002 | Excel Worksheet Functions | |||
Hybrid Mode
