Find the Closest Numeric Match

Excel Formulas › Lookup

All versionsINDEX/MATCH

Which value is nearest a target — the closest price, the nearest size? Find the smallest absolute difference, then return the matching item. No exact match required.

Quick formula: return the item whose value is closest to the target in E2:
=INDEX(items, MATCH(MIN(ABS(values-E2)), ABS(values-E2), 0))
ABS(values−target) is the distance of each; MIN finds the smallest; MATCH locates it; INDEX returns the item. (Array formula.)

Functions used (tap for the full reference guide):

INDEXMATCHMIN

The example

Closest standard size to a target of 47.

AB
1SizeΔ from 47
2407
3452
4558

The formula

Nearest value by absolute distance:

=INDEX(items, MATCH(MIN(ABS(vals-E2)), ABS(vals-E2), 0)) // closest to 47 → 45

How it works

Distance, minimum, then locate:

  1. ABS(values − target) gives each value’s distance from the target.
  2. MIN(…) finds the smallest distance.
  3. MATCH(min, distances, 0) finds which row has it; INDEX returns the corresponding item.
  4. In Excel 365 it just works; in older Excel enter it as an array formula (Ctrl+Shift+Enter).

Ties go to the first. If two values are equally close, MATCH returns the earlier one. To always round toward the smaller (or larger) value instead, an XLOOKUP approximate match with mode -1 (or 1) on sorted data is simpler.

Try it: interactive demo

Live demo

Find the closest standard size.

Closest:

Variations

Closest distance value

How far off:

=MIN(ABS(vals-E2))

XLOOKUP nearest (sorted)

Next smaller/larger:

=XLOOKUP(E2, sorted, items, , -1)

Closest above only

Ignore smaller values:

=MIN(IF(vals>=E2, vals))

Pitfalls & errors

Array entry pre-365. The ABS-based formula is an array formula — press Ctrl+Shift+Enter in Excel 2019 and earlier.

Ties pick the first. Equal distances return the earliest row; decide if that matters.

Blanks skew distance. A blank counts as 0, which may be the “closest” to a small target — clean the data.

Practice workbook

📊
Download the free Find the Closest Numeric Match practice workbook
A closest-match sheet with the array INDEX/MATCH, the distance, and XLOOKUP-nearest variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I find the closest value to a number in Excel?
Use =INDEX(items, MATCH(MIN(ABS(values-target)), ABS(values-target), 0)). It finds the smallest absolute distance and returns the matching item. Enter as an array formula before Excel 365.
How do I get just the nearest value (not the item)?
On sorted data, =XLOOKUP(target, sorted, sorted, , -1) (or 1) returns the next-smaller (or larger) value — simpler than the ABS approach.
What happens with ties?
MATCH returns the first of equally-close values. Use an XLOOKUP approximate match if you need a defined rounding direction.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: XLOOKUP approximate · Index-match 2D · Min if criteria

Function references: INDEX · MATCH · MIN