Excel INDEX MATCH Formula for Referencing the Nth Occurrence

Locating the first match in Excel is straightforward, but retrieving the second, third, or Nth occurrence of a data point often leads to frustrating manual workarounds. While standard VLOOKUP or basic INDEX MATCH functions are the industry-standard tools for single lookups, they fall short when handling duplicate records.

Mastering the Nth occurrence lookup grants you absolute control over complex datasets without requiring you to restructure your source tables. As a stipulation, keep in mind that these advanced formulas leverage array processing (utilizing SMALL and ROW), which can slow down performance in massive workbooks.

For example, isolating the second transaction for "Client A" in a ledger requires this exact multi-layered approach. Below, we outline the precise formula syntax to deploy this solution.

In the world of data analysis, Microsoft Excel is an indispensable tool. Among its most powerful lookup features are the INDEX and MATCH functions. When combined, they offer a highly flexible alternative to the traditional VLOOKUP. However, standard lookup functions share a common limitation: they are designed to return only the first occurrence of a matching value.

But what happens when your dataset contains duplicate entries, and you need to retrieve the second, third, or Nth occurrence? For instance, if you have a sales log and want to find the amount of John's 3rd transaction, a standard INDEX MATCH will fail, consistently returning his 1st transaction instead.

To overcome this limitation, you need to construct a formula that can identify and isolate multiple matching entries. In this comprehensive guide, we will explore three highly effective methods to reference the Nth occurrence using Excel: the classic INDEX-SMALL-IF Array Formula, the non-array AGGREGATE alternative, and the modern Excel 365 FILTER function.

Understanding the Sample Dataset

To illustrate these techniques, let us assume we have a sales table in the range A2:C7, representing transactional data. Our goal is to look up the 2nd transaction amount for the employee named "John".

If we search for "John", a standard =INDEX(C2:C7, MATCH("John", A2:A7, 0)) will return $200 (the 1st occurrence). To extract the 2nd occurrence ($450), we need a more advanced approach.

Method 1: The Classic INDEX, SMALL, and IF Array Formula

For Excel versions older than Office 365 (such as Excel 2013, 2016, or 2019), the standard solution is an array formula that combines INDEX, SMALL, IF, and ROW.

The Formula Syntax

{=INDEX(Return_Range, SMALL(IF(Lookup_Range = Lookup_Value, ROW(Lookup_Range) - ROW(First_Cell_in_Lookup_Range) + 1), N))}

Note: This is an array formula. You must press Ctrl + Shift + Enter (instead of just Enter) to execute it in older Excel versions. This wraps the formula in curly brackets {} automatically.

Applying the Formula to Our Example

To find the 2nd sale amount for "John" (where N = 2), enter the following formula in your cell:

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

How It Works: Step-by-Step Breakdown

• Step 1: The logical test with IF: The statement A2:A7="John" checks each cell in the lookup range. It generates an array of Boolean values: {FALSE; TRUE; FALSE; FALSE; TRUE; TRUE}.
• Step 2: Generating row indexes: The expression ROW(A2:A7) - ROW(A2) + 1 creates a relative sequence of row numbers starting from 1. It evaluates to: {1; 2; 3; 4; 5; 6}. This ensures the formula works perfectly regardless of where your table resides on the worksheet.
• Step 3: Filtering the row indexes: The IF function combines the Boolean array and the relative row numbers. If a value is TRUE, it retains the relative row number; if FALSE, it returns FALSE. The output array is: {FALSE; 2; FALSE; FALSE; 5; 6}. Note that "John" matches rows 2, 5, and 6.
• Step 4: Picking the Nth item with SMALL: The SMALL(..., N) function extracts the Nth smallest number from our filtered array, completely ignoring logical FALSE values. Since we requested the 2nd occurrence (N = 2), SMALL({FALSE; 2; FALSE; FALSE; 5; 6}, 2) returns 5.
• Step 5: Extracting the value with INDEX: Finally, INDEX(C2:C7, 5) references the 5th element in the sales column, which returns $450.

Method 2: The AGGREGATE Function (Non-Array Alternative)

If you are using Excel 2010 or newer but want to avoid the complexity of pressing Ctrl + Shift + Enter, you can use the incredibly versatile AGGREGATE function. This function can perform array-like operations natively without requiring array entry.

The Formula Syntax

=INDEX(Return_Range, AGGREGATE(15, 6, (ROW(Lookup_Range) - ROW(First_Cell) + 1) / (Lookup_Range = Lookup_Value), N))

Applying the Formula to Our Example

Using our sample dataset to find the 2nd sale for "John", the formula is:

=INDEX(C2:C7, AGGREGATE(15, 6, (ROW(A2:A7) - ROW(A2) + 1) / (A2:A7="John"), 2))

How It Works: Step-by-Step Breakdown

• The First Argument (15): This instructs AGGREGATE to act as the SMALL function.
• The Second Argument (6): This option instructs the function to ignore all error values. This is critical for the logic to function.
• The Array Division: The formula divides the relative row index array by the logical comparison array: (ROW(A2:A7) - ROW(A2) + 1) / (A2:A7="John").
• In Excel, dividing by a logical TRUE (which equals 1) returns the row number. Dividing by FALSE (which equals 0) yields a #DIV/0! error.
• This evaluates to: {1/FALSE; 2/TRUE; 3/FALSE; 4/FALSE; 5/TRUE; 6/TRUE}, which simplifies to {#DIV/0!; 2; #DIV/0!; #DIV/0!; 5; 6}.
• Because we specified option 6, AGGREGATE ignores all #DIV/0! errors, leaving only {2; 5; 6}.
• AGGREGATE then extracts the 2nd smallest value (N = 2), which is 5.
• Finally, INDEX(C2:C7, 5) retrieves $450.

Method 3: The Modern Excel 365 FILTER Function (Recommended)

If you are using Microsoft 365 or Excel 2021/Web, you have access to Dynamic Arrays. This completely eliminates the need for complex mathematical workarounds. You can simply combine INDEX with the new FILTER function.

The Formula Syntax

=INDEX(FILTER(Return_Range, Lookup_Range = Lookup_Value), N)

Applying the Formula to Our Example

To find the 2nd sale for "John" using Excel 365:

=INDEX(FILTER(C2:C7, A2:A7="John"), 2)

How It Works

The FILTER(C2:C7, A2:A7="John") function filters the sales column based on our criteria. It returns a dynamic array of matching entries: {$200; $450; $300}. From there, the INDEX function simply selects the 2nd element in this new, clean list, returning $450.

This is by far the cleanest, most readable, and fastest-calculating method available.

Handling Errors Safely

If you request an Nth occurrence that does not exist (for example, asking for the 4th occurrence of "John" when there are only 3), your formulas will return a #NUM! or #REF! error. To keep your dashboards clean, always wrap your lookup formulas in an IFERROR function.

Here is how to apply it to the Excel 365 formula:

=IFERROR(INDEX(FILTER(C2:C7, A2:A7="John"), 4), "No record found")

Similarly, for the AGGREGATE method:

=IFERROR(INDEX(C2:C7, AGGREGATE(15, 6, (ROW(A2:A7) - ROW(A2) + 1) / (A2:A7="John"), 4)), "No record found")

Conclusion: Which Method Should You Choose?

Choosing the right formula depends heavily on your Excel environment and structural requirements:

• Excel 365/2021: Always use the INDEX + FILTER approach. It is elegant, readable, and highly efficient.
• Excel 2010 to 2019 (Without Array Entry): Use the AGGREGATE method to avoid the potential user errors associated with legacy Ctrl+Shift+Enter formulas.
• Legacy Excel compatibility: Use the classic INDEX-SMALL-IF array formula if you must maintain compatibility with very old .xls files.

By mastering these dynamic methods, you can construct robust dashboards and advanced data models capable of handling duplicate keys and multi-tiered lookups with ease.