Select Page

This article shows How to find closest match by using INDEX, MATCH, ABS and MIN functions. Excel's array formula ability to evaluate formula for each cells in an array is key factor in this case.

# Syntax

{ =INDEX( return array,

MATCH(

MIN(ABS( search array – search value )),

ABS(search array – search value),

0

)

)  }

# Steps

2. Enter the array reference that includes the data to return E3:E11,
3. Continue with MATCH MATCH(
4. Use MIN function for the first argument of MATCH MIN(
5. Add ABS function to find absolute value of difference between search array and the value we search ABS(E3:E11-I3)
6. Close MIN function and continue with next argument ),
7. Once again add absolute value of difference between search array and the value we search ABS(E3:E11-I3),
8. Use 0 for match type argument and close the functions 0))
9. Press CTRL + SHIFT + ENTER instead of regular ENTER key to define formula as an array formula.

# How

#### Closest Match = Minimum Difference

First of all, to find the closest match we should find the difference between the values in array and our search value. This means to make subtraction operation for each value in array. Because the closest means the close from both negative and positive direction, the difference values should be evaluated in same direction. The ABS function can help us in this matter by converting negative values to positive.

As a result, we need to get difference values between all values and search value in positive direction. Although, a helper column would be helpful in this, we can use array formula feature to complete all calculation in a single cell. So difference formula can be like this:

ABS(E3:E11-I3)

Normally, subtracting a value from an array returns a result only for the first cell of array. However, converting formula into an array formula enforces Excel to calculate for each cell. As a result, the formula returns

{218;131;11;227;131;2;222;131;6}.

Next step is to find the position of closest matched value. The MATCH function will do the hard work here. The logic is to find minimum difference (closest match) in an array of distances. There is nothing better than the MINIMUM function to find the minimum value in an array.

So, to find the position of the minimum difference, the formula will be:

MATCH(MIN(ABS(E3:E11-I3)),ABS(E3:E11-I3),0)