Sifting through complex datasets to isolate a specific, recurring record can be highly frustrating. While standard search tools like basic VLOOKUP handle primary matches effortlessly, they fail when you need to extract the Nth occurrence based on multiple conditions. Mastering advanced conditional arrays grants you surgical precision over your data, eliminating manual filtering. Note the stipulation: older Excel versions require Ctrl+Shift+Enter execution, whereas Excel 365 streamlines this using INDEX and FILTER. For example, instantly retrieving the 3rd transaction for "Client A" becomes seamless. Below, we break down the exact formulas to implement this solution.

Excel Formula to Search Nth Match with Conditions

Standard Excel lookup functions, such as VLOOKUP, HLOOKUP, and the basic INDEX/MATCH combination, are designed to do one thing very well: find the first occurrence of a target value. However, real-world data analysis often demands more flexibility. You might need to retrieve the second, third, or Nth match from a dataset based on one or more specific conditions.

For example, if you have a sales ledger, you might want to find the 3rd transaction made by "Sales Rep A" in the "North" region. This article explores the most robust formulas to achieve this in Excel, categorized by Excel version, along with detailed explanations of how they work.

The Sample Dataset

To keep our explanations clear and consistent, we will reference a hypothetical sales dataset. Imagine a table spanning the range A1:D11:

Our Goal: Find the 3rd (N = 3) revenue amount for Sales Rep "John" in the "East" region.

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

If you are using Excel 365 or Excel 2021, the dynamic array engine makes this task incredibly simple using a combination of the INDEX and FILTER functions. You do not need complex array syntax or helper columns.

The Formula Syntax

=INDEX(FILTER(return_range, (cond_range1 = cond1) * (cond_range2 = cond2)), N)

Applied to Our Goal

To find the 3rd sale for John in the East region, use this formula:

=INDEX(FILTER(D2:D11, (B2:B11 = "John") * (C2:C11 = "East")), 3)

How It Works

• (B2:B11="John") * (C2:C11="East"): This evaluates both conditions. In Excel, multiplication (*) acts as the logical AND operator for arrays. It returns an array of 1s (where both conditions are True) and 0s (where one or both conditions are False).
  • Resulting array: {1; 0; 1; 0; 0; 1; 0; 1; 0; 0}
• FILTER(D2:D11, ...): The FILTER function takes the Revenue range (D2:D11) and filters out any rows where our condition array is 0.
  • Filtered array of values: {$1,200; $1,500; $1,100; $3,000} (these are the four matches for John in the East).
• INDEX(..., 3): Finally, INDEX extracts the 3rd element from the filtered array, which is $1,100.

Method 2: The Classic Array Formula (Excel 2019 and Older)

For older versions of Excel that do not support dynamic arrays (Excel 2019, 2016, 2013, etc.), you must rely on a traditional CSE (Ctrl + Shift + Enter) array formula combining INDEX, SMALL, and IF.

The Formula Syntax

{=INDEX(return_range, SMALL(IF((cond_range1 = cond1) * (cond_range2 = cond2), ROW(return_range) - ROW(first_cell) + 1), N))}

Note: Do not type the curly braces {} manually. Type the formula and press Ctrl + Shift + Enter. Excel will add the braces automatically.

Applied to Our Goal

=INDEX(D2:D11, SMALL(IF((B2:B11="John") * (C2:C11="East"), ROW(D2:D11) - ROW(D2) + 1), 3))

How It Works

1. Row Normalization (ROW(D2:D11) - ROW(D2) + 1): This construct creates a relative index of rows from 1 to 10 ({1; 2; 3; 4; 5; 6; 7; 8; 9; 10}). This ensures the formula works correctly even if your dataset does not start on Row 1.
2. The Logical Mask (IF(..., RelativeRows)): The IF function checks the conditions (B2:B11="John") * (C2:C11="East"). If True, it returns the relative row index. If False, it returns FALSE.
   • Resulting array: {1; FALSE; 3; FALSE; FALSE; 6; FALSE; 8; FALSE; FALSE}
3. Determining the Nth Row (SMALL(..., 3)): The SMALL function ignores FALSE values and retrieves the 3rd smallest number from the remaining numbers.
   • Sorted matching indices: {1, 3, 6, 8}
   • 3rd smallest: 6
4. Retrieving the Value (INDEX(D2:D11, 6)): INDEX looks at the 6th position in the range D2:D11, returning $1,100.

Method 3: The Non-CSE AGGREGATE Method (Excel 2010 and Later)

If you are using Excel 2010 or newer and want to avoid the complexity of pressing Ctrl + Shift + Enter, you can use the highly versatile AGGREGATE function. This function can perform array operations natively without needing special array entry.

The Formula Syntax

=INDEX(return_range, AGGREGATE(15, 6, (ROW(return_range) - ROW(first_cell) + 1) / ((cond_range1 = cond1) * (cond_range2 = cond2)), N))

Applied to Our Goal

=INDEX(D2:D11, AGGREGATE(15, 6, (ROW(D2:D11) - ROW(D2) + 1) / ((B2:B11="John") * (C2:C11="East")), 3))

How It Works

• 15 tells AGGREGATE to behave like the SMALL function.
• 6 tells AGGREGATE to ignore error values (e.g., #DIV/0!).
• The Division Trick: We divide the relative row numbers {1;2;3;4;5;6;7;8;9;10} by our conditional array of 1s and 0s:
  • If the conditions are met: Row # / 1 = Row #
  • If the conditions are not met: Row # / 0 = #DIV/0!
  • Resulting array sent to AGGREGATE: {1; #DIV/0!; 3; #DIV/0!; #DIV/0!; 6; #DIV/0!; 8; #DIV/0!; #DIV/0!}
• Because parameter 6 is set, AGGREGATE filters out all the #DIV/0! errors. It then extracts the 3rd smallest number (N = 3) from the remaining set {1, 3, 6, 8}, which is 6.
• INDEX(D2:D11, 6) returns $1,100.

Handling Errors Safely

If you search for an Nth match that doesn't exist (for example, asking for the 5th match when only 4 exist), Excel will throw a #REF! or #NUM! error. You can handle this gracefully using IFERROR or built-in functions.

For Excel 365 (Built-in FILTER option)

The FILTER function has a built-in third parameter to return a custom message if no match is found, but because it is nested inside INDEX, we must wrap the entire formula in IFERROR to catch the error if N exceeds the filtered results:

=IFERROR(INDEX(FILTER(D2:D11, (B2:B11 = "John") * (C2:C11 = "East")), 5), "No Match Found")

For Classic Excel Formulas

Simply wrap the INDEX block in an IFERROR block:

=IFERROR(INDEX(D2:D11, AGGREGATE(15, 6, (ROW(D2:D11) - ROW(D2) + 1) / ((B2:B11="John") * (C2:C11="East")), 5)), "No Match Found")

Summary: Which Method Should You Use?