Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
CLR CLR is offline
external usenet poster
 
Posts: 1,998
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Vlookup Returning #n/a Toolbar Confused Excel Worksheet Functions 4 July 5th 07 01:32 AM
vlookup returning a #N/A value Ian Excel Worksheet Functions 5 May 17th 06 02:58 PM
vlookup returning sum Rose Excel Worksheet Functions 1 July 14th 05 10:47 PM
Vlookup returning #N/A ww Excel Worksheet Functions 2 March 23rd 05 01:24 AM
Vlookup and returning #n/a Cathrine Excel Worksheet Functions 3 December 23rd 04 03:23 PM


All times are GMT +1. The time now is 07:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"