# Thread: Trying to understand index/match for specific scenario Thanks:  1 Post #5066420 (1) Likes:  1 Post #5066420 (1)

1. ## 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?

L

2. ## 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