# How to find the closest date to a given date in Excel

Find the smallest gap with MIN(ABS(…)) and look it up: `=XLOOKUP(MIN(ABS(A2:A7-E1)),ABS(A2:A7-E1),A2:A7)`.

**Excel:** `=XLOOKUP(MIN(ABS(A2:A7-E1)),ABS(A2:A7-E1),A2:A7)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** `=ARRAYFORMULA(XLOOKUP(MIN(ABS(A2:A7-E1)),ABS(A2:A7-E1),A2:A7))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Reading date | Value |  | Target date | 2026-06-15 |
| 2026-05-28 | 41 |  | Closest date |  |
| 2026-06-04 | 44 |  |  |  |
| 2026-06-12 | 47 |  |  |  |
| 2026-06-19 | 46 |  |  |  |
| 2026-06-26 | 49 |  |  |  |
| 2026-07-03 | 51 |  |  |  |

Formula in E2: `=XLOOKUP(MIN(ABS(A2:A7-E1)),ABS(A2:A7-E1),A2:A7)`

Result: 6/12/2026

## 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.

## Other ways

- `=INDEX(A2:A7,MATCH(MIN(ABS(A2:A7-E1)),ABS(A2:A7-E1),0))`: INDEX and MATCH; in Excel 2019 and earlier, confirm it with Ctrl+Shift+Enter.
- `=XLOOKUP(E1,A2:A7,A2:A7,,-1)`: 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.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/find-the-closest-date