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
|