Results 1 to 2 of 2

Thread: Trying to understand index/match for specific scenario

  1. #1
    New Member
    Join Date
    Nov 2017
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Trying to understand index/match for specific scenario

    Hi,

    I seem to be able to use index/match for a specific scenario but struggling to apply it to others so looking for some help - maybe that's not even the best way of doing it.

    I have two data sources a list of products and another data source with the products, order despatch dates and shortages.

    A B C
    1 Product First Shortage First Shortage Date
    2 123
    3 456
    4 789

    The source data I'm looking to draw from is similar to the below:

    A B C
    1 Product Order Date Shortage
    2 123 02/05/2018 0
    3 123 15/06/2018 0
    4 123 17/07/2018 5
    5 456 01/01/2019 1
    6 456 02/01/2019 2
    7 456 02/02/2019 10
    8 456 03/03/2019 15
    9 789 01/12/2017 0
    10 789 01/01/2018 5


















    I'm looking for a formula that will return the first time we're going to be short on a product and the date that the first shortage will occur. Here are the results i'd expect below.

    A B C
    1 Product First Shortage First Shortage Date
    2 123 5 17/07/2018
    3 456 1 01/01/2019
    4 789 5 01/01/2018









    How am i best to do it? Any ideas?

    Thanks in advance.

    L

  2. #2
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    2,493
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Trying to understand index/match for specific scenario

    Try

    Excel 2010
    ABC
    1ProductFirst ShortageFirst Shortage Date
    212357/17/2018
    345611/1/2019
    478951/1/2018

    Sheet5



    Array Formulas
    CellFormula
    B2{=INDEX(Sheet6!$C$2:$C$10,SMALL(IF(A2=Sheet6!$A$2:$A$10,IF(Sheet6!$C$2:$C$10>0,ROW(Sheet6!$C$2:$C$10)-ROW(Sheet6!$C$2)+1)),1))}
    C2{=INDEX(Sheet6!$B$2:$B$10,SMALL(IF(A2=Sheet6!$A$2:$A$10,IF(Sheet6!$C$2:$C$10>0,ROW(Sheet6!$C$2:$C$10)-ROW(Sheet6!$C$2)+1)),1))}
    B3{=INDEX(Sheet6!$C$2:$C$10,SMALL(IF(A3=Sheet6!$A$2:$A$10,IF(Sheet6!$C$2:$C$10>0,ROW(Sheet6!$C$2:$C$10)-ROW(Sheet6!$C$2)+1)),1))}
    C3{=INDEX(Sheet6!$B$2:$B$10,SMALL(IF(A3=Sheet6!$A$2:$A$10,IF(Sheet6!$C$2:$C$10>0,ROW(Sheet6!$C$2:$C$10)-ROW(Sheet6!$C$2)+1)),1))}
    B4{=INDEX(Sheet6!$C$2:$C$10,SMALL(IF(A4=Sheet6!$A$2:$A$10,IF(Sheet6!$C$2:$C$10>0,ROW(Sheet6!$C$2:$C$10)-ROW(Sheet6!$C$2)+1)),1))}
    C4{=INDEX(Sheet6!$B$2:$B$10,SMALL(IF(A4=Sheet6!$A$2:$A$10,IF(Sheet6!$C$2:$C$10>0,ROW(Sheet6!$C$2:$C$10)-ROW(Sheet6!$C$2)+1)),1))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself



    Excel 2010
    ABC
    1ProductOrder DateShortage
    21235/2/20180
    31236/15/20180
    41237/17/20185
    54561/1/20191
    64561/2/20192
    74562/2/201910
    84563/3/201915
    978912/1/20170
    107891/1/20185

    Sheet6



    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

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
  •