Trying to understand index/match for specific scenario

[HOGGL]

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

<tbody>
</tbody>

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

<tbody>
</tbody>

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

<tbody>
</tbody>

How am i best to do it? Any ideas?

Thanks in advance.

L

 

[Scott T]

Try

Excel 2010
	A	B	C
1	Product	First Shortage	First Shortage Date
2	123	5	7/17/2018
3	456	1	1/1/2019
4	789	5	1/1/2018
Sheet5

Cell Formulas
Range	Formula
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))}
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))}
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))}
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))}
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))}
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))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Excel 2010
	A	B	C
1	Product	Order Date	Shortage
2	123	5/2/2018	0
3	123	6/15/2018	0
4	123	7/17/2018	5
5	456	1/1/2019	1
6	456	1/2/2019	2
7	456	2/2/2019	10
8	456	3/3/2019	15
9	789	12/1/2017	0
10	789	1/1/2018	5
Sheet6