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

Question

I have 2 tables:

• Table1 - FG parts with QOH

• Table2 - BOM of Comp related to FG and CompQtyPer

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

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)

Answer 1

You can achieve this by creating a helper column to table 2 which basically ties table 1 to table 2 and calculates the number of FG you need for each comp:

I placed table 1 in A1:B6, Table 2 in F1:H6, and Table 3 in K1:L1

In I1:I6 create a helper column using the following formula:

=INDEX($B$1:$B$6,MATCH(F1,$A$1:$A$6,0))*H1

It grabs the QTY from table 1 and multiplies it by the QTY in table 2. It makes the next part in Table 3 very easy, and keeps your formulas relatively simple and easy to maintain.

In K1 place the comp you want to look up

In L1 use the following formula:

=SUMPRODUCT((G1:G6=K1)*I1:I6)