Select Page

Excel’s data lookup functions are, and have been saving us countless hours on our desks. Microsoft has also recently introduced some new functions like the XLOOKUP and XMATCH to further help with data analysis, and overcome some of the limitations of the older formulas. However, unfortunately, neither of these formulas can find the  second or third matches. In this article, we are going to show you how to find nth closest match in Excel.

If all you need is to find the closest match, you can check the simplified version of this method in How to find closest match in  Excel.

To find the nth match of an exact matched value, you can use VLOOKUP function: How to get nth match with VLOOKUP

## Formula

=INDEX(<return array>,MATCH(SMALL(ABS(<values array>-<value>),<n-value>),ABS(<values array>-<value>),0))

## How

### Closest Match = Minimum Difference

First of all, to find the closest match we need to find the difference between the values in an array containing our search value. This means subtracting it from each value in the array. Since “closest” mathematically means close from both negative and positive directions, the difference of the values should be evaluated in the same direction. The ABS function can help with this by converting negative values into positives.

As a result, we need to get difference values between all values and the search value in the positive direction. Although, a helper column could also help here, we can also use the array formulas to do all calculations inside a single cell. So, the difference formula can be like this:

ABS(\$E\$5:\$E\$13-\$H\$5)

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

{210;123;3;219;123;6;214;123;2}

Although, this formula portion is enough for most scenarios; If a difference value occurs multiple times you will see only a single values. To eliminate the duplication values, you need to add a small enough number that distinguish the values. The ROW function can be helpful to create unique values when it is used with a vertical table like our sample. The function will return a different number for each row. To make the number smaller, divide it by a big number like 100, 1000 or 10000 according to your dataset. The number 100 is enough for us because our data ends at the row 13.

ABS((\$E\$5:\$E\$13+ROW(\$E\$5:\$E\$13)/100)-\$H\$5)

Next step is to find the position of closest matched values. The MATCH function will do the hard work here. The idea is to find the minimum difference (closest match) in an array of distances. In order to find nth minimum value, we need to find nth closest match. The MIN function would be good choice if we want to find the minimum value only, but of course, that’s not why we are here. To find second, third, and other closest matches, the SMALL function will be helpful. The SMALL function returns the nth smallest value from an array. We can use this functionality to find nth smallest difference. For example;

The closest value: SMALL(ABS((\$E\$5:\$E\$13+ROW(\$E\$5:\$E\$13)/100)-\$H\$5),1)

The second closest value: SMALL(ABS((\$E\$5:\$E\$13+ROW(\$E\$5:\$E\$13)/100)-\$H\$5),2)

The third closest value: SMALL(ABS((\$E\$5:\$E\$13+ROW(\$E\$5:\$E\$13)/100)-\$H\$5),3)

You can populate sequential numbers in cells and refer them for nth­ argument.

MATCH(SMALL(ABS((\$E\$5:\$E\$13+ROW(\$E\$5:\$E\$13)/100)-\$H\$5),\$J7),ABS((\$E\$5:\$E\$13+ROW(\$E\$5:\$E\$13)/100)-\$H\$5),0) ### Return value

The rest of the formula is fairly straightforward: It returns an INDEX & MATCH combo where we have an array of values and the position. To find the nth closest match, use the same array of values that were already used.

=INDEX(\$E\$5:\$E\$13,MATCH(SMALL(ABS((\$E\$5:\$E\$13+ROW(\$E\$5:\$E\$13)/100)-\$H\$5),\$J7),ABS((\$E\$5:\$E\$13+ROW(\$E\$5:\$E\$13)/100)-\$H\$5),0))

Alternatively, you can select another related array (for example another column of the same table) to return a value that is in the same row as the matched value. For example; to get the name from our sample table, we can use the C3:C11 range which also contains the names.

=INDEX(\$C\$5:\$C\$13,MATCH(SMALL(ABS((\$E\$5:\$E\$13+ROW(\$E\$5:\$E\$13)/100)-\$H\$5),\$J7),ABS((\$E\$5:\$E\$13+ROW(\$E\$5:\$E\$13)/100)-\$H\$5),0))

The most important step is to press the CTRL + SHIFT + ENTER combination instead of just pressing the ENTER key, to define the formula as an array formula. Otherwise, you will see #N/A errors. 