Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default Vlookup, return zero if not found

I have a formula where I use multiple lookups to return several values, which
I add together. e.g =vlookup(A) + vlookup(B) +vlookup (C)

If vlookup A = 1, B = 3, & C = 2, everything is good and the result = 7.

But in some cases the value one of them is looking up does not exist in the
array.
If this happens it causes the whole formula to return #N/A. I would like it
to give the one that has the #N/A to return the value of zero. So say
vlookup A = #N/A, my total formula would = 6

Any help appreciated. Thanks.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default Vlookup, return zero if not found

=if(iserror(vlookup(...)),0,vlookup(...))
+if(iserror(vlookup(...)),0,vlookup(...))
+if(iserror(vlookup(...)),0,vlookup(...))
....



molsansk wrote:

I have a formula where I use multiple lookups to return several values, which
I add together. e.g =vlookup(A) + vlookup(B) +vlookup (C)

If vlookup A = 1, B = 3, & C = 2, everything is good and the result = 7.

But in some cases the value one of them is looking up does not exist in the
array.
If this happens it causes the whole formula to return #N/A. I would like it
to give the one that has the #N/A to return the value of zero. So say
vlookup A = #N/A, my total formula would = 6

Any help appreciated. Thanks.


--

Dave Peterson
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 510
Default Vlookup, return zero if not found

Hi,

Test your result with an IF , such as :

=IF(ISNA(vlookup(A)),0,vlookup(A))


HTH
Cheers

Carim

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 to Return a Range of Data James Excel Discussion (Misc queries) 0 July 13th 06 09:44 PM
vlookup, add parameter, on error return user defined value jims2994 Excel Worksheet Functions 0 July 7th 06 02:56 PM
VLOOKUP CONDITION TO RETURN BLANK CELL WHERE LOOKUP VALUE IS NOT IN TABLE ARRAY Scott Lolmaugh Excel Worksheet Functions 3 March 10th 06 12:05 AM
Want VLookup to Return the row above JoOwl0 Excel Worksheet Functions 8 April 23rd 05 07:16 PM
Vlookup of an if statement return James Excel Worksheet Functions 2 April 6th 05 10:28 PM


All times are GMT +1. The time now is 10:51 AM.

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"