Formula to obtain 2nd and 3rd future date from another worksheet

[woodie]

I have two worksheets in my workbook. Sheet 1 is called Front and sheet 2 is called Details. Cells A5-A57 contain events and Cells B5-57 contains the next date of the event in Column A. Column B gets the data from the Details Sheet. The formula I used to get the date of the next (future) event is {=MIN(IF(Details!B2:B283>=TODAY(),Details!B2:B283))}

I now would like to not only have the next event listed on the Front sheet, but the next 3 meetings listed. In Column C I would like the 2nd future meeting and in Column D the 3 future meeting.

As the year progresses, the data on the Front sheet will change to reflect the next 3 upcoming events.

Any assistance anyone could provide will greatly be appreciated.

Woodie

 

[sheetspread]

Use the SMALL function instead of MIN

 

[woodie]

I have never written a formula with the SMALL function so I am not sure how to write the formula so that results only include dates in the future and not past dates.

 

[sheetspread]

Something like:

Excel 2010
	A	B	C	D	E	F	G
1	Event	Date
2	RJM	1/19/2018		1/19/2018	1/19/2018	1/27/2018	2/6/2018
3	QNT	1/27/2018
4	KRC	2/6/2018
5	VYV	2/13/2018
6	LTG	2/24/2018
7	DTW	2/25/2018
8	QXR	3/6/2018
9	IWL	3/11/2018
10	LAE	3/20/2018
11	GMP	4/4/2018
12	EQH	4/13/2018
13	TBF	4/14/2018
14	JOC	4/20/2018
15	VXX	4/25/2018
16	SMO	5/5/2018
17	CXX	5/10/2018
18	NMM	5/21/2018
19	SON	5/29/2018
20	SSP	6/13/2018
21	DJW	6/26/2018
22	JZX	7/8/2018
23	JCP	7/19/2018
24	LYQ	7/21/2018
25	LWL	7/28/2018
26	JAY	8/3/2018
Sheet1

Cell Formulas
Range	Formula
E2	{=SMALL(IF($B$2:$B$26>=$D2,$B$2:$B$26),COLUMN(A1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[woodie]

The front sheet looks like this
[TABLE="width: 495"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Event [/TD]
[TD]Next Event Date[/TD]
[TD]2nd future Event Date [/TD]
[TD]3rd Future Event Date[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]RJB[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]JUT[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]ABC[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]

While the Detail sheet looks like this
[TABLE="width: 186"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]RJB[/TD]
[TD="align: right"]1/1/2018[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]RJB[/TD]
[TD="align: right"]2/15/2018[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]RJB[/TD]
[TD="align: right"]2/27/2017[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]RJB[/TD]
[TD="align: right"]3/3/2018[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]RJB[/TD]
[TD="align: right"]3/17/2018[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]RJB[/TD]
[TD="align: right"]3/29/2018[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]RJB[/TD]
[TD="align: right"]6/1/2018[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]JUT[/TD]
[TD="align: right"]5/1/2018[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]JUT[/TD]
[TD="align: right"]5/17/2018[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]JUT[/TD]
[TD="align: right"]6/3/2018[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]JUT[/TD]
[TD="align: right"]7/3/2017[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]ABC[/TD]
[TD="align: right"]1/16/2018[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]ABC[/TD]
[TD="align: right"]4/1/2018[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]ABC[/TD]
[TD="align: right"]4/6/2018[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]ABC[/TD]
[TD="align: right"]5/20/2018[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]ABC[/TD]
[TD="align: right"]11/11/2018[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

 

[sheetspread]

Excel 2010
	A	B	C	D
1	Event	Next Event Date	2nd future Event Date	3rd Future Event Date
2	RJB	2/15/2018	3/3/2018	3/17/2018
3	JUT	5/1/2018	5/17/2018	6/3/2018
4	ABC	4/1/2018	4/6/2018	5/20/2018
Front

Cell Formulas
Range	Formula
B2	{=SMALL(IF(Details!$A$1:$A$16=Front!$A2,IF(Details!$B$1:$B$16>=TODAY(),Details!$B$1:$B$16)),COLUMN(A1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[woodie]

I entered the formula and it came up with the next event date, however, I am not sure how I can obtain the 2nd future date and 3rd future date. Any assistance would greatly be appreciated.

 

[sheetspread]

Drag the formula right and down

 

[woodie]

When I drag the formula right and down, it skips the 2nd future event and provides the third future event

Front Sheet
[TABLE="width: 419"]
<tbody>[TR]
[TD]Event[/TD]
[TD]Next Event Date[/TD]
[TD]2nd future Event Date[/TD]
[TD]3rd Future Date[/TD]
[/TR]
[TR]
[TD]RJB[/TD]
[TD]2/15/18[/TD]
[TD]3/3/18[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]JUT[/TD]
[TD]5/1/18[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]4/1/18[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]
Detail Sheet
[TABLE="width: 138"]
<colgroup><col width="64" style="width: 48pt;"> <col width="119" style="width: 90pt; mso-width-source: userset; mso-width-alt: 4165;"> <tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]Event[/TD]
[TD="class: xl65, width: 119, bgcolor: transparent"]Date[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]RJB[/TD]
[TD="class: xl68, width: 119, bgcolor: white"]1/1/18[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]RJB[/TD]
[TD="class: xl68, width: 119, bgcolor: white"]2/15/18[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]RJB[/TD]
[TD="class: xl68, width: 119, bgcolor: white"]2/27/17[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]RJB[/TD]
[TD="class: xl68, width: 119, bgcolor: white"]3/3/18[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]RJB[/TD]
[TD="class: xl68, width: 119, bgcolor: white"]3/17/18[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]RJB[/TD]
[TD="class: xl67, width: 119, bgcolor: white"]3/29/2018[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]RJB[/TD]
[TD="class: xl67, width: 119, bgcolor: white"]6/1/2018[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]JUT[/TD]
[TD="class: xl67, width: 119, bgcolor: white"]5/1/2018[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]JUT[/TD]
[TD="class: xl67, width: 119, bgcolor: white"]5/17/2018[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]JUT[/TD]
[TD="class: xl67, width: 119, bgcolor: white"]6/3/2018[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]JUT[/TD]
[TD="class: xl67, width: 119, bgcolor: white"]7/3/2017[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]ABC[/TD]
[TD="class: xl67, width: 119, bgcolor: white"]1/16/2018[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]ABC[/TD]
[TD="class: xl67, width: 119, bgcolor: white"]4/1/2018[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]ABC[/TD]
[TD="class: xl67, width: 119, bgcolor: white"]4/6/2018[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]ABC[/TD]
[TD="class: xl67, width: 119, bgcolor: white"]5/20/2018[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: white"]ABC[/TD]
[TD="class: xl67, width: 119, bgcolor: white"]11/11/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]

 

[sheetspread]

Because the year is 2017