Get nth match with INDEX MATCH

To get the nth match with INDEX MATCH in Excel, you use a formula that combines INDEX, SMALL, and IF (or FILTER in newer versions) to return the value or row number of the k-th occurrence that meets a condition. This article explains the classic array formula, how it works, how to adapt it for multiple criteria, and common pitfalls.

Why INDEX MATCH Only Returns the First Match

A standard INDEX MATCH formula, such as =INDEX(B2:B100, MATCH("Apple", A2:A100, 0)), returns the first row where the lookup value is found. MATCH stops at the first exact match and returns its position. If you need the second, third, or nth occurrence, MATCH alone cannot do it because it has no built-in “occurrence number” parameter.

The workaround is to replace MATCH with a more powerful construct that generates an array of row numbers for all matching rows, then extracts the nth smallest one using SMALL. Once you have that row number, INDEX pulls the value from the column you want.

The Classic Array Formula for the Nth Match

Assuming your data is in rows 2 through 100, with the lookup value in column A and the result to return in column B, the formula for the nth match is:

=INDEX(B2:B100, SMALL(IF(A2:A100="Apple", ROW(A2:A100)-ROW(A2)+1), n))

Enter it as an array formula in older Excel (Ctrl+Shift+Enter). In Excel 365 and Excel 2021, it works with a normal Enter because dynamic arrays are default.

How it works:

• IF(A2:A100="Apple", ROW(A2:A100)-ROW(A2)+1) creates an array of relative row numbers (1,2,3,…) for every cell that equals “Apple”, and FALSE for others.
• SMALL(..., n) returns the nth smallest number from that array, i.e., the row position of the nth match.
• INDEX(B2:B100, ...) returns the value in column B at that position.

For example, if the first “Apple” is in row 2 and the second in row 5, the IF array contains {1;FALSE;FALSE;2;…}. SMALL with n=2 returns 2, and INDEX returns B5.

Using FILTER for a Simpler Approach (Excel 365)

If you have Excel 365 or Excel 2021, the FILTER function provides a cleaner way:

=INDEX(FILTER(B2:B100, A2:A100="Apple"), n)

FILTER returns an array of all values in B where A equals “Apple”, and INDEX picks the nth one. This is easier to read and doesn’t require array entry. For multiple criteria, you can use the multiplication operator:

=INDEX(FILTER(C2:C100, (A2:A100="Apple")*(B2:B100="Red")), n)

This filters column C where column A is “Apple” AND column B is “Red”.

Handling Multiple Criteria in the Classic Formula

To find the nth match based on two or more conditions, add more IF conditions inside the formula:

=INDEX(C2:C100, SMALL(IF((A2:A100="Apple")*(B2:B100="Red"), ROW(C2:C100)-ROW(C2)+1), n))

The multiplication of two boolean arrays acts like AND. If you need OR logic, use addition with parentheses: ((A2:A100="Apple")+(B2:B100="Red")) will count rows where either condition is true (but be careful: if both are true, the sum is 2, which is still nonzero, so it works).

For OR logic, you might want to ensure the result is always 1 or 0 by using --((A2:A100="Apple")+(B2:B100="Red")>0), but in practice, any nonzero number is treated as TRUE in the IF function.

Returning the Row Number Instead of a Value

Sometimes you need the actual row number of the nth match (e.g., to use in another formula). The formula becomes:

=SMALL(IF(A2:A100="Apple", ROW(A2:A100)), n)

This returns the absolute row number (e.g., 5 for row 5). If you want the relative position within the range, subtract the first row and add 1 as before.

If you need to return the entire row of data, use the row number with INDEX for each column, or use the # spill operator in Excel 365:

=INDEX(A2:C100, SMALL(IF(A2:A100="Apple", ROW(A2:A100)-ROW(A2)+1), n), 0)

The 0 for column returns the whole row as an array.

Common Mistakes and How to Avoid Them

Here are the most frequent errors when using these formulas:

• Forgetting to press Ctrl+Shift+Enter in older Excel versions. Without array entry, the formula may return #VALUE! or the first match only.
• Using the wrong row reference. If your data starts at row 2, the relative row numbers must start at 1. The ROW(A2:A100)-ROW(A2)+1 part handles this. If you omit it, you get absolute row numbers, which will misalign with INDEX.
• Not locking ranges. When copying the formula down, use absolute references (e.g., $A$2:$A$100) to avoid shifting the range.
• Assuming the data is sorted. These formulas work on unsorted data; they don’t require sorting. But if you want the nth match in a specific order (e.g., by date), you may need to sort the data first or use more advanced array formulas.
• Using FILTER with a non-array context. In Excel 365, FILTER returns a dynamic array. If you put it in a cell that doesn’t have enough space, you may get a #SPILL! error. Make sure the adjacent cells are empty.

Performance and Limitations

Array formulas can slow down your workbook if used on thousands of rows. The IF and SMALL combination evaluates the entire range for each formula instance. If you have many such formulas, consider using a helper column to flag matches, then use SMALL on that column, or use a PivotTable for large datasets.

Also, these formulas are case-insensitive by default. If you need case-sensitive matching, you’ll need to use EXACT inside the IF, but that complicates the formula and may require array entry.

For Excel versions that don’t support dynamic arrays (pre-365), the classic formula is the standard solution. For Google Sheets, the same array formula works, but you can also use the FILTER function, which is available there.

Frequently Asked Questions

How do I get the 2nd match in Excel without using an array formula?

In Excel 365, you can use the FILTER function as shown above. In older versions, you can add a helper column with a formula like =COUNTIF($A$2:A2,"Apple") to number each occurrence, then use VLOOKUP or INDEX MATCH to find the row where the helper equals 2. This avoids array formulas and is easier for many users.

Can I get the nth match with INDEX MATCH when the data is in a different order?

Yes, the formula doesn’t care about the order. It scans the entire range and picks the nth occurrence in the order they appear in the data. If you need the nth match based on a sorted order (e.g., the second oldest date), you would need to sort the data or use a more complex array formula involving LARGE/SMALL on dates.

Why does my formula return #NUM! when there are fewer than n matches?

SMALL returns #NUM! if the requested k is larger than the number of items in the array. To avoid this, you can wrap the formula in IFERROR, or use a dynamic array formula that handles it gracefully. For example, in Excel 365, =IFERROR(INDEX(FILTER(...), n), "Not enough matches").

How do I get the nth match with multiple criteria and return multiple columns?

Use the FILTER function for the entire table: =FILTER(A2:C100, (A2:A100="Apple")*(B2:B100="Red")) returns all columns for all matching rows. To get the nth row, use =INDEX(FILTER(A2:C100, ...), n, 0). In the classic formula, you can use =INDEX(A2:C100, SMALL(IF(...), n), 0) and enter as an array.

Does this work in Google Sheets?

Yes, the classic array formula works in Google Sheets, and you can also use the FILTER function. Google Sheets supports dynamic arrays natively, so no special entry is needed. The syntax is identical.

Related

• LISA Gravitational Wave Detector: How It Works
• Best Games Like Monster Hunter Stories for Turn Based Monster Collecting
• Custom AI Chip Design: What Businesses Must Know