Exceljet

Quick, clean, and to the point

Find closest match

Excel formula: Find closest match
Generic formula 
{=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0))}
Summary 

To find the closest match in numeric data, you can use INDEX and MATCH, with help from the ABS and MIN functions. In the example shown, the formula in F5, copied down, is:

=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))

where trip (B5:B14) and cost (C5:C14) are named ranges.

In F5, F6, and F7, the formula returns the trip closest in cost to 500, 1000, and 1500, respectively.

Note: this is an array formula and must be entered with control + shift + enter, except in Excel 365.

Explanation 

At the core, this is an INDEX and MATCH formula: MATCH locates the position of the closest match, feeds the position to INDEX, and INDEX returns the value at that position in the Trip column. The hard work is done with the MATCH function, which is carefully configured to match the "minimum difference" like this:

MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)

Taking things step-by-step, the lookup value is calculated with MIN and ABS like this:

MIN(ABS(cost-E5)

First, the value in E5 is subtracted from the named range cost (C5:C14). This is an array operation, and since there are 10 values in the range, the result is an array with 10 values like this:

{899;199;250;-201;495;1000;450;-101;500;795}

These numbers represent the difference between each cost in C5:C15 and the cost in cell E5, 700. Some values are negative because a cost is lower than the number in E5. To convert negative values to positive values, we use the ABS function: 

ABS({899;199;250;-201;495;1000;450;-101;500;795})

which returns:

{899;199;250;201;495;1000;450;101;500;795}

We are looking for the closest match, so we use the MIN function to find the smallest difference, which is 101:

MIN({899;199;250;201;495;1000;450;101;500;795}) // returns 101

This becomes the lookup value inside MATCH. The lookup array is generated as before:

ABS(cost-E5) // generate lookup array

which returns the same array we saw earlier:

{899;199;250;201;495;1000;450;101;500;795}

We now have what we need to find the position of the closest match (smallest difference), and we can rewrite the MATCH portion of the formula like this:

MATCH(101,{899;199;250;201;495;1000;450;101;500;795},0) // returns 8

With 101 as the lookup value, MATCH returns 8, since 101 is in the 8th position in the array. Finally, this position is fed into INDEX as the row argument, with the named range trip as the array:

=INDEX(trip,8)

and INDEX returns the 8th trip in the range, "Spain". When the formula is copied down to cells F6 and F7, it finds the closest match to 1000 and 1500, "France" and "Thailand" as shown.

Note: if there is a tie, this formula will return the first match.

With XLOOKUP

The XLOOKUP function provides an interesting way to solve this problem, because a match type of 1 (exact match or next largest) or -1 (exact match or next smallest) doesn't require data to be sorted. This means we can write a formula like this:

=XLOOKUP(0,ABS(cost-E5),trip,,1)

As above, we use the absolute value of (cost-E5) to create a lookup array:

{899;199;250;201;495;1000;450;101;500;795}

Then we configure XLOOKUP to look for zero, with match type set to 1, for exact match or next largest. We supply the named range trip as the return array, so the result is "Spain" as before.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.