# Thread: Product of Index & matches - need help to make more efficient Thanks: 0 Likes: 0

1. ## Product of Index & matches - need help to make more efficient

Hi Everyone,

I'm looking for some help to simplify the following:

1) I have 1 sheet with a large table A1:D4 showing sales volume (Mt) per productcode (column A) and per months of June, July and August (row 1)

 June July August ProductCode1 100 50 200 ProductCode2 150 80 120 ProductCode3 200 90 350

2) I have a 2nd sheet with a large table A1:E4 showing the % of each ingredients (row 1) that the productcodes (column A) are composed.

 Ingredient1 Ingredient2 Ingredient3 Ingredient4 ProductCode1 20% 5% 0% 75% ProductCode2 80% 20% 0% 0% ProductCode3 10% 20% 30% 40%

Goal: I'm looking to summarize in 1 (total) cell for each month, the volume (Mt) of the ingredients needed.

So far I managed only to work out ingredient per ingredient, month per month by pulling down -and right- something like the following formula.
Unfortunately this takes up more space than the original tables themselves.

INDEX(Sheet2!B2:E4;MATCH("ProductCode1";Sheet2!A2:A4;0);MATCH("Ingredient1";Sheet2!B1:E1;0))*INDEX(Sheet1!B2:D4;MATCH("June";Sheet1!B1:D1;0))

Would anyone have a better solution, that would allow me to summarize the ingredient volume (Mt) in 1 cell per month?
Thanks in advance for anyone willing to help out!

2. ## Re: Product of Index & matches - need help to make more efficient

Maybe something like this

Sheet2

 A B C D E 1 Ingredient1 Ingredient2 Ingredient3 Ingredient4 2 ProductCode1 20% 5% 0% 75% 3 ProductCode2 80% 20% 0% 0% 4 ProductCode3 10% 20% 30% 40%

Sheet1
I reversed the order of the products in column A to make the test more real.

 A B C D E F G H I J 1 June July August Ingredient1 Ingredient2 Ingredient3 Ingredient4 2 ProductCode3 200 90 350 June 160 75 60 155 3 ProductCode2 150 80 120 July 83 36,5 27 73,5 4 ProductCode1 100 50 200 August 171 104 105 290

Array formula in G2 copied across and down
=SUMPRODUCT(INDEX(\$B\$2:\$D\$4,0,MATCH(\$F2,\$B\$1:\$D\$1,0)),INDEX(INDEX(Sheet2!\$B\$2:\$E\$4,0,MATCH(G\$1,Sheet2!\$B\$1:\$E\$1,0)),N(IF (1,MATCH(\$A\$2:\$A\$4,Sheet2!\$A\$2:\$A\$4,0)))))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

3. ## Re: Product of Index & matches - need help to make more efficient

Originally Posted by Marcelo Branco
Maybe something like this

Sheet2

 A B C D E 1 Ingredient1 Ingredient2 Ingredient3 Ingredient4 2 ProductCode1 20% 5% 0% 75% 3 ProductCode2 80% 20% 0% 0% 4 ProductCode3 10% 20% 30% 40%

Sheet1
I reversed the order of the products in column A to make the test more real.

 A B C D E F G H I J 1 June July August Ingredient1 Ingredient2 Ingredient3 Ingredient4 2 ProductCode3 200 90 350 June 160 75 60 155 3 ProductCode2 150 80 120 July 83 36,5 27 73,5 4 ProductCode1 100 50 200 August 171 104 105 290

Array formula in G2 copied across and down
=SUMPRODUCT(INDEX(\$B\$2:\$D\$4,0,MATCH(\$F2,\$B\$1:\$D\$1,0)),INDEX(INDEX(Sheet2!\$B\$2:\$E\$4,0,MATCH(G\$1,Sheet2!\$B\$1:\$E\$1,0)),N(IF (1,MATCH(\$A\$2:\$A\$4,Sheet2!\$A\$2:\$A\$4,0)))))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.
Fantastic! Could attach and if(and( statement before it with 2 criteria? For example these products might be produced on different lines, so for the whole range in sheet 1 I would want to if(and(E:E="line1"; D:D="colour black"); then followed by the array?

4. ## Re: Product of Index & matches - need help to make more efficient

Originally Posted by 15266
Fantastic! Could attach and if(and( statement before it with 2 criteria? For example these products might be produced on different lines, so for the whole range in sheet 1 I would want to if(and(E:E="line1"; D:D="colour black"); then followed by the array?
This should complicate the formula. Could you provide an example along with the expected results?

By the way, we can simplify the original formula with a different configuration and as long as the product codes in Sheet2 column A are in ascending order.
Something like,

Sheet1

 A B C D E F G H I 1 June July August June July August 2 ProductCode3 200 90 350 Ingredient1 160 83 171 3 ProductCode2 150 80 120 Ingredient2 75 36,5 104 4 ProductCode1 100 50 200 Ingredient3 60 27 105 5 Ingredient4 155 73,5 290

Regular formula in G2 copied across and down
=SUMPRODUCT(B\$2:B\$4,LOOKUP(\$A\$2:\$A\$4,Sheet2!\$A\$2:\$A\$4,INDEX(Sheet2!\$B\$2:\$E\$4,0,MATCH(\$F2,Sheet2!\$B\$1:\$E\$1,0))))
confirmed with just Enter

M.

5. ## Re: Product of Index & matches - need help to make more efficient

Originally Posted by Marcelo Branco
This should complicate the formula. Could you provide an example along with the expected results?

By the way, we can simplify the original formula with a different configuration and as long as the product codes in Sheet2 column A are in ascending order.
Something like,

Sheet1

 A B C D E F G H I 1 June July August June July August 2 ProductCode3 200 90 350 Ingredient1 160 83 171 3 ProductCode2 150 80 120 Ingredient2 75 36,5 104 4 ProductCode1 100 50 200 Ingredient3 60 27 105 5 Ingredient4 155 73,5 290

Regular formula in G2 copied across and down
=SUMPRODUCT(B\$2:B\$4,LOOKUP(\$A\$2:\$A\$4,Sheet2!\$A\$2:\$A\$4,INDEX(Sheet2!\$B\$2:\$E\$4,0,MATCH(\$F2,Sheet2!\$B\$1:\$E\$1,0))))
confirmed with just Enter

M.
Hi Marcelo,

Thx for your quick response.
Would look something like the below. To keep it simple the results would be the same as before, but I would be able to exclude ProductCodes from the array when they 1) are not produced on line 1 and 2) don't have the colour black.
In essence, I only look for the ingredient volume outcome of all products where those criteria are met:

 A B C D E F G H I 1 Line Colour June July August June July August 2 ProductCode3 1 Black 200 90 350 Ingredient1 160 83 171 3 ProductCode2 1 Black 150 80 120 Ingredient2 75 36,5 104 4 ProductCode1 1 Black 100 50 200 Ingredient3 60 27 105 5 ProductCode4 2 Orange 20 80 20 Ingredient4 155 73,5 290 6 ProductCode5 1 Blue 30 30 30

****** id="cke_pastebin" style="position: absolute; top: -3.05176e-05px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
 A B C D E F G H I 1 June July August June July August 2 ProductCode3 200 90 350 Ingredient1 160 83 171 3 ProductCode2 150 80 120 Ingredient2 75 36,5 104 4 ProductCode1 100 50 200 Ingredient3 60 27 105 5 Ingredient4 155 73,5 290
Would that be possible?

6. ## Re: Product of Index & matches - need help to make more efficient

New version - doesn't require Product Codes in ascending order (Column A of Sheet2)

Sheet2 ( i changed the rows of ProductCode2 and ProductCode1 for testing purposes)

 A B C D E 1 Ingredient1 Ingredient2 Ingredient3 Ingredient4 2 ProductCode2 80% 20% 0% 0% 3 ProductCode1 20% 5% 0% 75% 4 ProductCode3 10% 20% 30% 40% 5 ProductCode4 60% 10% 20% 10% 6 ProductCode5 20% 30% 10% 40%

Sheet1 (Criteria: line 1 or 2; Colour = Black)

 A B C D E F G H I J K 1 Line Colour June July August June July August 2 ProductCode3 1 Black 200 90 350 Ingredient1 160 83 171 3 ProductCode2 1 Black 150 80 120 Ingredient2 75 36,5 104 4 ProductCode1 1 Black 100 50 200 Ingredient3 60 27 105 5 ProductCode4 2 Orange 20 80 20 Ingredient4 155 73,5 290 6 ProductCode5 1 Blue 30 30 30

Formula in I2 copied across and down
=SUMPRODUCT(D\$2:D\$6,SUMIF(Sheet2!\$A\$2:\$A\$6,\$A\$2:\$A\$6,INDEX(Sheet2!\$B\$2:\$E\$6,0,MATCH(\$H2,Sheet2!\$B\$1:\$E\$1,0))),--((\$B\$2:\$B\$6=1)+(\$B\$2:\$B\$6=2)),--(\$C\$2:\$C\$6="Black"))

M.

7. ## Re: Product of Index & matches - need help to make more efficient

Thanks Marcelo,

It's not yet working, wondering if I'm doing something wrong.
Could something potentially be missed around the index match piece of the formula?
Notice it's not picking up those 2 ranges in recognizable colours in the formula (but just guessing)

Thanks a lot for your help so far!

8. ## Re: Product of Index & matches - need help to make more efficient

Worked perfectly for me.

What you mean by not working? An error, wrong result,...?
Could you show us again the table in Sheet2?

M.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•