< Go Back

Get nth match

To get nth match in excel, you can use a SMALL function formula that allows you to find out the matching values in a given data set.

Formula

The generic formula to get nth match is given below:

=SMALL(IF(logical,ROW(list)-MIN(ROW(list))+1),n)

It is an array formula and must be entered with control + shift + enter.

Explanation

This formula works due to a SMALL function that provides the nth smallest value in a values list (corresponding row numbers). Using IF statement, row numbers are ‘filtered’ thereby applying logic for the match. ‘List’ is the named range that contains all the values. IF match up the values in the list to the value of any cell, thus make given below array;

{TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE}

Following code creates a set of comparative row numbers or “value if true“.

ROW(list)-MIN(ROW(list))+1

The logical test provides a selection of results and IF function is used to filter the results. Row numbers that are matched are returned as TRUE, while others return FALSE. The SMALL function will then return the nth smallest value in the list.

Example

To get nth match in this example, we have used Small Function formula to find out the 3rd and 2nd matching values for apple and orange from the given list. See Screenshots.

The formula in G5 is:

=SMALL(IF(list=E5,ROW(list)-MIN(ROW(list))+1),F5)

Here,

List is a named range for B5:B11

Figure 1 Get nth match Example of Apple’s 3rd matching value

The formula in G6 is:

=SMALL(IF(list=E5,ROW(list)-MIN(ROW(list))+1),F5)

Here,

List is a named range for B5:B11

Figure 2 Get nth match Example of Orange’s 2nd matching value

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar