Lookup
Find Closest Numeric Match
Find the value in a list that is closest to a target number. Useful for finding the nearest price point, closest measurement, or best-matching threshold.
Excel Formula
=INDEX(A2:A100, MATCH(MIN(ABS(A2:A100-C2)), ABS(A2:A100-C2), 0))Step-by-Step Explanation
1
ABS(A2:A100-C2) calculates the distance from each value to the target
2
MIN finds the smallest distance
3
MATCH locates which row has that smallest distance
4
INDEX returns the actual value from that row
5
This is an array formula — Ctrl+Shift+Enter in older Excel
Example
| Available Sizes (A) | Target (C) | Closest (D) |
|---|---|---|
| 10 | 27 | 25 |
| 15 | ||
| 20 | ||
| 25 | ||
| 50 |
Result: Target = 27, Closest available size = 25
Common Variations
Closest without going over
=INDEX(A2:A100,MATCH(C2,A2:A100,1))Like Price Is Right — closest without exceeding
Closest above
=INDEX(A2:A100,MATCH(C2,A2:A100,-1))Closest value that is >= target
Need a Custom Version?
Use our AI generator to create a formula tailored to your specific data and requirements.
Try It with AI →