Excel Lookup Second Match: 4 Formulas That Work

Formulas & functions · Updated November 22, 2026

To return the second (or any nth) match in Excel, use the FILTER function (Excel 2021/Microsoft 365): =INDEX(FILTER(range, condition), n). For older versions, use an array formula with INDEX, SMALL, and IF: =INDEX(return_range, SMALL(IF(check_range=value, ROW(check_range)-ROW(first_cell)+1), n)), entered with Ctrl+Shift+Enter.

Why VLOOKUP Can’t Do This

VLOOKUP and HLOOKUP are designed to return only the first match in a column or row. They have no built-in parameter for “second match” or “third match.” This limitation is a common source of frustration, especially when working with duplicate customer IDs, repeated product codes, or multiple transactions on the same date. The same applies to the newer XLOOKUP, which also returns the first match by default (though it can search from the end with the search_mode argument). To get the second match, you need a formula that either filters the data or calculates the row position of each match.

Method 1: FILTER (Excel 2021 and Microsoft 365)

The FILTER function is the simplest and most readable way. It returns an array of all matching values, and you can then pull the nth one with INDEX.

Example layout: Names in A2:A20, sales amounts in B2:B20. To get the second sale for “Alice” (assuming Alice appears multiple times):

=INDEX(FILTER(B2:B20, A2:A20="Alice"), 2)

This returns the second amount in the filtered list. To get the third, change the 2 to 3. If there are fewer than n matches, you get a #CALC! error. To avoid that, wrap in IFERROR: =IFERROR(INDEX(FILTER(...), 2), "Not enough matches").

If you need the entire row of the second match, use FILTER with the whole table range: =INDEX(FILTER(A2:C20, A2:A20="Alice"), 2, 0) returns the second matching row as a horizontal array (requires Excel 365 dynamic arrays).

Method 2: INDEX, SMALL, IF (All Versions)

For Excel 2019 and earlier, the classic array formula works. It calculates the relative row numbers of all matches, then returns the nth smallest.

Example layout: Names in A2:A20, sales in B2:B20. To get the second sale for “Alice”:

=INDEX(B2:B20, SMALL(IF(A2:A20="Alice", ROW(A2:A20)-ROW(A2)+1), 2))

Enter with Ctrl+Shift+Enter in older Excel (in Excel 2021/365, it works with a normal Enter). The ROW(A2:A20)-ROW(A2)+1 converts absolute row numbers to relative positions (1 for row 2, 2 for row 3, etc.). The IF creates an array of those positions only where the name matches, and SMALL picks the second smallest. If there is only one match, you get #NUM! — wrap in IFERROR to handle gracefully.

Method 3: Helper Column with COUNTIF

A simple, non-array approach is to add a helper column that numbers each occurrence of a value. This works in any version and is easier for many users to understand.

Example layout: Names in A2:A20. In C2, enter:

=COUNTIF($A$2:A2, A2)

Drag down. This gives 1 for the first “Alice”, 2 for the second, etc. Then use INDEX/MATCH to get the second occurrence’s data:

=INDEX(B2:B20, MATCH(2, C2:C20, 0))

This returns the sales amount for the second “Alice”. To get the third, change the 2 to 3. The helper column updates automatically if you add rows, as long as you copy the formula down.

Method 4: XLOOKUP with COUNTIF (Excel 2021/365)

You can also use XLOOKUP with a running count trick. Create a helper column as above, then use XLOOKUP to find the nth occurrence:

=XLOOKUP(2, C2:C20, B2:B20)

This works because XLOOKUP finds the first exact match of 2 in the helper column, which corresponds to the second occurrence of the original value. It’s clean but requires the helper column.

How to Choose the Right Method

  • Use FILTER if you have Excel 2021 or Microsoft 365 and want a single formula without helper columns.
  • Use INDEX/SMALL/IF if you need to support older Excel versions and don’t mind an array formula.
  • Use a helper column if you prefer simplicity, need to reference the nth match frequently, or are sharing the workbook with less advanced users.
  • Use XLOOKUP with helper if you’re already using XLOOKUP and want a straightforward approach.

Common Problems and Solutions

#VALUE! or #NUM! errors usually mean the array formula wasn’t entered with Ctrl+Shift+Enter (in older Excel) or there are fewer matches than the nth number you requested. Use IFERROR to show a friendly message.

Case sensitivity: The formulas above are case-insensitive by default. If you need case-sensitive matching, use EXACT inside the IF or FILTER condition (e.g., FILTER(B2:B20, EXACT(A2:A20, "alice"))).

Blank cells or errors in data: If your lookup column contains empty cells, the IF formula might treat them as matches for an empty value. Add a condition like (A2:A20<>"") to exclude blanks.

Multiple criteria: To find the second match where two conditions are true (e.g., name = “Alice” and date = “2025-01-01”), multiply conditions in the IF or use the * operator in FILTER: =INDEX(FILTER(B2:B20, (A2:A20="Alice")*(C2:C20=DATE(2025,1,1))), 2).

Frequently Asked Questions

Can I use VLOOKUP to get the second match? No, VLOOKUP always returns the first match. You need one of the methods above.

Does XLOOKUP have a “second match” option? XLOOKUP has a search_mode argument that can search from last to first, but it still returns only one match. To get the second, you need to combine it with a helper column or use FILTER.

How do I get the second match in Google Sheets? The same FILTER method works in Google Sheets: =INDEX(FILTER(B2:B20, A2:A20="Alice"), 2). For older compatibility, use =INDEX(B2:B20, SMALL(IF(A2:A20="Alice", ROW(A2:A20)-1), 2)) with Ctrl+Shift+Enter.

Why does my array formula return #VALUE! after editing? In Excel 2019 and earlier, you must press Ctrl+Shift+Enter after editing the formula. If you press Enter alone, Excel treats it as a regular formula and returns #VALUE!.

Can I get the second match without a helper column in Excel 2016? Yes, the INDEX/SMALL/IF array formula works in Excel 2016, but you must enter it with Ctrl+Shift+Enter.

Final Tips for Reliable Lookups

When working with large datasets, array formulas can slow down your workbook. If performance is a concern, use a helper column with COUNTIF — it’s faster and easier to audit. Also, consider sorting your data by the lookup column; although not required, it can make manual verification easier. Always test your formula with a small sample to ensure it returns the correct nth match before applying it to thousands of rows.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.