Find the Closest Match in Excel

To find the closest match in Excel, work out how far every value is from your target, then look up the one with the smallest gap. With XLOOKUP that is one formula: =XLOOKUP(MIN(ABS(B2:B8-E1)),ABS(B2:B8-E1),A2:A8). This guide shows that method step by step, explains when "next smaller" or "next larger" is what you really need, and gives a version for older Excel.

The sample data#

The examples use this list of laptop models and prices. It is not sorted, which is normal for real data:

The target, a budget of 670, is in E1. No price is exactly 670, so an exact lookup returns #N/A. The question is which model comes closest.

How to find the closest match with XLOOKUP and ABS#

The closest value is the one with the smallest difference from the target, whether it is above or below. In E2, type:

=XLOOKUP(MIN(ABS(B2:B8-E1)),ABS(B2:B8-E1),A2:A8)

The result is C300. Here is how it works:

1. B2:B8-E1 subtracts 670 from every price: -220, -90, 50, -60, 229, -155, 380.
2. ABS(...) turns each one into a positive gap: 220, 90, 50, 60, 229, 155, 380. ABS returns the absolute value, the number without its sign.
3. MIN(...) finds the smallest gap: 50.
4. XLOOKUP looks for 50 in the list of gaps and returns the model in the same position, C300 (720 is 50 above the budget).

This needs Excel 2021 or Microsoft 365, because XLOOKUP and formulas that work on a whole range at once (dynamic arrays) arrived there. To return the price instead of the model, change the last range to B2:B8. The XLOOKUP function guide covers each argument.

Closest vs next smaller vs next larger#

"Closest" can mean three different things, and the right formula depends on which one you need. XLOOKUP has a fifth argument, match_mode, for the other two:

Notice that "next smaller" gives D400, which is 60 away, while the true closest is C300, 50 away. Use next smaller when the rule is "the highest value that doesn't go over", like tax bands or bonus tiers. Use next larger for "the smallest one that is big enough", like a box size or a shipping band. Both work on unsorted data. The guide to XLOOKUP approximate match has more tier examples.

How to find the closest match in older Excel with INDEX and MATCH#

In Excel 2019 and earlier there is no XLOOKUP, but INDEX and MATCH can do the same job. MATCH returns a position and INDEX returns the value at that position:

=INDEX(A2:A8,MATCH(MIN(ABS(B2:B8-E1)),ABS(B2:B8-E1),0))

The result is again C300. The 0 tells MATCH to find the gap of 50 exactly. In older Excel, this is an array formula: press Ctrl+Shift+Enter on Windows, or Control+Shift+Return on a Mac, instead of Enter. Excel then shows curly brackets around the formula. If you press only Enter, you may get a wrong answer or #VALUE!. In Microsoft 365, plain Enter works.

If you have not combined these two functions before, read how INDEX MATCH works first, and see the MATCH function guide for the match types.

Next smaller or larger with MATCH on sorted data#

MATCH can also find the next smaller or next larger value without ABS, but only on sorted data. With match type 1, MATCH returns the position of the largest value that is less than or equal to the target. The list must be sorted from smallest to largest.

Sorted, the prices are 450, 515, 580, 610, 720, 899, 1,050. On that list:

=MATCH(670,B2:B8,1)

returns 4, the position of 610. Match type -1 finds the smallest value that is greater than or equal to the target, but it needs the list sorted from largest to smallest. If the data isn't sorted the right way, MATCH gives a wrong position without any warning, which is why the ABS method or XLOOKUP is safer. The article on the MATCH function explains all three match types.

What happens with ties, blanks and text#

The ABS method returns the first value in the list when two are equally close. With a target of 665, both 610 and 720 are 55 away. The formula returns C300, because C300 (720) comes before D400 (610) in the list. If you need a rule such as "prefer the cheaper one", sort the list by price first so the smaller value comes first.

A few other things to watch:

• Blank cells count as 0, so a blank price can become the "closest" for a small target. Fill or remove blank rows.
• Text in the number column makes the subtraction fail, and the formula returns #VALUE!. Check for numbers stored as text.
• Closest text is a different problem. For partial text matches, such as a name that contains "Lap", use wildcards; see XLOOKUP wildcard matches.

Return more than one column or the gap itself#

Sometimes you want the gap as well as the item, for example to show how far over budget the closest model is. The gap is just the MIN part on its own:

=MIN(ABS(B2:B8-E1))

This returns 50. To see whether the closest price is above or below the target, subtract the target from the price XLOOKUP returns: =XLOOKUP(MIN(ABS(B2:B8-E1)),ABS(B2:B8-E1),B2:B8)-E1 gives 50, so it is 50 over. A negative result would mean under.

XLOOKUP can also return several columns at once. If column C held a screen size, =XLOOKUP(MIN(ABS(B2:B8-E1)),ABS(B2:B8-E1),A2:C8) would spill the model, price and screen size into three cells.

Practise it#

Try the exercise on this page: it finds the model closest to the budget with XLOOKUP and ABS. Then practise XLOOKUP match modes in a live spreadsheet, where you place values into tiers with next smaller and next larger matches and get instant feedback. For older files, the INDEX and MATCH lesson shows how to look up in any direction.