Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to find the closest matching value in Excel and Google Sheets
Topic:Lookups (any direction)Excel & Google Sheets
=INDEX(B2:B5,MATCH(MIN(ABS(B2:B5-D2)),ABS(B2:B5-D2),0))Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Put your target value in a cell (e.g. D2).
- 2Enter =INDEX(B2:B5,MATCH(MIN(ABS(B2:B5-D2)),ABS(B2:B5-D2),0)) in the result cell.
- 3In Excel (non-365), confirm with Ctrl+Shift+Enter. In Google Sheets and Excel 365, press Enter.
- 4ABS(B2:B5-D2) calculates the absolute difference for each value; MIN finds the smallest difference; MATCH locates its position; INDEX returns the original value.
Tips
- If two values are equally close to the target, this formula returns the first one found. To return the second, adjust the MATCH starting position.
- To return the label associated with the closest value rather than the value itself, change the INDEX range to the labels column.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
What if the target exactly matches a value in the list?
The formula works correctly — ABS gives 0 for an exact match, which is the minimum, so the exact match is returned.
Can I find the closest value above or below the target?
Yes — filter the differences to only positive (above) or only negative (below) before finding the MIN.
More on Lookups (any direction)
See all →Reference
HLOOKUP
Look up a value in the top row of a table and return a value from a row below it — the horizontal cousin of VLOOKUP.
How-toHow to look up the last matching value
Use LOOKUP with a search trick or XLOOKUP with search mode -1 to find the last occurrence of a value in a list.
How-toHow to do a lookup with wildcards
Use VLOOKUP or MATCH with wildcard characters (* and ?) to perform partial-match lookups on text values.
How-toHow to do a case-sensitive lookup
Use INDEX with MATCH and the EXACT function to perform a lookup that distinguishes between uppercase and lowercase text.
How-toHow to look up a value and sum the matches
Use SUMIF or SUMIFS to sum all values that match a lookup criterion, returning the total of all matching rows rather than a single result.
How-toHow to look up a value across multiple columns
Use MATCH with a combined range or INDEX to search for a value across several columns and return the column it was found in or its adjacent value.