# How to find the closest value to a number in Excel

Look up the smallest distance: `=XLOOKUP(MIN(ABS(B2:B9-E1)),ABS(B2:B9-E1),A2:A9)`.

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

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Product | Price |  | Budget | 300 |
| Bluetooth speaker | 129 |  | Closest price |  |
| Noise-cancelling headphones | 279 |  |  |  |
| Smartwatch | 349 |  |  |  |
| E-reader | 139 |  |  |  |
| Tablet | 329 |  |  |  |
| Action camera | 219 |  |  |  |
| Mechanical keyboard | 159 |  |  |  |
| Fitness tracker | 99 |  |  |  |

Formula in E2: `=XLOOKUP(MIN(ABS(B2:B9-E1)),ABS(B2:B9-E1),A2:A9)`

Result: Noise-cancelling headphones

## How it works

`ABS(B2:B9-E1)` is the distance of every price from the budget: the headphones are $21 away, the tablet $29. MIN picks the smallest distance, and XLOOKUP finds it in the list of distances and returns the product on that row.

## Other ways

- `=INDEX(A2:A9,MATCH(MIN(ABS(B2:B9-E1)),ABS(B2:B9-E1),0))`: Every Excel version (Ctrl+Shift+Enter in 2019 and earlier).
- `=XLOOKUP(E1,B2:B9,A2:A9,,-1)`: When the price must not go over the budget: match mode -1 returns the exact price or the next one below. The list doesn't need sorting. (Closest without going over: the headphones here too, but with a $345 budget it picks the tablet, not the closer smartwatch.)

## Common mistakes

- Leaving out ABS finds the most negative difference, the cheapest item, instead of the closest.
- With a tie, two prices the same distance away, the one higher in the list wins.

## Questions

**How do I return the closest price instead of the product?**

Return the price column: `=XLOOKUP(MIN(ABS(B2:B9-E1)),ABS(B2:B9-E1),B2:B9)`.

**How do I find the closest date?**

The same formula works on dates, since dates are numbers: `=XLOOKUP(MIN(ABS(A2:A20-E1)),ABS(A2:A20-E1),A2:A20)`.

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-closest-value