How to Find the Nth Occurrence of a Value in an Excel Column

Locating a specific repeating data point in Excel can be highly frustrating. While standard lookup methods like VLOOKUP logically bridge this gap for basic queries, they fail by default when forced to look beyond the first match. Mastering advanced formulas grants you the power of precise, automated extraction from complex datasets. However, as an educational stipulation, these solutions require careful handling of array formulas or modern dynamic functions to work correctly. For example, identifying the 3rd transaction for "Client A" demands exact structural alignment. Below, we outline the precise formulas and steps to find any Nth occurrence effortlessly.

Excel is an incredibly powerful tool for data analysis, but it has some historical limitations that can frustrate users. One of the most common challenges is performing lookups for repeating data. Standard lookup functions like VLOOKUP, HLOOKUP, and MATCH are designed to find and return only the first occurrence of a target value. If you need to find the second, third, or Nth occurrence of a value in a column, these standard formulas will fall short on their own.

Fortunately, there are several ways to overcome this limitation. Depending on your version of Excel, you can use modern dynamic array functions, classic array formulas, or a simple helper column approach. In this comprehensive guide, we will explore the best methods to find the Nth occurrence of a value in an Excel column.

Sample Data Setup

To make these formulas easy to follow, we will use the following sample sales transaction table. Our goal will be to find the 2nd or 3rd order amount for a specific customer, such as "John".

Method 1: The Modern Way (Excel 365 & Excel 2021+)

If you are using Microsoft 365 or Excel 2021, you have access to dynamic arrays. This makes finding the Nth occurrence incredibly straightforward using a combination of the INDEX and FILTER functions.

The Formula Syntax

=INDEX(FILTER(return_range, lookup_range = lookup_value), N)

How It Works

• FILTER(return_range, lookup_range = lookup_value): This function filters your data and returns an array containing only the values that match your criteria. For example, filtering for "John" returns an array of his transaction amounts: {120, 150, 310}.
• INDEX(..., N): The INDEX function then extracts the item at position N from that filtered list. If N is 2, it returns the 2nd item.

Step-by-Step Example

To find the 2nd purchase amount for John using our sample table:

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

Result: $150

Pro Tip: To prevent errors if the Nth occurrence does not exist (for example, looking for the 4th purchase of John), wrap the formula in IFERROR:

=IFERROR(INDEX(FILTER(B2:B7, A2:A7 = "John"), 4), "Not Found")

Method 2: The Classic Array Formula (INDEX + SMALL + IF)

If you are working in an older version of Excel (such as Excel 2019, 2016, or 2013), you won't have access to the FILTER function. Instead, you must use a traditional array formula combining INDEX, SMALL, and IF.

The Formula Syntax

=INDEX(return_range, SMALL(IF(lookup_range = lookup_value, ROW(lookup_range) - ROW(first_cell_in_lookup_range) + 1), N))

Note: Because this is an array formula in older Excel versions, you must press Ctrl + Shift + Enter instead of just Enter to commit the formula. Doing so will wrap the formula in curly braces { } automatically.

How It Works

1. IF(lookup_range = lookup_value, ...): Checks each cell in the lookup range. If it matches your target value, it calculates its relative row position; otherwise, it returns FALSE.
2. ROW(lookup_range) - ROW(first_cell) + 1: Generates a relative row index list (1, 2, 3, etc.) regardless of where your data table starts on the sheet.
3. SMALL(..., N): From the array of matching row numbers, SMALL selects the Nth smallest number (which corresponds directly to the Nth match).
4. INDEX(return_range, ...): Finally, INDEX retrieves the value from the matching position in your target range.

Step-by-Step Example

To find the 2nd purchase amount for John in our sample range A2:B7:

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

Remember to press Ctrl + Shift + Enter. The result returned will be $150.

Method 3: The AGGREGATE Formula (Excel 2010+ / No Ctrl+Shift+Enter)

If you are using Excel 2010 or newer and want to avoid the complexities of pressing Ctrl+Shift+Enter, you can use the highly versatile AGGREGATE function. It can perform array-like behavior without requiring special keyboard shortcuts.

The Formula Syntax

=INDEX(return_range, AGGREGATE(15, 6, (ROW(lookup_range) - ROW(first_cell) + 1) / (lookup_range = lookup_value), N))

How It Works

• 15: Instructs AGGREGATE to behave like the SMALL function.
• 6: Tells the function to ignore error values.
• (ROW(lookup_range) - ROW(first_cell) + 1) / (lookup_range = lookup_value): This is the clever trick. For matching rows, the expression divides the row index by TRUE (which Excel treats as 1), returning the row index. For non-matching rows, it divides by FALSE (treated as 0), producing a #DIV/0! error. Because we set the option code to 6, AGGREGATE ignores all these division errors, leaving only the row numbers of matches.
• The function then picks the Nth smallest row number, and INDEX displays the corresponding value.

Step-by-Step Example

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

Result: $150 (Press Enter normally).

Method 4: The Helper Column Approach (Simple & Fast)

If you prefer to avoid complex, nested formulas altogether, you can use a helper column. This is often the best approach for large datasets because it is easy to audit and doesn't slow down Excel like heavy array calculations can.

Step 1: Create a Unique Identifier Key

Insert a new column to the left of your data (or anywhere convenient) to count occurrences dynamically using COUNTIF with a mixed absolute/relative reference.

In a new Column A, enter this formula in row 2 and drag it down:

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

This creates a progressive counter for each customer. Your table will now look like this:

Step 2: Use a Standard VLOOKUP

Now that every occurrence has a unique name (like "John-2" for his second purchase), you can use a standard VLOOKUP or INDEX/MATCH to find the value:

=VLOOKUP("John-2", A2:C7, 3, FALSE)

Result: $150

Summary: Which Method Should You Choose?

To help you decide which formula is best for your current spreadsheet, refer to this quick breakdown:

• Use Method 1 (INDEX & FILTER) if you and your team are using modern versions of Excel (Microsoft 365 or Office 2021+). It is the most readable and clean formula.
• Use Method 2 (INDEX, SMALL, IF) if you must maintain backward compatibility with older XLS files (pre-2010) and don't mind utilizing Ctrl+Shift+Enter.
• Use Method 3 (AGGREGATE) if you want compatibility back to Excel 2010 but prefer a standard formula that doesn't require complex array entry.
• Use Method 4 (Helper Column) if your workbook is exceptionally large and slow, or if you want a visual way to track and debug occurrence numbers on your sheet.