Lookup & Reference
Find the closest match to a target number (unsorted lookup)
Locate the nearest standard gauge, size, or setpoint—whether above or below your target—using squared differences inside MIN and XLOOKUP.
Why approximate XLOOKUP/VLOOKUP is NOT a closest-value match
Approximate match (match_mode = -1 or 1) returns an exact match when available, otherwise the next smaller or next larger value: for target 21 mm against standard pipe sizes {12, 19, 25, 32}, next-larger returns 25 mm (4 mm off) even though 19 mm is only 2 mm away.
Squaring the array difference (A2:A5 - C2)^2 produces non-negative squared distances {81, 4, 16, 121} without needing unsorted sorting. XLOOKUP(MIN(d), d, B2:B5) returns the part with minimum distance ("P-19").
| SizeMM (A) | PartCode (B) | Target = 21 | Squared Diff (A - 21)^2 |
|---|---|---|---|
| 12 | P-12 | diff -9 | 81 |
| 19 | P-19 | diff -2 (closest) | 4 |
| 25 | P-25 | diff +4 | 16 |
| 32 | P-32 | diff +11 | 121 |
Closest part code and distance formulas
Bind d = (A2:A5 - C2)^2 with LET so the array difference is computed once.
=LET(d, (A2:A5 - C2)^2, XLOOKUP(MIN(d), d, B2:B5))Exact absolute deviation to closest match
SQRT(MIN((A2:A5 - C2)^2)) returns the absolute distance in original units (2 mm).
=SQRT(MIN((A2:A5 - C2)^2))Inputs and interpretation
Use a nonempty numeric range with no errors or blanks, and a numeric target in the same unit. Equal distances return the first matching row. Squaring extreme magnitudes may overflow, so rescale if necessary. Nearest size is a numerical result, not a determination of mechanical suitability or tolerance.
Try the example
Use the original values or change them to test your understanding.