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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top