Excel XLOOKUP Next Match: How to Get the 2nd Result

Formulas & functions · Updated November 22, 2026

To get the next match with XLOOKUP in Excel, use the match_mode and search_mode arguments together with a helper column, or wrap XLOOKUP in a formula that filters out previously found values. The simplest reliable method is to add a helper column that numbers each duplicate, then look up that number.

Why XLOOKUP Only Returns the First Match by Default

XLOOKUP scans a lookup array and returns the first value that matches your lookup value, unless you change its match_mode to 2 (wildcard match) or -1/1 (exact match with next smaller/larger). None of those give you the “second” or “third” exact match. The function is designed to return a single result, and its default behavior stops at the first hit. If your data has duplicate lookup values, you need a strategy to make each duplicate unique.

Method 1: Helper Column with COUNTIF (Most Reliable)

Assume your data has names in A2:A20 and sales in B2:B20. Add a helper column in C that numbers each occurrence of the name. In C2, enter:

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

Copy this down. For the first “John” it returns 1, the second “John” returns 2, and so on. Now to get the second match for “John”, use:

=XLOOKUP(2, (A2:A20="John")*(C2:C20), B2:B20)

But that formula won’t work as written because XLOOKUP expects a lookup array, not a multiplication. Instead, use the helper column as part of a concatenated key. In D2, enter:

=A2&"-"&C2

Copy down. Then to get the second “John”’s sales:

=XLOOKUP("John-2", D2:D20, B2:B20)

This returns the sales value for the second occurrence. If you want the row number, use XMATCH instead:

=XMATCH("John-2", D2:D20)

That returns the relative position (e.g., 5 if the second John is in row 6).

Method 2: FILTER + INDEX to Get All Matches

If you have Microsoft 365 or Excel 2021, you can use FILTER to return all matching values, then use INDEX to pick the second one. With names in A2:A20 and sales in B2:B20, enter:

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

This returns the second sales value for John. If there is no second match, it returns #CALC!. You can wrap it with IFERROR:

=IFERROR(INDEX(FILTER(B2:B20, A2:A20="John"), 2), "No second match")

This method is cleaner because it doesn’t require a helper column, but it only works in Excel 2021 or Microsoft 365. On Mac Excel for Microsoft 365, the same formulas work.

Method 3: XLOOKUP with Search Mode (Reverse Order)

If you want the last match instead of the second, you can use search_mode set to -1 (search from last to first). For example:

=XLOOKUP("John", A2:A20, B2:B20, ,0,-1)

This returns the last “John” in the list. To get the second-to-last, you’d need a more complex approach, such as using TAKE with FILTER:

=INDEX(FILTER(B2:B20, A2:A20="John"), COUNTA(FILTER(B2:B20, A2:A20="John"))-1)

That returns the second-to-last match. This is rarely needed, but it shows how search mode can help with “next” in the sense of “previous from the bottom”.

Common Mistakes and How to Avoid Them

  • Forgetting to lock the range in COUNTIF: Use $A$2:A2 so the range expands as you copy down. If you use A2:A20 without locking, the count will be wrong.
  • Using XLOOKUP with a wildcard accidentally: If you set match_mode to 2, “John” will match “Johnny” too. For exact matches, use 0.
  • Assuming XLOOKUP returns an array: XLOOKUP returns a single value unless you give it a return array with multiple columns. It does not spill multiple matches by default.
  • Not handling missing second matches: Use IFERROR or IFNA to return a friendly message instead of an error.

How to Choose Between the Methods

If you only need the second match occasionally, the FILTER + INDEX method is simplest because it’s a single formula and no helper columns. If you need to retrieve multiple different matches (2nd, 3rd, 4th) repeatedly, the helper column with a concatenated key is faster to write and easier to understand for other users. If your data is large (tens of thousands of rows), the helper column approach is more efficient because it avoids recalculating FILTER arrays. If you’re using Excel 2019 or earlier, you cannot use XLOOKUP or FILTER, so you’ll need to use INDEX + SMALL + IF array formulas (entered with Ctrl+Shift+Enter) or a PivotTable.

Alternatives: PivotTable and Power Query

A PivotTable is a built-in alternative that shows all matches without formulas. Drag the lookup field to Rows, the value field to Values, and you’ll see each occurrence as a separate row. To get the second occurrence specifically, you can add a “count” field and filter. Power Query (Get & Transform Data) can also split duplicates into separate columns using the “Group By” feature, but it requires refreshing the query after data changes. These are better when you need a full report of all duplicates, not just one “next” match.

Frequently Asked Questions

Does XLOOKUP have a “next match” argument?

No. XLOOKUP has only four optional arguments: if_not_found, match_mode, search_mode, and (in some versions) a wildcard toggle. None of them let you specify “give me the second match”. You must use a helper column or FILTER.

Can I use XLOOKUP to return all matches at once?

No, XLOOKUP is designed to return a single value. To return all matches, use FILTER. For example, =FILTER(B2:B20, A2:A20="John") spills all sales values for John.

How do I get the 3rd or 4th match?

Use the helper column method and change the number in the lookup value (e.g., "John-3"), or use INDEX(FILTER(...), 3) for the third match. The same pattern works for any ordinal.

Why does my XLOOKUP return #N/A even though there is a match?

This usually happens when the lookup value is text but the data is numeric, or vice versa, or when there are extra spaces. Use TRIM on both sides, or check that the data types match. Also ensure you’re using match_mode of 0 for exact match.

Is there a way to get the next match without a helper column in Excel 2019?

Yes, use an array formula: =INDEX(B2:B20, SMALL(IF(A2:A20="John", ROW(A2:A20)-1), 2)) entered with Ctrl+Shift+Enter. This returns the second match’s value. It’s less intuitive but works in older versions.

Practical Example with Real Data

Suppose you have a sales log with names in A2:A20 and amounts in B2:B20. The data is:

A2: John, B2: 100
A3: Mary, B3: 200
A4: John, B4: 150
A5: John, B5: 300
A6: Mary, B6: 250

To get the second John’s amount (150), use the FILTER method:

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

This returns 150. To get the third John’s amount, change the 2 to 3 and it returns 300. If you use the helper column method, your helper column C would show 1 for A2, 1 for A3, 2 for A4, 3 for A5, 2 for A6, and the formula =XLOOKUP("John-2", D2:D20, B2:B20) returns 150.

Final Thoughts on XLOOKUP Next Match

There is no built-in “next match” parameter, but the workaround is straightforward. Choose the method that fits your Excel version and your need for multiple matches. For most users, the FILTER + INDEX approach is the cleanest, while the helper column is the most compatible and transparent. Always test your formula with a small dataset first to confirm the results.

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.