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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Product[/TD]
[TD]First Shortage[/TD]
[TD]First Shortage Date[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]123[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]456[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]789[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Product[/TD]
[TD]Order Date[/TD]
[TD]Shortage[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]123[/TD]
[TD]02/05/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]123[/TD]
[TD]15/06/2018[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]123[/TD]
[TD]17/07/2018[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]456[/TD]
[TD]01/01/2019[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]456[/TD]
[TD]02/01/2019[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]456[/TD]
[TD]02/02/2019[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]456[/TD]
[TD]03/03/2019[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]789[/TD]
[TD]01/12/2017[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]789[/TD]
[TD]01/01/2018[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


















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.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Product[/TD]
[TD]First Shortage[/TD]
[TD]First Shortage Date[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]123[/TD]
[TD]5[/TD]
[TD]17/07/2018[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]456[/TD]
[TD]1[/TD]
[TD]01/01/2019[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]789[/TD]
[TD]5[/TD]
[TD]01/01/2018[/TD]
[/TR]
</tbody>[/TABLE]









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