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

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

  1. #1
    New Member
    Join Date
    Feb 2016
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,239
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default 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. #3
    New Member
    Join Date
    Feb 2016
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Marcelo Branco View Post
    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. #4
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,239
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

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

    Quote Originally Posted by 15266 View Post
    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. #5
    New Member
    Join Date
    Feb 2016
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Marcelo Branco View Post
    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. #6
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,239
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default 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.
    Last edited by Marcelo Branco; Jun 20th, 2019 at 09:44 PM.

  7. #7
    New Member
    Join Date
    Feb 2016
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #8
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,239
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default 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.
    Last edited by Marcelo Branco; Jun 21st, 2019 at 08:18 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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