Lookup & Reference
Find nearest value & closest location with ABS, MIN, and XMATCH
Locate the closest warehouse milepost, resistor value, or price point above OR below a target—unlike approximate VLOOKUP which only steps one way—using ABS distance.
Find the smallest absolute numeric distance
Approximate lower-bracket lookup and nearest-value lookup answer different questions. On an ascending sorted list, approximate VLOOKUP selects the greatest value at or below the target; here that would be 130, not the nearest value 145. Approximate VLOOKUP is not reliable on the unsorted example table.
ABS(B2 - $E$2) measures the numeric gap. MIN finds the smallest gap and exact XMATCH locates its first occurrence in source-row order. Equal-distance ties and duplicate values therefore return the first matching row. This measures one-dimensional numeric proximity, not driving distance or travel time. All five source values and the target are required numeric inputs.
| Depot (A) | Highway Milepost (B) | ABS Distance from Mile 142 (C) | Outcome |
|---|---|---|---|
| North Hub | 95 | |95 - 142| = 47 | 47 miles from target |
| East Yard | 145 | |145 - 142| = 3 | Closest Depot (3 miles away)! |
| Central Port | 180 | |180 - 142| = 38 | Too far north |
| South Gate | 60 | |60 - 142| = 82 | Unsorted order works fine |
| West Annex | 130 | |130 - 142| = 12 | Lower bracket on an ascending sorted list |
Return name of nearest location to target E2
With C2:C6 =ABS(B2 - $E$2), MIN(C2:C6) locates the minimum distance regardless of sort order.
=IF(OR(COUNT(B2:B6)<>5,COUNT(E2)<>1),"Invalid inputs",INDEX(A2:A6, XMATCH(MIN(C2:C6), C2:C6, 0)))Return the nearest numeric value
Looking up B2:B6 at the same XMATCH index returns the closest milepost (145).
=IF(OR(COUNT(B2:B6)<>5,COUNT(E2)<>1),"Invalid inputs",INDEX(B2:B6, XMATCH(MIN(C2:C6), C2:C6, 0)))Try the example
Use the original values or change them to test your understanding.