Excel Formula to Index the Nth Occurrence Based on Specific Criteria

Locating a specific, non-initial record in Excel often frustrates data analysts when standard lookup sources, like VLOOKUP, restrict you to the first match. When tracking complex datasets like capital allocations or funding sources, pinpointing a specific transaction sequence is critical.

Utilizing a combined INDEX and SMALL array formula grants you the exact precision needed to isolate any recurring item. To ensure accuracy, note the stipulation: this formula requires array activation (Ctrl+Shift+Enter) to correctly evaluate the logical conditions. For example, easily retrieving the 3rd "Federal Grant" disbursement becomes straightforward. Below, we will break down the formula structure step-by-step.

Standard lookup functions in Excel, such as VLOOKUP, HLOOKUP, and the classic INDEX & MATCH combination, are designed with a singular focus: to find and return the first matching occurrence of a target value. While this is ideal for scanning unique databases like employee IDs or product SKUs, it falls short when working with transactional datasets, ledger books, or registry logs where multiple records exist for the same entity.

If you need to find the 2nd, 3rd, or Nth occurrence of a value based on a specific condition, you must look beyond basic lookup formulas. Depending on your version of Excel, you can solve this challenge using the modern, dynamic FILTER function, a robust non-array AGGREGATE formula, or a classic legacy array formula using INDEX, SMALL, and IF. This comprehensive guide covers all three approaches, complete with step-by-step breakdowns and advanced multi-condition scenarios.

Our Sample Dataset

To demonstrate these formulas in action, we will refer to the following sample transaction table. Our objective in the examples below will be to find the 2nd sale amount generated by the sales representative named "Sarah".

Looking closely at the data, Sarah has three sales: $850 (1st occurrence, row 3), $950 (2nd occurrence, row 5), and $1,100 (3rd occurrence, row 6). Our formulas should successfully isolate the 2nd occurrence and return $950.

Method 1: The Modern Way – Using FILTER and INDEX (Excel 365 & 2021)

If you are using Microsoft 365 or Excel 2021 and newer, you have access to dynamic arrays. This makes the task incredibly straightforward. By combining the FILTER function (which extracts only the rows matching your condition) with the INDEX function (which selects the specific row by its position), you can build an elegant, easy-to-read formula.

The Generic Formula

=INDEX(FILTER(return_range, criteria_range = criteria), N)

Applying the Formula

To extract the 2nd (N = 2) sale amount for "Sarah" from our sample table, write the following formula:

=INDEX(FILTER(C2:C7, B2:B7 = "Sarah"), 2)

How It Works

1. FILTER(C2:C7, B2:B7 = "Sarah"): This part scans range B2:B7 for the text "Sarah". It filters the target range C2:C7 down to only Sarah's sales, returning a dynamic array of matching values: {850; 950; 1100}.
2. INDEX(..., 2): The INDEX function then points to the 2nd position in that filtered array, which is $950.

This method is highly recommended because it is computationally efficient, easy to audit, and does not require complex array key combinations.

Method 2: The Classic Array Formula – INDEX, SMALL, and IF (Excel 2019 and Older)

If you are working on a legacy version of Excel (Excel 2019, 2016, 2013, or earlier), the FILTER function is not available. To achieve the same result, you must construct an array formula using a combination of INDEX, SMALL, IF, and ROW.

Note: Because this is a traditional array formula, you must press Ctrl + Shift + Enter after typing it in Excel 2019 or older. Doing so wraps the formula in curly braces { } automatically.

The Generic Formula

=INDEX(return_range, SMALL(IF(criteria_range = criteria, ROW(criteria_range) - MIN(ROW(criteria_range)) + 1), N))

Applying the Formula

To find Sarah's 2nd sale using this method, enter the following formula and press Ctrl + Shift + Enter:

=INDEX(C2:C7, SMALL(IF(B2:B7 = "Sarah", ROW(B2:B7) - ROW(B2) + 1), 2))

How It Works

This formula functions by dynamically creating a list of relative row indexes for matching entries, choosing the Nth smallest index, and returning the value from that position.

• Relative Row Generation: ROW(B2:B7) - ROW(B2) + 1 generates an array of relative row numbers within our selection: {1; 2; 3; 4; 5; 6}.
• Logical Filtering (IF): The IF statement checks if each cell in B2:B7 equals "Sarah". If true, it returns the relative row number; if false, it returns FALSE. This yields the array: {FALSE; 2; FALSE; 4; 5; FALSE} (representing Sarah's positions at rows 2, 4, and 5 relative to the start).
• Finding the Nth Occurrence (SMALL): The SMALL function finds the Nth smallest number in that filtered list. Since we asked for the 2nd (N = 2) smallest number, it ignores FALSE values and identifies 4 as the second lowest relative row index.
• Returning the Value (INDEX): Finally, INDEX(C2:C7, 4) retrieves the value from the 4th position of our target range, which is $950.

Method 3: The Robust AGGREGATE Alternative (Excel 2010+)

If you want to support older versions of Excel but prefer to avoid the tricky "Ctrl + Shift + Enter" keypress requirement, you can use the AGGREGATE function introduced in Excel 2010. AGGREGATE is highly versatile because it can perform array calculations natively and ignore errors generated during calculations.

The Generic Formula

=INDEX(return_range, AGGREGATE(15, 6, (ROW(criteria_range) - ROW(first_cell_of_criteria_range) + 1) / (criteria_range = criteria), N))

Applying the Formula

To find the 2nd transaction value for Sarah, enter this formula normally (just press Enter):

=INDEX(C2:C7, AGGREGATE(15, 6, (ROW(B2:B7) - ROW(B2) + 1) / (B2:B7 = "Sarah"), 2))

How It Works

• 15 and 6 Arguments: 15 tells AGGREGATE to behave like the SMALL function (finds Nth smallest value). 6 instructs it to ignore any errors generated during calculation.
• The Division Magic: The expression (ROW(B2:B7) - ROW(B2) + 1) / (B2:B7 = "Sarah") is evaluated. The array of relative row numbers {1; 2; 3; 4; 5; 6} is divided by an array of Boolean values {FALSE; TRUE; FALSE; TRUE; TRUE; FALSE}.
• In Excel arithmetic, TRUE acts as 1, and FALSE acts as 0.
  • Row 1: 1 / FALSE = 1 / 0 = #DIV/0!
  • Row 2: 2 / TRUE = 2 / 1 = 2
  • Row 3: 3 / FALSE = 3 / 0 = #DIV/0!
  • Row 4: 4 / TRUE = 4 / 1 = 4
  • Row 5: 5 / TRUE = 5 / 1 = 5
  • Row 6: 6 / FALSE = 6 / 0 = #DIV/0!
• The resulting array is: {#DIV/0!; 2; #DIV/0!; 4; 5; #DIV/0!}. Because we set the second argument of AGGREGATE to 6, all error values are ignored, leaving only {2; 4; 5}.
• Extracting the Value: AGGREGATE extracts the 2nd smallest value from {2; 4; 5}, which is 4. INDEX(C2:C7, 4) then returns $950.

Handling Multiple Conditions

Often, you will need to find the Nth occurrence based on more than one criteria-for instance, finding the 2nd sale made by "Sarah" that was processed in a specific "Region" or above a certain "Threshold". Below is how you adapt both the modern and classic methods for multi-criteria indexing.

Multi-Criteria with FILTER (Modern)

To add more criteria in FILTER, multiply each condition group with an asterisk (*), which functions as the logical AND operator:

=INDEX(FILTER(return_range, (criteria_range1 = criteria1) * (criteria_range2 = criteria2)), N)

Multi-Criteria with AGGREGATE (Legacy-Friendly)

Similarly, with the AGGREGATE approach, you multiply logical conditions in the denominator:

=INDEX(return_range, AGGREGATE(15, 6, (ROW(criteria_range1) - ROW(first_cell) + 1) / ((criteria_range1 = criteria1) * (criteria_range2 = criteria2)), N))

Comparison Summary

To help you decide which approach is best suited for your specific workbook, here is a quick breakdown of each method:

Conclusion

Whether you are building modern reporting dashboards in Excel 365 or maintaining legacy templates in Excel 2016, you no longer have to worry about the limitations of single-occurrence lookups. By leveraging FILTER, AGGREGATE, or array-based INDEX/SMALL constructs, you can easily target any specific transaction or data point in your dataset regardless of its index position.