How to find the nth match in Excel
Formula (Excel and Google Sheets)
=INDEX(FILTER(A2:A10,B2:B10=F1),H1)Filter the matches and pick the one you want: =INDEX(FILTER(A2:A10,B2:B10=F1),H1) returns match number H1.
- Result on the example
- 4/17/2026 in
F2 - Works in
- Excel 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.
How it works
FILTER returns the dates of every Fabrikam visit as a list, in the order they appear: April 6, 17 and 24. INDEX picks item H1 from that list, so 2 gives April 17. Change H1 to 3 for the third visit.
=INDEX(FILTER(A2:A10,B2:B10=F1),H1)Result46129
A2:A109 rows × 1 col: 4/2/2026, 4/6/2026, 4/9/2026, 4/13/2026, …A range: 9 cells in one column.
B2:B109 rows × 1 col: "Contoso", "Fabrikam", "Northwind", "Contoso", …A range: 9 cells in one column.
F1"Fabrikam"The value in F1.
B2:B10=F1{9 rows × 1 col} FALSE, TRUE, FALSE, FALSE, …The = operator tests whether the two sides are equal.
FILTER(A2:A10,B2:B10=F1){3 rows × 1 col} 46118, 46129, 46136FILTER: Rows of a range that meet every condition. #N/A when none match.- range
A2:A109 rows × 1 col: 4/2/2026, 4/6/2026, 4/9/2026, 4/13/2026, … - condition1
B2:B10=F1{9 rows × 1 col} FALSE, TRUE, FALSE, FALSE, …
- range
H12The value in H1.
INDEX(FILTER(A2:A10,B2:B10=F1),H1)46129INDEX: Value at a given row and column of a range.- array
FILTER(A2:A10,B2:B10=F1){3 rows × 1 col} 46118, 46129, 46136 - row_num
H12
- array
In Google Sheets
The same formula works in Google Sheets: =INDEX(FILTER(A2:A10,B2:B10=F1),H1). We ran it in Google Sheets mode on the same data and it returns 4/17/2026.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(A2:A10,SMALL(IF(B2:B10=F1,ROW(A2:A10)-ROW(A2)+1),H1)) | 4/17/2026 | Works in every Excel version: IF keeps the positions of the matching rows and SMALL picks the nth smallest. Confirm with Ctrl+Shift+Enter in Excel 2019 and earlier. |
| =CHOOSEROWS(FILTER(A2:A10,B2:B10=F1),H1) | 4/17/2026 | CHOOSEROWS also accepts negative numbers: -1 is the last match, -2 the one before it. Excel 2024, Microsoft 365 and Google Sheets. |
Common mistakes
- Asking for more matches than exist returns #REF!. Wrap it as
=IFERROR(INDEX(…),"No visit "&H1). - This is the nth match in list order. For the nth by date, sort first:
INDEX(SORT(FILTER(…)),H1).
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Find the nth match
F2, return the date of visit number H1 for the customer in F1.Questions
How do I get the row number of the nth match?
Return row numbers instead of dates: =INDEX(FILTER(ROW(A2:A10),B2:B10=F1),H1).
How do I find the nth non-blank value in a column?
Filter out the blanks: =INDEX(FILTER(A2:A10,A2:A10<>""),H1).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.