# SUMPRODUCT to index and array of string values

#### D4rwin

##### Board Regular
Here's a tricky Sumproduct question which I'm getting stuck on. I want to sum the Quantity in Table 2 with a lookup on the group criteria in Table1.
The most logical solution would be to add another column next to Quantity and populate it with a lookup on group. However, this question is purely out of technical curiosity and so I'm wondering if anyone has any ideas how to get this methodology to work.

=SUMPRODUCT(Quantity*(Product=(INDEX(LookProduct, MATCH("X", LookGroup, 0)))))

At the moment Index is of course only returning the first matched value - "A" - but I would like to find a way to index the array of LookProduct associated with each LookGroup (x and y).

To demonstrate more clearly what it is I'm looking for (referencing Group "X"):
=SUMPRODUCT(Quantity*(Product={"A", "B"}))

Table1

 LookProduct LookGroup A X B Y C Y D X

<tbody>
</tbody>

Table2

 Product Quantity a 12 a 23 a 43 b 432 c 14 b 32 d 61 d 19 a 27

<tbody>
</tbody>

Any clues??
Thanks

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### pgc01

##### MrExcel MVP
To demonstrate more clearly what it is I'm looking for (referencing Group "X"):
=SUMPRODUCT(Quantity*(Product={"A", "B"}))

Table1

 LookProduct LookGroup A X B Y C Y D X

<tbody>
</tbody>

Hi

Shouldn't group X be {"A","D"}?

#### D4rwin

##### Board Regular
yes, absolutely correct. My mistake.

#### Marcelo Branco

##### MrExcel MVP
Maybe....

 A​ B​ C​ D​ E​ F​ G​ H​ 1​ Group​ Quantity​ LookProduct​ LookGroup​ Product​ Quantity​ 2​ X​ 185​ A​ X​ a​ 12​ 3​ Y​ 478​ B​ Y​ a​ 23​ 4​ C​ Y​ a​ 43​ 5​ D​ X​ b​ 432​ 6​ c​ 14​ 7​ b​ 32​ 8​ d​ 61​ 9​ d​ 19​ 10​ a​ 27​

Formula in B2 copied down
=SUMPRODUCT(SUMIF(\$G\$2:\$G\$10,\$D\$2:\$D\$5,\$H\$2:\$H\$10),--(\$E\$2:\$E\$5=A2))

Hope this helps

M.

#### D4rwin

##### Board Regular
Right on the the spot!

Replies
13
Views
229
Replies
11
Views
562
Replies
7
Views
89
Replies
6
Views
109
Replies
10
Views
113