Finding the Nth Occurrence in Excel Using INDEX and SMALL Formulas

Finding the first match in Excel is straightforward, but isolating the exact nth occurrence of a data point often frustrates data analysts. While standard tools like VLOOKUP or XLOOKUP handle basic lookups efficiently, they cannot natively query duplicate records. Combining INDEX and SMALL grants you the ultimate precision to extract any specific repetition dynamically.

Stipulation: This method requires array formula execution (Ctrl+Shift+Enter in legacy Excel) and can impact performance on massive datasets. However, it is invaluable for concrete tasks, such as extracting a client's third transaction or a vendor's second-lowest bid. Below, we will dissect the exact formula syntax step-by-step.

Excel users quickly learn that standard lookup functions like VLOOKUP, HLOOKUP, and even the classic INDEX/MATCH combination share a common limitation: they are designed to return only the first matching occurrence of a search term. If your dataset contains duplicate entries and you need to retrieve the second, third, or Nth occurrence, standard formulas fall short.

Fortunately, by combining the analytical power of INDEX, SMALL, IF, and ROW, you can build a dynamic formula capable of pulling any specific occurrence from a list. In this comprehensive guide, we will break down how this classic array formula works, construct a step-by-step example, troubleshoot common pitfalls, and explore modern alternatives for newer Excel versions.

The Blueprint Formula

Before diving into the mechanics, let us look at the standard syntax of the formula. This is traditionally entered as an array formula (using Ctrl + Shift + Enter in legacy Excel versions):

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

At first glance, this nested formula looks intimidating. However, it operates on a logical system of filtering, sorting, and indexing. To understand it, we must break down each function from the inside out.

How the Formula Works: Step-by-Step

To demystify this formula, let us analyze its components using a practical scenario. Imagine we have a sales database structured like this:

Our goal is to find the 2nd occurrence of a purchase by Alice. The expected result is "Bananas".

Step 1: Identifying Matches with IF

The core logic begins with the IF statement:

IF(A$2:A$6 = "Alice", ...)

This evaluates every cell in the customer range to see if it equals "Alice". It returns an array of boolean values:

{TRUE; FALSE; TRUE; FALSE; TRUE}

Step 2: Generating Relative Row Numbers

We need to know the physical positions of these matches within our target range. We do this using the ROW function:

ROW(A$2:A$6) - ROW(A$2) + 1

Why not just use ROW(A$2:A$6)? If your data starts on row 10, ROW() will return indices starting at 10. By subtracting the first row's index (ROW(A$2), which is 2) and adding 1, we normalize the array to always start at 1, regardless of where the table sits on the sheet. This returns:

{1; 2; 3; 4; 5}

Step 3: Filtering the Row Indices

Next, the IF statement merges these two steps:

IF({TRUE; FALSE; TRUE; FALSE; TRUE}, {1; 2; 3; 4; 5}, "")

If the condition is TRUE, Excel returns the relative row number. If FALSE, it returns an empty string (""). This yields:

{1; ""; 3; ""; 5}

We now have an array containing only the relative positions where "Alice" appears.

Step 4: Selecting the Nth Occurrence with SMALL

Now, we want the 2nd occurrence. This is where the SMALL function shines. SMALL(array, k) returns the k-th smallest value in a data set, completely ignoring text and empty strings.

SMALL({1; ""; 3; ""; 5}, 2)

The smallest value is 1 (the 1st occurrence). The second smallest value is 3 (the 2nd occurrence). SMALL successfully extracts the number 3.

Step 5: Retrieving the Value with INDEX

Finally, we feed this relative index back into the INDEX function to extract our actual target data from column B (B$2:B$6):

INDEX(B$2:B$6, 3)

The third item in our product range is "Bananas". The search is complete!

Executing the Formula in Different Excel Versions

Depending on which version of Excel you are running, how you input this formula matters immensely.

Excel 2019 and Older (Legacy Excel)

In older versions of Excel, this is a traditional array formula. After typing or pasting the formula, you must not simply press Enter. Instead, press Ctrl + Shift + Enter (CSE). Excel will automatically wrap your formula in curly braces {}. If you edit the formula later, you must press CSE again.

Excel 365 and Excel 2021 (Modern Excel)

Modern Excel engines support Dynamic Arrays. This means Excel automatically recognizes array operations. You can simply paste the formula and press Enter. No special key strokes are required.

Handling Errors with IFERROR

What happens if you search for the 4th occurrence of "Alice", but she only purchased items three times? The SMALL function will return a #NUM! error because there is no 4th smallest number in your filtered array.

To keep your spreadsheet clean and professional, wrap the entire formula in an IFERROR function:

=IFERROR(INDEX(B$2:B$6, SMALL(IF(A$2:A$6="Alice", ROW(A$2:A$6)-ROW(A$2)+1, ""), 4)), "No Match Found")

Now, instead of an ugly error code, Excel will return "No Match Found" or a blank space depending on your preference.

Making the Occurrence Dynamic

Instead of hardcoding the occurrence number (like 2 or 3) inside your formula, you should reference a cell. For example, if you list your occurrence number in cell D2, your formula becomes highly interactive:

=INDEX(B$2:B$6, SMALL(IF(A$2:A$6="Alice", ROW(A$2:A$6)-ROW(A$2)+1, ""), D2))

Now, changing the value in D2 from 1 to 2 or 3 instantly updates the formula results without modifying the syntax.

The Modern Alternative: The FILTER Function

If you are lucky enough to be using Excel 365 or Excel 2021, Microsoft has provided a much cleaner, easier-to-read function that eliminates the need for the complex SMALL/IF/ROW nested logic: the FILTER function.

To get the Nth occurrence using modern Excel, you can use INDEX combined with FILTER:

=INDEX(FILTER(B2:B6, A2:A6 = "Alice"), 2)

How the Modern Version Works:

• FILTER(B2:B6, A2:A6 = "Alice") extracts all products associated with Alice, returning an array: {"Apples"; "Bananas"; "Cherries"}.
• INDEX(..., 2) grabs the 2nd item from that newly filtered array, which is "Bananas".

This modern variation is faster to compute, much easier to write, and significantly easier for other users to audit.

Summary of Best Practices

• Lock Ranges: Always use absolute references (like $A$2:$A$6) for your lookup and return ranges if you plan to drag the formula down to prevent Excel from shifting your ranges.
• Always Normalize Rows: Never skip the - ROW(first_row) + 1 step when using the legacy version; otherwise, inserting rows above your table in the future will break your formulas.
• Upgrade When Possible: If your team uses Excel 365, transition to the INDEX/FILTER approach for better workbook performance and readability.