Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup returning #n/a
The following is my formula, how do I get it return 0 instead of #N/A? I
tried VLOOKUP(A17,'November 2007'!C$3:R$261,14,FALSE)+VLOOKUP(A18,'November 2007'!C$3:R$261,14,FALSE)+VLOOKUP(A20,'November 2007'!C$3:R$261,14,FALSE)+VLOOKUP(A21,'November 2007'!C$3:R$261,14,FALSE) I tried using the following which did't give me the 0 instead #N/A but it gave me the wrong amount. Maybe you can tell me where I went wrong. IF(ISNA(VLOOKUP(A17,'November 2007'!C$3:R$261,14,FALSE)),"",VLOOKUP(A17,'Novembe r 2007'!C$3:R$261,14,FALSE)+IF(ISNA(VLOOKUP(A18,'Nov ember 2007'!C$3:R$261,14,FALSE)),VLOOKUP(A18,'November 2007'!C$3:R$261,14,FALSE)+IF(ISNA(VLOOKUP(A20,'Nov ember 2007'!C$3:R$261,14,FALSE)),VLOOKUP(A20,'November 2007'!C$3:R$261,14,FALSE)+IF(ISNA(VLOOKUP(A21,'Nov ember 2007'!C$3:R$261,14,FALSE)),VLOOKUP(A21,'November 2007'!C$3:R$261,14,FALSE))))) Thanks in advance for your help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup returning #n/a
Since you're trying to add all those returned values, you want everything
returned to be a number. Try changing all the ,"", to ,0, TRYIN wrote: The following is my formula, how do I get it return 0 instead of #N/A? I tried VLOOKUP(A17,'November 2007'!C$3:R$261,14,FALSE)+VLOOKUP(A18,'November 2007'!C$3:R$261,14,FALSE)+VLOOKUP(A20,'November 2007'!C$3:R$261,14,FALSE)+VLOOKUP(A21,'November 2007'!C$3:R$261,14,FALSE) I tried using the following which did't give me the 0 instead #N/A but it gave me the wrong amount. Maybe you can tell me where I went wrong. IF(ISNA(VLOOKUP(A17,'November 2007'!C$3:R$261,14,FALSE)),"",VLOOKUP(A17,'Novembe r 2007'!C$3:R$261,14,FALSE)+IF(ISNA(VLOOKUP(A18,'Nov ember 2007'!C$3:R$261,14,FALSE)),VLOOKUP(A18,'November 2007'!C$3:R$261,14,FALSE)+IF(ISNA(VLOOKUP(A20,'Nov ember 2007'!C$3:R$261,14,FALSE)),VLOOKUP(A20,'November 2007'!C$3:R$261,14,FALSE)+IF(ISNA(VLOOKUP(A21,'Nov ember 2007'!C$3:R$261,14,FALSE)),VLOOKUP(A21,'November 2007'!C$3:R$261,14,FALSE))))) Thanks in advance for your help. -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup returning #n/a
If any one of the stacked VLOOKUPS is going #N/A on you then the whole thing
will....I would try wrapping each individual section in it's own IF statement instead of trying to do it all in one.....otherwise, you must insure that your looked up values are actually in the tables. Vaya con Dios, Chuck, CABGx3 "TRYIN" wrote: The following is my formula, how do I get it return 0 instead of #N/A? I tried VLOOKUP(A17,'November 2007'!C$3:R$261,14,FALSE)+VLOOKUP(A18,'November 2007'!C$3:R$261,14,FALSE)+VLOOKUP(A20,'November 2007'!C$3:R$261,14,FALSE)+VLOOKUP(A21,'November 2007'!C$3:R$261,14,FALSE) I tried using the following which did't give me the 0 instead #N/A but it gave me the wrong amount. Maybe you can tell me where I went wrong. IF(ISNA(VLOOKUP(A17,'November 2007'!C$3:R$261,14,FALSE)),"",VLOOKUP(A17,'Novembe r 2007'!C$3:R$261,14,FALSE)+IF(ISNA(VLOOKUP(A18,'Nov ember 2007'!C$3:R$261,14,FALSE)),VLOOKUP(A18,'November 2007'!C$3:R$261,14,FALSE)+IF(ISNA(VLOOKUP(A20,'Nov ember 2007'!C$3:R$261,14,FALSE)),VLOOKUP(A20,'November 2007'!C$3:R$261,14,FALSE)+IF(ISNA(VLOOKUP(A21,'Nov ember 2007'!C$3:R$261,14,FALSE)),VLOOKUP(A21,'November 2007'!C$3:R$261,14,FALSE))))) Thanks in advance for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup Returning #n/a | Excel Worksheet Functions | |||
vlookup returning a #N/A value | Excel Worksheet Functions | |||
vlookup returning sum | Excel Worksheet Functions | |||
Vlookup returning #N/A | Excel Worksheet Functions | |||
Vlookup and returning #n/a | Excel Worksheet Functions |