In this article, we are going to show you how to find nth closest match in Excel.
To find the nth match of an exact matched value, you can use the VLOOKUP function: How to get nth match with VLOOKUP
Formula
How it works
Closest Match = Minimum Difference
To find the closest match, we need to find the difference between the values in an array containing our search value. This means subtracting the search value from each value in the array. Closest match in this can mean from both negative and positive directions, therefore the difference of the values should be evaluated in the same direction. The ABS function can help with this by converting negative values into positive.
As a result, we need to get the difference 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 makes Excel calculate for each cell. As a result, the formula returns:
{210;123;3;219;123;6;214;123;2}
Although, this formula can be enough for most scenarios, you will see only a single value if a difference value occurs multiple times in the array. To eliminate the duplication values, you need to add a small enough number to distinguish the value. The ROW function can be helpful to create unique values when used with a vertical table like in our sample. The function will return a different number for each row. To make the number smaller, divide it by a large number like 100, 1000 or 10000 depending on your data. 100 is enough for this example, since the data ends at 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. To find second, third, and other closest matches, the SMALL function can be used. 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 pressing the CTRL + SHIFT + ENTER key combination (instead of just pressing the ENTER key) to define the formula as an array formula. Otherwise, you will see #N/A errors.
