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))}
Explanation 

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.

How this formula works

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.

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.