SUMPRODUCT to index and array of string values

[D4rwin]

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

 

[pgc01]

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]

yes, absolutely correct. My mistake.

 

[Marcelo Branco]

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]

Right on the the spot!
Thanks for your help Marcelo