Vlookup returning #n/a

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TRYIN - 08 Feb 2008 18:11 GMT

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.

Reply to this Message

Dave Peterson - 08 Feb 2008 18:19 GMT

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

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

> The following is my formula, how do I get it return 0 instead of #N/A? I
> tried
[quoted text clipped - 16 lines]
>
> Thanks in advance for your help.

Signature

Dave Peterson

Reply to this Message

CLR - 08 Feb 2008 19:56 GMT

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

> The following is my formula, how do I get it return 0 instead of #N/A? I
> tried
[quoted text clipped - 16 lines]
>
> Thanks in advance for your help.

Reply to this Message