Excel Formula to Find the Nth Occurrence Using SMALL and ROW

Retrieving specific data in Excel often frustrates analysts when standard lookup functions fail to locate duplicate entries, consistently stopping at the first match. While traditional lookup sources and standard index methods resolve basic queries, they lack the depth needed for complex, multi-layered data analysis.

Masterfully combining the SMALL and ROW functions solves this by granting users the ability to isolate any specific occurrence within a dataset. A key stipulation of this method is that older Excel versions require CSE (Ctrl+Shift+Enter) execution to correctly process the array. For example, this technique allows you to effortlessly extract the third transaction invoice for a specific vendor.

Below, we will break down the exact formula syntax, step-by-step logic, and execution methods required to implement this solution in your spreadsheets.

Excel Formula to Lookup Nth Occurrence with Small and Row

When working with data analysis in Microsoft Excel, retrieving information is one of the most common tasks. Standard lookup functions like VLOOKUP, HLOOKUP, and the classic INDEX and MATCH combination are incredibly powerful. However, they all share a fundamental limitation: they are designed to return only the first occurrence of a matching value.

What happens when you need to find the second, third, or Nth occurrence of a specific value? For instance, you might want to find the second purchase made by a specific customer, or the third transaction recorded in a specific region. To accomplish this in older or standard versions of Excel, you must combine several functions into an advanced array formula: INDEX, SMALL, IF, and ROW.

In this comprehensive guide, we will break down how this classic formula works step-by-step, explain the mechanics of each component, and look at a practical, real-world example.

The Core Formula Syntax

Before diving into the explanation, let's look at the generic structure of the formula. Because this is an array formula, if you are using Excel 2019 or earlier, you must enter it by pressing Ctrl + Shift + Enter instead of just Enter. This wraps the formula in curly braces { }.

{=INDEX(return_range, SMALL(IF(lookup_range = lookup_value, ROW(lookup_range) - ROW(first_row_of_lookup_range) + 1), N))}

Formula Components Break Down:

• return_range: The range of cells containing the actual data you want to retrieve.
• lookup_range: The range of cells where you are searching for your criteria.
• lookup_value: The specific criteria or value you want to match in the lookup_range.
• ROW(lookup_range) - ROW(first_row_of_lookup_range) + 1: This relative row generator calculates a clean list of sequential index numbers (1, 2, 3...) regardless of where your data table starts on the worksheet.
• N: The specific occurrence you want to retrieve (e.g., 2 for the second match, 3 for the third match).

How the Formula Works Under the Hood

To understand why this formula is so effective, we must look at how Excel processes each nested layer from the inside out.

Step 1: The Relative Row Generator

If your data table starts at row 5 and ends at row 15, simply using the ROW() function on your range would return the absolute row numbers: {5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15}. If we pass these numbers directly to INDEX, it will look for the 5th item of our range, which would actually be row 10 of the worksheet.

To fix this, we normalize the rows:

ROW(lookup_range) - ROW(first_row_of_lookup_range) + 1

If the first row of your lookup range is row 5, this evaluates to:

{5; 6; 7...} - 5 + 1 = {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}

This provides us with a sequential list of relative index positions within our range.

Step 2: Filtering with the IF Function

Next, the IF statement compares every cell in our lookup_range to our lookup_value. If a match is found, Excel keeps the relative row number generated in Step 1. If it does not match, Excel assigns a value of FALSE.

IF(lookup_range = lookup_value, relative_row_numbers)

This results in an array that looks something like this: {FALSE; 2; FALSE; FALSE; 5; FALSE; 7; FALSE...}. The numbers represent the relative positions where our matches exist.

Step 3: Extracting the Nth Match with SMALL

The SMALL function is designed to return the k-th smallest value in a data set. Its syntax is SMALL(array, k). Crucially, the SMALL function ignores logical values like FALSE.

If our array of matching positions is {FALSE; 2; FALSE; FALSE; 5; FALSE; 7} and we set our occurrence variable N to 2 (asking for the second smallest match), SMALL ignores all the FALSE values and looks only at the numbers: {2; 5; 7}. The second smallest number in this set is 5.

Step 4: Retrieving the Value with INDEX

Now that SMALL has successfully identified that the second match is located at relative index position 5, it hands this index number over to the INDEX function.

INDEX(return_range, 5)

Excel quickly navigates to the 5th item in your return_range and returns the correct result.

Step-by-Step Practical Example

Let's apply this formula to a practical scenario. Suppose we have the following sales directory table, spanning from cells A2 to C8:

Our objective is to find the 2nd sale made by "John" and display his Revenue.

• Return Range: C2:C8 (Revenue)
• Lookup Range: A2:A8 (Sales Rep)
• Lookup Value: "John"
• N (Occurrence): 2

The exact formula we write is:

=INDEX(C2:C8, SMALL(IF(A2:A8 = "John", ROW(A2:A8) - ROW(A2) + 1), 2))

Remember, if you are not using Microsoft 365, you must press Ctrl + Shift + Enter to input this formula.

How Excel Evaluates This Specific Example:

1. ROW(A2:A8) - ROW(A2) + 1 evaluates to:
   {2;3;4;5;6;7;8} - 2 + 1 which generates the array: {1; 2; 3; 4; 5; 6; 7}.
2. The IF function checks which cells in A2:A8 equal "John":
   {"John"; "Sarah"; "John"; "Mike"; "Sarah"; "John"; "Sarah"} = "John"
   This results in: {TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}.
3. The IF statement assigns the relative row numbers to the TRUE values:
   {1; FALSE; 3; FALSE; FALSE; 6; FALSE}.
4. The SMALL function processes this array for N = 2:
   SMALL({1; FALSE; 3; FALSE; FALSE; 6; FALSE}, 2) returns 3 (since 1 is the 1st smallest and 3 is the 2nd smallest).
5. Finally, the INDEX function retrieves the 3rd value in range C2:C8:
   INDEX(C2:C8, 3) which points directly to cell C4, returning $1,800.

Handling Errors Safely

If you ask Excel to look up an occurrence that doesn't exist-for example, looking up the 4th transaction for "John" when he only has 3 transactions-the SMALL function will fail and return a #NUM! error.

To ensure your spreadsheet layout remains clean and professional, wrap your formula inside an IFERROR function. This allows you to define a fallback message, such as "Not Found" or an empty string.

=IFERROR(INDEX(C2:C8, SMALL(IF(A2:A8 = "John", ROW(A2:A8) - ROW(A2) + 1), 4)), "No Match Found")

A Modern Alternative: The FILTER Function

If you are using modern versions of Excel (such as Microsoft 365 or Excel 2021 and newer), you can bypass the complex INDEX, SMALL, and ROW array formula entirely. Microsoft introduced the dynamic array function FILTER, which makes this task dramatically simpler.

Using FILTER along with INDEX, you can accomplish the exact same Nth occurrence lookup like this:

=INDEX(FILTER(C2:C8, A2:A8 = "John"), 2)

The nested FILTER(C2:C8, A2:A8 = "John") outputs an array of only John's revenues: {$1,200; $1,800; $2,200}. Then, the standard INDEX function simply grabs the 2nd item from that filtered list. It is cleaner, faster to calculate, and does not require complex array entries.

However, mastering the legacy SMALL and ROW method remains an essential skill. It ensures your spreadsheets are backward-compatible with legacy versions of Excel used across many corporate environments, and helps you master the concepts of array manipulation.