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 **3****rd** and **2****nd** 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*

## Leave a Comment