Hello Experts
In the attached table SKUs 1,5 and 7 all contain blue.
I want a function that will look up the quantity of each SKU that contains Blue in Table 2 (e.g. SKU 1 =55, SKU 5 =67 and SKU 7 =73) and then multiply and sum each of the quantities by the numbers in the column "Amount 1 per unit" and Amount 2 per unit" in Table 1.
In this example the function will return the 42,263 which equals Blue = SKU 1 (55 x 2x 23) + SKU 5 (67 x 6 x 63) + SKU 7 (8 x 8 x73)
<tbody>
</tbody>
In the attached table SKUs 1,5 and 7 all contain blue.
I want a function that will look up the quantity of each SKU that contains Blue in Table 2 (e.g. SKU 1 =55, SKU 5 =67 and SKU 7 =73) and then multiply and sum each of the quantities by the numbers in the column "Amount 1 per unit" and Amount 2 per unit" in Table 1.
In this example the function will return the 42,263 which equals Blue = SKU 1 (55 x 2x 23) + SKU 5 (67 x 6 x 63) + SKU 7 (8 x 8 x73)
TABLE 1 | ||||||||
SKU | Colour | Amount 1 per unit | Amount 2 per unit | |||||
SKU 1 | blue | 2 | 23 | |||||
SKU 2 | red | 3 | 21 | |||||
SKU 3 | green | 4 | 45 | |||||
SKU 4 | yellow | 5 | 41 | |||||
SKU 5 | blue | 6 | 63 | |||||
SKU 6 | red | 7 | 24 | |||||
SKU 7 | blue | 8 | 8 | |||||
TABLE 2 | ||||||||
SKU | QTY | Colour | TOTAL QTY | |||||
SKU 1 | 55 | blue | 42,263 | |||||
SKU 2 | 58 | |||||||
SKU 3 | 61 | Blue = SKU 1 (55 x 2x 23) + SKU 5 (67 x 6 x 63) + SKU 7 (8 x 8 x73) | ||||||
SKU 4 | 64 | |||||||
SKU 5 | 67 | |||||||
SKU 6 | 70 | |||||||
SKU 7 | 73 |
<tbody>
</tbody>