How to Find the Nth Occurrence in Excel Using INDEX and SMALL

Locating data beyond the first match in Excel is a common struggle, as standard search tools fail when duplicate records exist. While standard lookup functions serve as the traditional funding sources of data retrieval, they lack multi-occurrence capability. The INDEX and SMALL combination solves this, granting users the unique ability to target any specific nth occurrence. Crucially, as a stipulation, legacy Excel versions require executing this as an array formula (Ctrl+Shift+Enter). For instance, retrieving the second transaction for "Client A" demonstrates its practical utility. Below, we break down how to construct this formula step-by-step.

Excel users frequently rely on lookup functions like VLOOKUP, HLOOKUP, or the standard INDEX and MATCH combination to retrieve data from a table. While these tools are incredibly powerful, they share a common limitation: they are designed to return only the first occurrence of a matching value. If your dataset contains duplicate entries and you need to find the second, third, or Nth occurrence of a specific value, these standard functions fall short.

Fortunately, by combining the analytical power of the INDEX, SMALL, IF, and ROW functions, you can build a robust array formula that extracts any specific occurrence of a matching record. In this comprehensive guide, we will explore how this formula works, break down its components, walk through a step-by-step practical example, and look at modern alternatives for users of newer Excel versions.

The Core Formula Syntax

Before diving into how the formula works, let us look at its standard structure. In older versions of Excel (Excel 2019 and earlier), this is an array formula, meaning it must be entered using the Ctrl + Shift + Enter keystroke. In Excel 365 and Excel 2021, thanks to the Dynamic Arrays engine, it can be entered normally.

{=INDEX(return_range, SMALL(IF(criteria_range = criteria, ROW(criteria_range) - ROW(first_row_of_range) + 1), N))}

Where:

• return_range is the column or range containing the actual value you want to retrieve.
• criteria_range is the column or range where Excel will search for your match.
• criteria is the specific value you are searching for.
• first_row_of_range is the first cell reference in your criteria range (used to calculate a relative row number).
• N is the occurrence you want to retrieve (e.g., 1 for first, 2 for second, 3 for third).

How the Formula Works: A Detailed Breakdown

To understand why this formula is so effective, we must deconstruct it from the inside out. Let us examine how the nested functions work together to identify, filter, and extract your data.

Step 1: The Logical Filter (IF and ROW)

The core engine of this formula is the logical test inside the IF statement:

IF(criteria_range = criteria, ROW(criteria_range) - ROW(first_row_of_range) + 1)

The expression criteria_range = criteria creates an array of TRUE and FALSE values. For instance, if your criteria range has five items and only the second and fourth items match your criteria, Excel generates: {FALSE; TRUE; FALSE; TRUE; FALSE}.

Next, we calculate the relative row numbers using: ROW(criteria_range) - ROW(first_row_of_range) + 1. If your range spans from row 5 to row 9, this expression evaluates to: {5;6;7;8;9} - 5 + 1, which simplifies to the relative index array: {1; 2; 3; 4; 5}.

When the IF function processes these two arrays, it replaces the TRUE values with their corresponding relative row numbers, and the FALSE values with FALSE:

{FALSE; 2; FALSE; 4; FALSE}

Step 2: Selecting the Nth Item (SMALL)

Once the IF function has isolated the relative row positions of our matching entries, the SMALL function takes over. The syntax for SMALL is:

SMALL(array, k)

Where k represents the "k-th" smallest value in the dataset. Since the logical FALSE values are ignored by SMALL, the function only evaluates our array of row numbers: {2; 4}.

• If you set N (k) to 1, SMALL returns 2 (the first matching row position).
• If you set N (k) to 2, SMALL returns 4 (the second matching row position).

Step 3: Retrieving the Value (INDEX)

Finally, the INDEX function receives the relative row number returned by SMALL. If SMALL returned 4, the INDEX function extracts the value located at the 4th position of the return_range.

A Practical Step-by-Step Example

Let us put this formula into practice. Suppose you manage a customer support department and have a log of support tickets. The same customer can submit multiple tickets, and you want to locate the 2nd ticket ID submitted by "Alice".

Consider the following sample dataset in Excel (cells A1 to C7):

We want to find the 2nd occurrence of "Alice" and retrieve her Ticket ID.

• Return Range: B2:B7
• Criteria Range: A2:A7
• Criteria: "Alice" (or a cell reference like E2 containing "Alice")
• N: 2 (or a cell reference like F2 containing 2)

Our complete formula is:

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

Note for Legacy Excel Users: Paste the formula into your cell, then press Ctrl + Shift + Enter instead of just hitting Enter. Excel will wrap your formula in curly braces {...} indicating that it is operating as an array formula.

How this formula resolves step-by-step for our data:

1. The logical test checks each name in A2:A7 against "Alice":
   {"Bob";"Alice";"Charlie";"Alice";"Bob";"Alice"} = "Alice"
   This evaluates to: {FALSE; TRUE; FALSE; TRUE; FALSE; TRUE}.
2. The relative row calculations run:
   ROW(A2:A7) - ROW(A2) + 1 evaluates to {1; 2; 3; 4; 5; 6}.
3. The IF statement combines the logical outcomes with the relative row indices:
   IF({FALSE; TRUE; FALSE; TRUE; FALSE; TRUE}, {1; 2; 3; 4; 5; 6})
   This gives us: {FALSE; 2; FALSE; 4; FALSE; 6}.
4. The SMALL function pulls the 2nd smallest positive integer (since N = 2) from our list:
   SMALL({FALSE; 2; FALSE; 4; FALSE; 6}, 2) returns 4.
5. The INDEX function retrieves the 4th item from our return range (B2:B7):
   The 4th item in {"TK-101"; "TK-102"; "TK-103"; "TK-104"; "TK-105"; "TK-106"} is "TK-104".

Handling Errors with IFERROR

What happens if you look for the 4th ticket submitted by Alice, but she has only submitted three? The SMALL function will fail to find a 4th value and will return a #NUM! error. To make your dashboard or reports user-friendly, you should always wrap your formula in an IFERROR statement. This ensures your workbook returns a clean blank cell or a custom message instead of an ugly error message.

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

The Modern Alternative: FILTER Function (Excel 365 & 2021)

If you are lucky enough to be using Microsoft 365 or Excel 2021, you do not need to construct this complex matrix of nested formulas. Microsoft introduced the FILTER function, which dynamically filters arrays based on criteria you define. We can use FILTER in combination with a simple INDEX to pull our Nth record easily.

The simplified modern equivalent for finding the 2nd ticket for "Alice" is:

=INDEX(FILTER(B2:B7, A2:A7 = "Alice"), 2)

This works by using the FILTER function to generate a smaller, dynamic list of only Alice's ticket IDs: {"TK-102"; "TK-104"; "TK-106"}. The INDEX function then simply pulls the 2nd item from that array, returning TK-104. This is vastly easier to write, troubleshoot, and maintain!

Conclusion

While modern Excel features like dynamic arrays and the FILTER function are rapidly streamlining data analysis, knowing the classic INDEX, SMALL, IF, and ROW formula remains a vital skill. Many organizations still use legacy versions of Excel, and understanding this formula gives you a deeper comprehension of how Excel processes arrays behind the scenes.

By keeping this formula in your analytical toolkit, you can fearlessly design spreadsheets that extract exactly what you need, regardless of how many duplicate rows stand in your way.