How to find the closest value to a number in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(MIN(ABS(B2:B9-E1)),ABS(B2:B9-E1),A2:A9)Look up the smallest distance: =XLOOKUP(MIN(ABS(B2:B9-E1)),ABS(B2:B9-E1),A2:A9).
- Result on the example
- Noise-cancelling headphones in
E2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
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(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.
=XLOOKUP(MIN(ABS(B2:B9-E1)),ABS(B2:B9-E1),A2:A9)Result"Noise-cancelling headphones"
B2:B98 rows × 1 col: $129, $279, $349, $139, …A range: 8 cells in one column.
E1$300The value in E1.
B2:B9-E1{8 rows × 1 col} -171, -21, 49, -161, …The - operator subtracts.
ABS(B2:B9-E1){8 rows × 1 col} 171, 21, 49, 161, …ABS: Absolute value.- number
B2:B9-E1{8 rows × 1 col} -171, -21, 49, -161, …
- number
MIN(ABS(B2:B9-E1))21MIN: Smallest number.- number1
ABS(B2:B9-E1){8 rows × 1 col} 171, 21, 49, 161, …
- number1
B2:B9-E1{8 rows × 1 col} -171, -21, 49, -161, …The - operator subtracts.
ABS(B2:B9-E1){8 rows × 1 col} 171, 21, 49, 161, …ABS: Absolute value.- number
B2:B9-E1{8 rows × 1 col} -171, -21, 49, -161, …
- number
A2:A98 rows × 1 col: "Bluetooth speaker", "Noise-cancelling headphones", "Smartwatch", "E-reader", …A range: 8 cells in one column.
XLOOKUP(MIN(ABS(B2:B9-E1)),ABS(B2:B9-E1),A2:A9)"Noise-cancelling headphones"XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
MIN(ABS(B2:B9-E1))21 - lookup_array
ABS(B2:B9-E1){8 rows × 1 col} 171, 21, 49, 161, … - return_array
A2:A98 rows × 1 col: "Bluetooth speaker", "Noise-cancelling headphones", "Smartwatch", "E-reader", …
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(MIN(ABS(B2:B9-E1)),ABS(B2:B9-E1),A2:A9). We ran it in Google Sheets mode on the same data and it returns Noise-cancelling headphones.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(A2:A9,MATCH(MIN(ABS(B2:B9-E1)),ABS(B2:B9-E1),0)) | Noise-cancelling headphones | Every Excel version (Ctrl+Shift+Enter in 2019 and earlier). |
| =XLOOKUP(E1,B2:B9,A2:A9,,-1) | Noise-cancelling headphonesDifferent result | 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.
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 value to a number
E2, return the product whose price is closest to the budget in E1, above or below.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).
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.