How do I replace a return value of #N/A in a vlookup with zero?
Rather than #N/A being returned from a lookup for a value that is not in the
lookup table can I replace the returned #N/A with a zero? Without being able to do this I am unable sum the returned values. |
How do I replace a return value of #N/A in a vlookup with zero?
IF(ISNA(YourLookupFormula),0,YourLookupFormula)
Vaya con Dios, Chuck, CABGx3 "marketingman" wrote: Rather than #N/A being returned from a lookup for a value that is not in the lookup table can I replace the returned #N/A with a zero? Without being able to do this I am unable sum the returned values. |
How do I replace a return value of #N/A in a vlookup with zero?
Try this:
If(isna(vlookup(your lookup)),0,vlookup(your lookup)) |
How do I replace a return value of #N/A in a vlookup with zero?
Hi,
To sum the values ignoring the error values, you may want to use the following formula. You need not alter your vlookup formula In range A1:A4, you have the following 1 2 3 #DIV/0! In cell A6, array enter (Ctrl+Shift+Enter) the following formula SUM(IF(NOT(ISERROR(A1:A4)),A1:A4)) Regards, Ashish Mathur "marketingman" wrote: Rather than #N/A being returned from a lookup for a value that is not in the lookup table can I replace the returned #N/A with a zero? Without being able to do this I am unable sum the returned values. |
How do I replace a return value of #N/A in a vlookup with zero?
1.
=SUM(Range,"<#N/A") 2. If you're sending the workbook across countries... =SUMIF(Range,"<"&NA()) marketingman wrote: Rather than #N/A being returned from a lookup for a value that is not in the lookup table can I replace the returned #N/A with a zero? Without being able to do this I am unable sum the returned values. |
All times are GMT +1. The time now is 09:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com