How to find the closest date to a given date in Excel
Excel formula
=XLOOKUP(MIN(ABS(A2:A7-E1)),ABS(A2:A7-E1),A2:A7)Find the smallest gap with MIN(ABS(…)) and look it up: =XLOOKUP(MIN(ABS(A2:A7-E1)),ABS(A2:A7-E1),A2:A7).
- Result on the example
- 6/12/2026 in
E2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
How it works
ABS(A2:A7-E1) is how far each date is from the target in days, ignoring the direction. MIN finds the smallest gap, and XLOOKUP returns the date with that gap: June 12 is 3 days away, closer than June 19 at 4.
=XLOOKUP(MIN(ABS(A2:A7-E1)),ABS(A2:A7-E1),A2:A7)Result46185
A2:A76 rows × 1 col: 5/28/2026, 6/4/2026, 6/12/2026, 6/19/2026, …A range: 6 cells in one column.
E16/15/2026The value in E1.
A2:A7-E1{6 rows × 1 col} -18, -11, -3, 4, …The - operator subtracts.
ABS(A2:A7-E1){6 rows × 1 col} 18, 11, 3, 4, …ABS: Absolute value.- number
A2:A7-E1{6 rows × 1 col} -18, -11, -3, 4, …
- number
MIN(ABS(A2:A7-E1))3MIN: Smallest number.- number1
ABS(A2:A7-E1){6 rows × 1 col} 18, 11, 3, 4, …
- number1
A2:A7-E1{6 rows × 1 col} -18, -11, -3, 4, …The - operator subtracts.
ABS(A2:A7-E1){6 rows × 1 col} 18, 11, 3, 4, …ABS: Absolute value.- number
A2:A7-E1{6 rows × 1 col} -18, -11, -3, 4, …
- number
XLOOKUP(MIN(ABS(A2:A7-E1)),ABS(A2:A7-E1),A2:A7)46185XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
MIN(ABS(A2:A7-E1))3 - lookup_array
ABS(A2:A7-E1){6 rows × 1 col} 18, 11, 3, 4, … - return_array
A2:A76 rows × 1 col: 5/28/2026, 6/4/2026, 6/12/2026, 6/19/2026, …
- lookup_value
In Google Sheets
Google Sheets needs a different formula: =ARRAYFORMULA(XLOOKUP(MIN(ABS(A2:A7-E1)),ABS(A2:A7-E1),A2:A7)). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(A2:A7,MATCH(MIN(ABS(A2:A7-E1)),ABS(A2:A7-E1),0)) | 6/12/2026 | INDEX and MATCH; in Excel 2019 and earlier, confirm it with Ctrl+Shift+Enter. |
| =XLOOKUP(E1,A2:A7,A2:A7,,-1) | 6/12/2026Different result | The closest date on or before the target, for "as of" lookups. Only looks at dates on or before the target. |
Common mistakes
- With a tie, one date as far before as another is after, XLOOKUP returns the first in the list.
- In Google Sheets, wrap the formula in ARRAYFORMULA so ABS works on the whole range.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Find the closest date to a given date
E2, return the reading date closest to the target date in E1, before or after.Questions
How do I return the value on the closest date?
Point XLOOKUP at the value column: =XLOOKUP(MIN(ABS(A2:A7-E1)),ABS(A2:A7-E1),B2:B7).
How do I get the next date on or after the target?
=XLOOKUP(E1,A2:A7,A2:A7,,1), or =MINIFS(A2:A7,A2:A7,">="&E1).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.