Index and Match using Small on condition

[rex759]

Hello,

My goal is to pull the first and second appropriate date in column E if the ID number occurs a second time in column D.
The formula I am using is always pulling the second date if it exists. I thought I could use IFERROR or ISERROR on SMALL so if not found, it would use the traditional INDEX and MATCH. That didn’t work for me either.

Any ideas?

Thanks in advance

Book1
	A	B	C	D	E	F
1	Individual Wkbk			MASTER
2	Product ID	Date		Product ID	Date
3	145	2/5/2019		145	2/7/2019	2/7/2019
4	6942	2/6/2019		6942	2/6/2019
5	145	2/7/2019		145	2/7/2019
6	662	2/8/2019		662	2/11/2019
7	1957	2/9/2019		1957	2/9/2019
8	818	2/10/2019		818	2/10/2019
9	662	2/11/2019		662	2/11/2019
10	6349	2/12/2019		6349	2/12/2019
11	6169	2/13/2019		6169	2/13/2019
Sheet1

Cell Formulas
Range	Formula
F3	F3	{=IFERROR(INDEX($B$3:$B$11,SMALL(IF($A$3:$A$11=D3,ROW($B$3:$B$11)-ROW($B$3)+1,ROW($E$11)+1),2),1),INDEX($B$3:$B$11,MATCH(D3,$A$3:$A$11,0),0))}
B4:B11	B4	=B3+1
E3:E11	E3	{=IF(COUNTIF($D$3:$D$11,D3)>1,INDEX($B$3:$B$11,SMALL(IF($A$3:$A$11=D3,ROW($B$3:$B$11)-ROW($B$3)+1,ROW($E$11)+1),2),1),INDEX($B$3:$B$11,MATCH(D3,$A$3:$A$11,0),0))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[AhoyNC]

Is this what you are trying to do?

Book1
	A	B	C	D	E
1	Individual Wkbk			MASTER
2	Product ID	Date		Product ID	Date
3	145	2/5/2019		145	2/5/2019
4	6942	2/6/2019		6942	2/6/2019
5	145	2/7/2019		145	2/7/2019
6	662	2/8/2019		662	2/8/2019
7	1957	2/9/2019		1957	2/9/2019
8	818	2/10/2019		818	2/10/2019
9	662	2/11/2019		662	2/11/2019
10	6349	2/12/2019		6349	2/12/2019
11	6169	2/13/2019		6169	2/13/2019
Sheet1

Cell Formulas
Range	Formula
B4:B11	B4	=B3+1
E3:E11	E3	{=INDEX($B$3:$B$11,SMALL(IF($A$3:$A$11=$D3,ROW($A$3:$A$11)-ROW($A$3)+1),COUNTIF($A$3:A3,$D3)))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[rex759]

Yes! I wasnt even close!! Thank you very much.

 

[AhoyNC]

You're welcome. Actual you were pretty close.
If you have Excel 2016 or later you could use the following formula that doesn't require CTRL-SHIFT-ENTER. Just enter.

Book1
	A	B	C	D	E
1	Individual Wkbk			MASTER
2	Product ID	Date		Product ID	Date
3	145	2/5/2019		145	2/5/2019
4	6942	2/6/2019		6942	2/6/2019
5	145	2/7/2019		145	2/7/2019
6	662	2/8/2019		662	2/8/2019
7	1957	2/9/2019		1957	2/9/2019
8	818	2/10/2019		818	2/10/2019
9	662	2/11/2019		662	2/11/2019
10	6349	2/12/2019		6349	2/12/2019
11	6169	2/13/2019		6169	2/13/2019
Sheet1

Cell Formulas
Range	Formula
B4:B11	B4	=B3+1
E3:E11	E3	=INDEX($B$3:$B$11,AGGREGATE(15,6,(ROW($A$3:$A$11)-ROW($A$3)+1)/($A$3:$A$11=$D3),COUNTIF($A$3:A3,$D3)))

 

[rex759]

Hmmm, when I applied a sort to column D I get #num errors. do not line up, I get a #num error. Do the ID# have to be in the same row to work?

 

[AhoyNC]

My mistake. Change the COUNTIF in both formulas from:

COUNTIF($A$3:A3,$D3) to: COUNTIF($D$3:D3,$D3) Book1 A B C D E 1 Individual Wkbk MASTER 2 Product ID Date Product ID Date 3 145 2/5/2019 145 2/5/2019 4 6942 2/6/2019 145 2/7/2019 5 145 2/7/2019 662 2/8/2019 6 662 2/8/2019 662 2/11/2019 7 1957 2/9/2019 818 2/10/2019 8 818 2/10/2019 1957 2/9/2019 9 662 2/11/2019 6169 2/13/2019 10 6349 2/12/2019 6349 2/12/2019 11 6169 2/13/2019 6942 2/6/2019 Sheet3 Cell Formulas Range Formula E3:E11 E3 =INDEX($B$3:$B$11,AGGREGATE(15,6,(ROW($A$3:$A$11)-ROW($A$3)+1)/($A$3:$A$11=$D3),COUNTIF($D$3:D3,$D3))) Book1 A B C D E 1 Individual Wkbk MASTER 2 Product ID Date Product ID Date 3 145 2/5/2019 145 2/5/2019 4 6942 2/6/2019 145 2/7/2019 5 145 2/7/2019 662 2/8/2019 6 662 2/8/2019 662 2/11/2019 7 1957 2/9/2019 818 2/10/2019 8 818 2/10/2019 1957 2/9/2019 9 662 2/11/2019 6169 2/13/2019 10 6349 2/12/2019 6349 2/12/2019 11 6169 2/13/2019 6942 2/6/2019 Sheet4 Cell Formulas Range Formula E3:E11 E3 {=INDEX($B$3:$B$11,SMALL(IF($A$3:$A$11=$D3,ROW($A$3:$A$11)-ROW($A$3)+1),COUNTIF($D$3:D3,$D3)))} Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[rex759]

Thank you!