Lookup which returns multiple values which are additive
I need to lookup some data. I want to find a way to have a look-up which
uses a lookup variable which can be range (i.e. more than 1 variable) and return the results in an additive manner (if more than one variable is used). Here is a range of data that I want to pull elsewhere in the spreadsheet: A B C D 1 2007 2008 2009 2 Profile 1 x1 y1 z1 3 Profile 2 x2 y2 z2 4 Profile 3 x3 y3 z3 Now lets say down below I have chosen which Profile I want to use (with drop down boxes) for three separate groups which I will consolidate later: 5 Group1 Group2 Group3 6 Profile 1 Profile 2 Profile 3 (drop down boxes) Now I want to be able to lookup the values across all three groups and add them together (i.e. consolidate the groups) in the appropriate date columns below: 7 2007 2008 2009 8 All Groups (x1+x2+x3) (y1+y2+y3) (z1+z2+z3) (this is the formula I need) So effectively in B8 I want use the range B6:D6 as my lookup variable. And read from the array in B2:B4. And I want it to return three variables which are added together. Note I used a simple example here. But I could have chosen Profile 1 for all three groups or any other combination. I am assuming its either using an array formula or using MMULT or SUMPRODUCT. I do not want to use a pivot table. Any thoughts. Thanks EM |
Lookup which returns multiple values which are additive
Create name range for each Profile
Select A2:D4 then Insert Name Create Create Names in select Left column A6 to D6 are your drop down Formula: in A8 =SUMPRODUCT(--($A$2:$A$4=A6)*$B$2:$D$4) and drag across to D8 A9 =SUM(A8:C8) "ExcelMonkey" wrote: I need to lookup some data. I want to find a way to have a look-up which uses a lookup variable which can be range (i.e. more than 1 variable) and return the results in an additive manner (if more than one variable is used). Here is a range of data that I want to pull elsewhere in the spreadsheet: A B C D 1 2007 2008 2009 2 Profile 1 x1 y1 z1 3 Profile 2 x2 y2 z2 4 Profile 3 x3 y3 z3 Now lets say down below I have chosen which Profile I want to use (with drop down boxes) for three separate groups which I will consolidate later: 5 Group1 Group2 Group3 6 Profile 1 Profile 2 Profile 3 (drop down boxes) Now I want to be able to lookup the values across all three groups and add them together (i.e. consolidate the groups) in the appropriate date columns below: 7 2007 2008 2009 8 All Groups (x1+x2+x3) (y1+y2+y3) (z1+z2+z3) (this is the formula I need) So effectively in B8 I want use the range B6:D6 as my lookup variable. And read from the array in B2:B4. And I want it to return three variables which are added together. Note I used a simple example here. But I could have chosen Profile 1 for all three groups or any other combination. I am assuming its either using an array formula or using MMULT or SUMPRODUCT. I do not want to use a pivot table. Any thoughts. Thanks EM |
All times are GMT +1. The time now is 01:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com