Learn How to Get nth Match in Microsoft Excel
The main purpose of this formula is to get the position of the nth match, for example, the 2nd matching value, the 3rd matching value, etc. Here we will learn how to get an nth match in the given data range in the workbook in Microsoft Excel.
That implies, with the help of a formula based on the SMALL function you can able to get the position of the nth match, for example, the 2nd matching value, the 3rd matching value, etc. So, with the help of this formula, you can able to get an nth match in the given data range in the workbook in Microsoft Excel.
General Formula to Get nth Match
=SMALL(IF(rng=value,ROW(rng)-MIN(ROW(rng))+1),n)
The Explanation to Get nth Match
So we know that with the help of the given formula above you can able to get the position of the nth match, for example, the 2nd matching value, the 3rd matching value, etc. Here we will learn how to get an nth match in the given data range in the workbook in Microsoft Excel.
As we know the core of this formula is a SMALL function. Which is used to return the nth smallest value in a list of values that correspond to row numbers. The row numbers have been “filtered” by the IF statement, which applies the logic for a match.
Working from the inside out, IF compares all values in the named range “list” to the value in the B column, which creates an array. So now you have learned how to get an nth match in the given data range in the workbook in Microsoft Excel.