How to find the closest match in Excel

Closest value with INDEX, MATCH, MIN and ABS

A warehouse packs orders by volume. An order needs 36 litres (E2) and you want the carton closest in size. The list in column B is not sorted, which is normal for a packing list and is exactly why the classic approximate VLOOKUP does not help here.

Read the formula from the inside out. B2:B8-E2 subtracts the target from every volume. ABS turns each difference into a distance, so 33 litres is 3 away and 40 litres is 4 away. MIN picks the smallest distance, and MATCH with 0 finds where that distance sits in the list. INDEX returns the carton name from the same row.

In Excel 365 and 2021 or later this works as a normal formula. In Excel 2019 and older it is an array formula: press Ctrl+Shift+Enter instead of Enter, or it can return the wrong row or #VALUE!.

See how close the match is

"Closest" can still be far off. Show the gap next to the result so nobody ships a 12-litre mailer for a 36-litre order just because it was the best of a bad list. The MIN(ABS()) part on its own is the gap.

To return the matching value instead of the name, point INDEX at column B: =INDEX(B2:B8,MATCH(MIN(ABS(B2:B8-E2)),ABS(B2:B8-E2),0)) returns 33. You can then flag large gaps with something like =IF(gap>5,"Check","OK").

Next larger value: XLOOKUP with match_mode 1

For a carton the closest size is often the wrong question: a 33-litre box will not hold 36 litres of stock. What you want is the smallest carton that is at least 36. XLOOKUP's fifth argument, match_mode, does this. 1 means "exact match, or else the next larger item".

Unlike VLOOKUP or MATCH with approximate match, XLOOKUP does not need the list sorted for match_mode 1 or -1. It finds the right answer in this unsorted list. Use the same pattern for shipping weight bands, minimum order quantities and pack sizes.

Next smaller value: match_mode -1

-1 means "exact match, or else the next smaller item". Use it for anything that works like a floor: discount tiers ("spend 500 or more"), commission bands, or the last price break you have passed.

Both modes return an exact match first if there is one. =XLOOKUP(40,B2:B8,A2:A8,,-1) returns Box L because 40 is in the list. Only when the exact value is missing does it fall back to the neighbour.

Ties: the first one in the list wins

The order in E3 needs 30 litres. Box M (27) and Cube C1 (33) are both exactly 3 away. MATCH returns the first position that holds the smallest gap, so the answer depends on row order, not on which carton is better.

If ties matter, decide the rule yourself. Sort the list so the one you prefer comes first, or skip the tie entirely by using next larger (match_mode 1) when rounding up is the safe choice. In Excel 365 you can list every tied item with FILTER: =FILTER(A2:A8,ABS(B2:B8-E3)=MIN(ABS(B2:B8-E3))) spills Box M and Cube C1.

When nothing is big enough

An 80-litre order (E4) is larger than every carton, so a next-larger lookup has nothing to return and gives #N/A. XLOOKUP's fourth argument, if_not_found, replaces that with a message the packer will understand.

The closest-match formula never fails this way: it always returns something, which here would be Box 2XL at 72 litres. That is the main reason to choose between them deliberately. Closest is right for "which reading is nearest", next larger is right for "what will fit".

Which method should you use?

Use INDEX/MATCH with MIN(ABS()) when you truly want the nearest value in either direction: the nearest sensor reading, the closest date to a deadline, the price nearest a budget. It works in every version of Excel.

Use XLOOKUP match_mode 1 when the result must be at least the target (sizes, capacity, minimums) and -1 when it must not exceed it (tiers, bands, thresholds). On Excel 2019 or older, the equivalent is MATCH(E2,B2:B8,1) for next smaller, but only on a list sorted ascending; on an unsorted list it silently returns wrong rows.