How to find the closest date after

[slgalvan]

Regarding the following question and answer from a previous thread:

Question: I have 2 columns, one has couple dates such as 2/15/2006, 5/10/2006, 5/15/2006, 5/20/2006, 8/15/2006, and 10/15/2006, the other one has a value of 6/15/2006. I want to find the closest date before 6/15/2006. Could you please help me out with this problem?

"iliace" responded:

If the column of dates is always in ascending order (later dates at the bottom), you can use the MATCH() function. Let's say 6/15/2006 is in A1, and the other dates are in B1:B10.

=INDEX($B$1:$B$10, MATCH(A1, $B$1:$B$10, 1))

Will return the closest date before the date in A1.

My Question: How do I get the closest date after the date in A1?

 

[JoeMo]

Your formula will return the closest date before or on the date in A1, provided the dates in B1:B10 are sorted in ascending order.
If you sort the dates in B1:B10 in descending order, you can use the formula below to return the closest date after or on the date in A1.

Book2.xlsm
	A	B	C	D
1	3/7/2022	3/11/2022		3/8/2022
2		3/8/2022
3		3/6/2022
4		3/5/2022
5		3/5/2022
6		2/28/2022
7		2/24/2022
8		2/23/2022
9		2/22/2022
10		2/22/2022
List

Cell Formulas
Range	Formula
D1	D1	=INDEX($B$1:$B$10,MATCH(A1,$B$1:$B$10,-1))

 

[slgalvan]

Thank you!!

 

[JoeMo]

Thank you!!

You are welcome - thanks for the reply.