Formula to locate the date from a list of dates that is less than but closest to a specific date

[JillF1015]

I have a column of specific dates (sprint end dates). When determining delivery milestone dates for a project, I need to know which sprint a milestone date falls in. I can look it up manually but where is the fun in that? 

For example: if a project estimate determines a design must be completed by 11/10, I want to know the sprint end date of the sprint it would fall into?

Data Table:
Sprint	Start Date	End Date
OCT24-21	10/9/2024	10/22/2024
OCT24-22	10/23/2024	11/5/2024
NOV24-23	11/6/2024	11/19/2024
NOV24-24	11/20/2024	12/3/2024
DEC24-25	12/4/2024	12/17/2024
DEC24-26	12/18/2024	12/31/2024
Milestone date:	11/10/2024
Sprint End Date:	11/19/2024

[#2]

@JillF1015 

You can use SUMPRODUCT to obtain the sprint end date:

=SUMPRODUCT((B9>=B2:B7)*(B9<=C2:C7)*C2:C7)

Please see attached sample workbook.

[#3]

Thank you!! That works perfectly!

[#4]

You're welcome. Have a good weekend!

[#5 · Accepted solution]

@JillF1015 

Since you didn't state the version of Excel you use, here is a solution designed for 365 

= XLOOKUP(milestone, startDate, endDate,,-1)