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

15266

New Member
Joined
Feb 10, 2016
Messages
10
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)


JuneJulyAugust
ProductCode110050200
ProductCode215080120
ProductCode320090350

<tbody>
</tbody>

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.


Ingredient1Ingredient2Ingredient3Ingredient4
ProductCode120%
5%0%75%
ProductCode280%20%0%0%
ProductCode310%20%30%40%

<tbody>
</tbody>


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!
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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.
 

15266

New Member
Joined
Feb 10, 2016
Messages
10
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%​

<tbody>
</tbody>


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​

<tbody>
</tbody>


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?
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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.
 

15266

New Member
Joined
Feb 10, 2016
Messages
10
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​

<tbody>
</tbody>


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
LineColourJuneJulyAugustJuneJulyAugust
2
ProductCode31Black
200​
90​
350​
Ingredient1
160​
83​
171​
3
ProductCode21Black
150​
80​
120​
Ingredient2
75​
36,5​
104​
4
ProductCode11Black
100​
50​
200​
Ingredient3
60​
27​
105​
5
ProductCode42Orange208020Ingredient4
155​
73,5​
290​
6ProductCode51Blue303030

<tbody>
</tbody>

****** 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​

<tbody>
</tbody>
</body>Would that be possible?
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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%

<tbody>
</tbody>


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​

<tbody>
</tbody>


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.
 
Last edited:

15266

New Member
Joined
Feb 10, 2016
Messages
10
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!
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
Worked perfectly for me.

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

M.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,102,728
Messages
5,488,532
Members
407,643
Latest member
samerf86

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top