Look Up the Nth Match

Excel 365Legacy alt

A normal lookup returns the first match. To get the 2nd, 3rd, or nth occurrence — the second order for a customer, the third entry for a code — use FILTER with INDEX in Excel 365, or a classic INDEX/SMALL array in older versions.

Quick formula: to get the 2nd value matching E2 (Excel 365):

=INDEX(FILTER(C2:C9, A2:A9=E2), 2)

FILTER returns all matching values; INDEX then picks the 2nd one from that list.

Functions used (tap for the full reference guide):

FILTER INDEX SMALL

The example

Acme appears three times. We want Acme’s 2nd amount.

	A	B
1	Customer	Amount
2	Acme	$120
3	Bolt	$80
4	Acme	$200
5	Acme	$90
6	Acme’s 2nd amount:	$200

The formula

Acme’s second amount:

=INDEX(FILTER(B2:B5, A2:A5="Acme"), 2) // matches: 120, 200, 90 → 2nd = 200

How it works

Filter first, then index into the results:

FILTER(B2:B5, A2:A5="Acme") returns every Acme amount as a list: {120, 200, 90}.
INDEX(…, 2) picks the 2nd item from that list — 200.
Change the index to 1, 3, … for the first, third, and so on. Point it at a cell to make it adjustable.
In Excel 2019 and older, use the INDEX/SMALL/IF array formula in the variations.

Get the last match by indexing the count: =INDEX(FILTER(B2:B5, A2:A5=E2), COUNTIF(A2:A5, E2)) — the index equals how many matches there are.

Try it: interactive demo

Live demo

Pick which occurrence of Acme to return.

Match number

Value:

Variations

Legacy INDEX/SMALL/IF (array)

Excel 2019 and older — Ctrl+Shift+Enter, fill down for each match:

=INDEX($B$2:$B$5, SMALL(IF($A$2:$A$5=$E$2, ROW($A$2:$A$5)-ROW($A$2)+1), ROWS($A$2:A2)))

Get the last match

Index by the match count:

=INDEX(FILTER(B2:B5, A2:A5=E2), COUNTIF(A2:A5, E2))

All matches at once

FILTER alone spills every match:

=FILTER(B2:B5, A2:A5=E2)

Pitfalls & errors

#REF! when n is too big. Asking for the 4th of 3 matches errors. Guard with IFERROR or check the count with COUNTIF.

FILTER needs Excel 365/2021. Older versions show #NAME? — use the INDEX/SMALL array formula.

The legacy formula must be array-entered and the ranges locked exactly, then filled down. FILTER is far simpler if you have it.

Practice workbook

📊

Download the free Look Up the Nth Match practice workbook

A repeated-key list with the FILTER+INDEX nth-match (results shown), the legacy INDEX/SMALL and last-match variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I look up the 2nd or nth match in Excel?

In Excel 365 use =INDEX(FILTER(values, range=key), n) to return the nth matching value. In older versions use an INDEX/SMALL/IF array formula entered with Ctrl+Shift+Enter.

How do I get the last matching value?

Index by the match count: =INDEX(FILTER(values, range=key), COUNTIF(range, key)), since the number of matches is the position of the last one.

How do I return all matches, not just one?

FILTER alone spills every match: =FILTER(values, range=key) returns the full list in Excel 365 and 2021.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: FILTER · INDEX · SMALL