| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 -#N/A
I have a VLookup which is returning #N/A, how can I have the formula return a
zero "0" in its place? I need to sum and the sheet is very large. Thanks for any help. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2003 -#N/A
Maybe this
=IF(ISNA(VLOOKUP(C1,A1:B6,2,FALSE)),"",VLOOKUP(C1, A1:B6,2,FALSE)) Mike "C" wrote: I have a VLookup which is returning #N/A, how can I have the formula return a zero "0" in its place? I need to sum and the sheet is very large. Thanks for any help. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2003 -#N/A
Mike
Thanks, but the formula is showing invalid, it has a problem with ,"", and if I take that out I am still getting #N/A. C "Mike H" wrote: Maybe this =IF(ISNA(VLOOKUP(C1,A1:B6,2,FALSE)),"",VLOOKUP(C1, A1:B6,2,FALSE)) Mike "C" wrote: I have a VLookup which is returning #N/A, how can I have the formula return a zero "0" in its place? I need to sum and the sheet is very large. Thanks for any help. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2003 -#N/A
Mike, Thank you! I had a problem with the parentheses, and it now works.
"Mike H" wrote: Maybe this =IF(ISNA(VLOOKUP(C1,A1:B6,2,FALSE)),"",VLOOKUP(C1, A1:B6,2,FALSE)) Mike "C" wrote: I have a VLookup which is returning #N/A, how can I have the formula return a zero "0" in its place? I need to sum and the sheet is very large. Thanks for any help. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2003 -#N/A
Try this approach:
=IF(ISNA(your_formula),0,your_formula) Hope this helps. Pete On Jan 9, 2:54*pm, C wrote: I have a VLookup which is returning #N/A, how can I have the formula return a zero "0" in its place? *I need to sum and the sheet is very large. Thanks for any help. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2003 -#N/A
=IF(ISNA(vlookup_formula),0,vlookup_formula)
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "C" wrote in message ... I have a VLookup which is returning #N/A, how can I have the formula return a zero "0" in its place? I need to sum and the sheet is very large. Thanks for any help. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2003 -#N/A
Others have shown the correct workaround formula:
=IF(ISNA(your_formula),0,your_formula). This has the drawback that your_formula is typically evaluated twice -- first to test for NA and again for the result in the False clause of the IF function. This is unavoidable and may cause slower calculations. Just FYI and for anyone else reading this, Excel 2007 introduced a new function named IFERROR. This eliminates the need for the second your_formula: =IFERROR(your_formula,0) Here, your_formula is evaluated only once, not twice. If your_formula doesn't return an error, its result is returned. If your_formula does result in an error, 0 is returned. Alas, this is not available in 2003 and earlier. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting LLC www.cpearson.com (email on the web site) "C" wrote in message ... I have a VLookup which is returning #N/A, how can I have the formula return a zero "0" in its place? I need to sum and the sheet is very large. Thanks for any help. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| unable to paste Excel 2003 chart into Outlook 2003 | Charts and Charting in Excel | |||
| opening excel files from access 2003, win xp, office 2003 | Excel Discussion (Misc queries) | |||
| import Excel 2003 file into Outlook 2003 - NO NAMED RANGES?? | Excel Discussion (Misc queries) | |||
| Copying Excel 2003 Selection into Outlook 2003 HTML E-Mail Message | Excel Discussion (Misc queries) | |||
| Excel 2003 Database Driver Visual FoxPro 7 on Server 2003. | Excel Discussion (Misc queries) | |||
Hybrid Mode
