Vlookup returning #n/a

T

TRYIN

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,'November
2007'!C$3:R$261,14,FALSE)+IF(ISNA(VLOOKUP(A18,'November
2007'!C$3:R$261,14,FALSE)),VLOOKUP(A18,'November
2007'!C$3:R$261,14,FALSE)+IF(ISNA(VLOOKUP(A20,'November
2007'!C$3:R$261,14,FALSE)),VLOOKUP(A20,'November
2007'!C$3:R$261,14,FALSE)+IF(ISNA(VLOOKUP(A21,'November
2007'!C$3:R$261,14,FALSE)),VLOOKUP(A21,'November 2007'!C$3:R$261,14,FALSE)))))

Thanks in advance for your help.
 
D

Dave Peterson

Since you're trying to add all those returned values, you want everything
returned to be a number.

Try changing all the ,"", to ,0,
 
C

CLR

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
 
Top