How to Look Up the Nth Instance of a Criterion in Excel Formula

Sometimes, you may need to find the nth occurrence of a value that meets a certain criterion in a range of data. For example, you may want to find the second product that a customer bought, or the third employee who completed a training course. In this article, we will show you how to use Excel formulas to look up the nth instance of a criterion in a simple and flexible way.

The basic idea behind looking up the nth instance of a criterion is to use a combination of functions that can return the row number of the matching value, and then use that row number to extract the desired information from another column. There are two main approaches to do this:

• Using a helper column. This method involves adding a column to the original data that contains a unique identifier for each row based on the criterion. Then, we can use a simple VLOOKUP or INDEX/MATCH formula to look up the nth value based on the identifier.
• Using an array formula. This method does not require modifying the original data, but uses a more complex formula that involves the SMALL and IF functions to filter the row numbers based on the criterion, and then use the INDEX function to return the nth value.

We will explain each method in detail and show some examples in the following sections.

Using a Helper Column

The helper column method is easier to understand and implement, but it requires adding an extra column to the data. The steps are as follows:

1. Insert a column before the column that contains the criterion. In this column, enter a formula that concatenates the criterion value with a sequential number based on the count of the criterion value. For example, if the criterion is in column A, the formula in the helper column B could be:

=A2&COUNTIF($A$2:$A2,A2)

This formula will create a unique identifier for each row, such as “A1”, “A2”, “B1”, “B2”, etc.

2. In another cell, enter the criterion value and the nth instance that you want to look up. For example, if you want to find the second product that customer A bought, you can enter “A” in cell E2 and “2” in cell F2.
3. In the cell where you want to display the result, enter a VLOOKUP or INDEX/MATCH formula that uses the concatenation of the criterion value and the nth instance as the lookup value, and the column that contains the desired information as the return column. For example, if the product name is in column C, the formula in cell G2 could be:

=VLOOKUP(E2&"-"&F2,$B$2:$C$10,2,0)

or

=INDEX($C$2:$C$10,MATCH(E2&"-"&F2,$B$2:$B$10,0))

Both formulas will return the same result, which is the second product that customer A bought.

Using an Array Formula

The array formula method is more advanced and does not require adding a helper column, but it uses a longer and more complex formula that needs to be entered with Ctrl + Shift + Enter. The steps are as follows:

1. In a cell, enter the criterion value and the nth instance that you want to look up. For example, if you want to find the third employee who completed the training course “Excel”, you can enter “Excel” in cell H2 and “3” in cell I2.
2. In the cell where you want to display the result, enter an array formula that uses the SMALL and IF functions to filter the row numbers based on the criterion, and then use the INDEX function to return the value in the desired column. For example, if the employee name is in column A and the training course is in column B, the formula in cell J2 could be:

=INDEX($A$2:$A$10,SMALL(IF($B$2:$B$10=H2,ROW($B$2:$B$10)-MIN(ROW($B$2:$B$10))+1),I2))

This formula needs to be entered with Ctrl + Shift + Enter to work as an array formula. It will return the third employee who completed the training course “Excel”.

Example

To illustrate the two methods, let’s use the following example data:

We want to find the name of the nth employee who completed a given training course. Here is how the worksheet would look like using the helper column method:

The formula in the helper column B is:

=B2&COUNTIF($B$2:$B2,B2)

The formula in the result column F is:

=VLOOKUP(D2&"-"&E2,$B$2:$C$10,2,0)

Here is how the worksheet would look like using the array formula method:

The formula in the result column E is:

=INDEX($A$2:$A$10,SMALL(IF($B$2:$B$10=C2,ROW($B$2:$B$10)-MIN(ROW($B$2:$B$10))+1),D2))

This formula needs to be entered with Ctrl + Shift + Enter.