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

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