Find Nth Matching value with Single Criteria in Excel using INDEX!!

As we all know, the INDEX function returns the value at a given position in a range or array, and it is frequently used together with the MATCH function. In this tutorial, we will show how to find the N^th matching value with single criteria in Excel?. Let’s get into this article!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

Steps to get the N^th Match:

• Please do follow the below steps to find the N^th Matching value in Excel.
• You can use the below formula based on the INDEX function.

=INDEX(data,SMALL(IF(ranges=value,ROW(ranges)-ROW(INDEX(ranges,1,1))+1),N))

Argument Explanations:

• INDEX – In Excel, the INDEX function will help to return the value at a given position in a range or array.
• SMALL – This function will help to find the N^th smallest value from the given range of data. Read more on the SMALL function.
• IF – The IF function helps to return one value for a TRUE result, and another for a FALSE result.
• ROW – It is used to return the row number for reference. Read more on the ROW Function.

Example:

Let’s see how to find the N^th matching value in a range that meets the single condition.

• First, we will give the input values in ColumnB and Column C.

Input Ranges

• Here, we need to find the values based on the specified position.
• Enter the given formula to the formula bar section.

Enter the formula

• After that, Press CTRL + SHIFT + ENTER Keys.
• Finally, we will get the result in the selected cell.

Result

Bottom-Line:

From this tutorial, you can get some clarification on how to find the Nth matching value with single criteria in Excel. Make use of it. If you have any doubts , feel free to share it with us. Thank you!! To learn more, check out Geek Excel!!

Related Articles:

• Excel Formulas: INDEX and MATCH Descending Order ~ Easy Guide!!
• Excel Formulas to Find the Position of Nth Matching Value!!