Excel array index/match vlookup to another table and multiply results

[VA3GJG]

I have 2 tables:

• Table1 - FG parts with QOH
  

  	A	B
  1	FG	FG_QOH
  2	a	1
  3	b	2
  4	c	3
  5	d	4
  6	e	5
  7	f	6

  
  <tbody>
  </tbody>
• Table2 - BOM of Comp related to FG and CompQtyPer

	D	E	F
1	FG	Comp	CompQtyPer
2	f	x	2
3	e	y	2
4	d	z	2
5	b	x	3
6	c	y	3
7	a	z	3

<tbody>
</tbody>

Comp is known and want to sum table1 FG_QOH where the FG matches the Comp in Table2 multiplied against CompQtyPer

	H	I
1	Comp	TotQty
2	y	19

<tbody>
</tbody>

Table2 cell E3 is related to FG 'e' and has CompQtyPer=2.
Table1 FG 'e' has FG_QOH=5.
So 2*5 = 10 - Table2 cell E6 is related to FG 'c' and has CompQtyPer=3. Table1 FG 'c' has FG_QOH=3. So 3*3 = 9 TotQty = 19 (10+9)

 

[Eric W]

Try:

	A	B	C	D	E	F	G	H	I	J
1	FG	FG_QOH		FG	Comp	CompQtyPer		Comp	TotQty
2	a	1		f	x	2		y	19
3	b	2		e	y	2
4	c	3		d	z	2
5	d	4		b	x	3
6	e	5		c	y	3
7	f	6		a	z	3

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula I2 {=SUM(<fo F2:F7*SUMIF(A2:A7,IF(E2:E7=H2,D2:D7,"xxx"),B2:B7))}</fo <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The "xxx" being some value that will never occur in column A.