VLOOKUP Second Match: Return the Nth Occurrence in Excel

A VLOOKUP second match is not something VLOOKUP can return on its own: it always stops at the first row that matches. To get the second match, or the third, or any nth match, you either list every match with FILTER and pick one with INDEX, or give each repeat its own key in a helper column. This guide shows both, plus a no-helper formula for older Excel.

Why VLOOKUP only returns the first match#

VLOOKUP searches the first column from the top and stops as soon as it finds the value. Everything below that row is ignored. XLOOKUP and MATCH work the same way. That is fine for unique IDs, but not for a customer who appears on several orders.

All the examples use this order list in A1:C8:

=VLOOKUP("Lena",A2:C8,3,FALSE) returns 120, Lena's first order. Her second order, the Lamp for 64, is the one we want.

How to return the second match with INDEX and FILTER#

In Excel 2021 or Microsoft 365, combine FILTER and INDEX. FILTER returns every value that meets a condition, as a list. INDEX then picks one item from that list by its position:

=INDEX(FILTER(C2:C8,A2:A8="Lena"),2)

1. FILTER(C2:C8,A2:A8="Lena") builds the list of Lena's amounts: 120, 64, 99.
2. INDEX(...,2) takes the second item from that list.

The result is 64. Change the 2 to 3 and you get 99, the third match. To return the product name instead, filter column B: =INDEX(FILTER(B2:B8,A2:A8="Lena"),2) returns Lamp.

This is the cleanest method because it needs no extra columns and keeps working when rows are added. The FILTER function guide covers its arguments, and the Excel FILTER function article shows more conditions. The INDEX reference explains how the position number works.

How to return the nth match from a cell#

Put the name and the occurrence number in cells so one formula answers every question. With the customer in F1 and the number in G1:

=INDEX(FILTER(C2:C8,A2:A8=F1),G1)

If G1 is larger than the number of matches, INDEX returns #REF!. If the name has no matches at all, FILTER returns #CALC!. Wrap the formula in IFERROR to show a friendly message for both:

=IFERROR(INDEX(FILTER(C2:C8,A2:A8=F1),G1),"No such match")

To get the last match, whatever the count, use COUNTIF as the position: =INDEX(FILTER(C2:C8,A2:A8=F1),COUNTIF(A2:A8,F1)) returns 99 for Lena. XLOOKUP can also do this with search mode -1; see how to look up the last match.

VLOOKUP second match with a COUNTIF helper column#

In any version of Excel, you can make VLOOKUP find the second match by giving every row a unique key. The key joins the customer name with a running count: Lena-1, Lena-2, Lena-3.

VLOOKUP can only look to the right, so the key must be in the first column. Insert a new column A (right-click the column A header, then Insert). The data moves to columns B:D. In A2, type:

=B2&"-"&COUNTIF($B$2:B2,B2)

Fill it down to row 8 (Windows: Ctrl+D; Mac: ⌘D). The range $B$2:B2 is an expanding range: the start is locked with $, but the end moves down as you fill. So in row 4 it becomes $B$2:B4 and counts the second Lena, giving Lena-2.

Now VLOOKUP finds the second match with an exact-match lookup:

=VLOOKUP("Lena-2",A2:D8,4,FALSE)

The result is 64. With the name in F1 and the number in G1, use VLOOKUP(F1&"-"&G1,A2:D8,4,FALSE). The COUNTIF function guide explains the counting part. You can hide the helper column if you don't want people to see it.

Nth match in older Excel without a helper column#

If you can't use FILTER and don't want a helper column, INDEX with SMALL and IF finds the nth matching row:

=INDEX(C2:C8,SMALL(IF(A2:A8="Lena",ROW(A2:A8)-ROW(A2)+1),2))

• ROW(A2:A8)-ROW(A2)+1 numbers the rows 1 to 7.
• IF(A2:A8="Lena",...) keeps the numbers of Lena's rows: 1, 3 and 5.
• SMALL(...,2) takes the second smallest: 3.
• INDEX(C2:C8,3) returns 64.

In Excel 2019 and earlier this is an array formula: press Ctrl+Shift+Enter instead of Enter (Mac: ⌘+Shift+Return). In Microsoft 365 a normal Enter works. It's harder to read than FILTER, so use it only when you must. The INDEX MATCH guide covers the INDEX basics.

Return all the matches instead#

Sometimes you don't need the second match, you need every match. FILTER alone does that: =FILTER(B2:C8,A2:A8="Lena") spills all three of Lena's orders into the cells below. To show them in one cell, such as "Chair, Lamp, Rug", combine TEXTJOIN with FILTER. Both are covered in how to return multiple values from a lookup and how to list every match in one cell.

Two common mistakes to avoid:

• Forgetting FALSE in VLOOKUP. Without it, VLOOKUP uses an approximate match and can return the wrong row.
• Not locking the start of the COUNTIF range. COUNTIF(B2:B2,B2) filled down becomes B3:B3, B4:B4, so every key ends in -1.
• Hidden spaces in the names. "Lena " with a trailing space is a different value from "Lena", so it gets its own count and FILTER skips it. Clean the column with TRIM before you look anything up.
• Sorting after building the helper keys. The counts follow the row order. If you sort the data, the formulas recalculate, so "Lena-2" may now point at a different order. That is correct, but it surprises people who wrote the old answer down.

Practise it#

Repeat customers, duplicate employee names and multiple orders per ID all show up in job tests. Practise FILTER in a live spreadsheet: you type the formula and it checks it instantly. Then practise the building blocks in the INDEX MATCH lesson and the COUNTIF lesson, or try the exercise on this page first.