Prioritising Dates

[IanDouglas]

Hello all,

I have a lot of data with three input columns (End date, Break Date, Rent review) and I can't work out a formula for the output column (LEASE EVENT).

In each row, i want to know the closest date FROM today date, with the corresponding column heading to end up in the output column. A mannual example below

End date (to)	BreakDate	Rent Review	LEASE EVENT
30/06/2025	01/07/2020	01/07/2015	Break Date
30/06/2026	n/a	01/07/2016	End Date
24/02/2018	25/03/2013	25/07/2017	Rent Review
03/11/2019	04/11/2015	04/11/2015	??

<colgroup><col span="4"></colgroup><tbody>
</tbody>

I need help building a formula for this.

Kind regards,

Ian

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[DRSteele]

Welcome to the forum.

I think you meant to say that you want the next closest data to today's date. Your sample data includes dates that are closer to today but have already passed.

This works, but it is admittedly bulky. Copy D2 down as necessary.

	A	B	C	D
1	End date (to)	BreakDate	Rent Review	LEASE EVENT
2	6/30/2025	7/1/2020	7/1/2015	BreakDate
3	6/30/2026		7/1/2016	End date (to)
4	2/24/2018	3/25/2013	7/25/2017	Rent Review
5	11/3/2019	11/4/2015	11/4/2015	End date (to)

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet27​

Array Formulas Cell Formula D2 {=INDEX($A$1:$C$1,,MAX((MIN(IF((A2:C2-TODAY())>0,(A2:C2-TODAY()),10^30))=(A2:C2-TODAY()))*(COLUMN(A2:C2)-COLUMN(A2)+1)))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself.

<tbody>
</tbody>

 

[IanDouglas]

Welcome to the forum.

I think you meant to say that you want the next closest data to today's date. Your sample data includes dates that are closer to today but have already passed.

This works, but it is admittedly bulky. Copy D2 down as necessary.

	A	B	C	D
1	End date (to)	BreakDate	Rent Review	LEASE EVENT
2	6/30/2025	7/1/2020	7/1/2015	BreakDate
3	6/30/2026		7/1/2016	End date (to)
4	2/24/2018	3/25/2013	7/25/2017	Rent Review
5	11/3/2019	11/4/2015	11/4/2015	End date (to)

<tbody>
</tbody>

Sheet27​

Array Formulas Cell Formula D2 {=INDEX($A$1:$C$1,,MAX((MIN(IF((A2:C2-TODAY())>0,(A2:C2-TODAY()),10^30))=(A2:C2-TODAY()))*(COLUMN(A2:C2)-COLUMN(A2)+1)))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself.

<tbody>
</tbody>

[

Worked like a charm! Thankyou very much for our help DrSteele]