How to find the Closest Match in Excel?

o 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. 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.

Generic formula

{=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0))}