Excel INDEX MATCH Duplicates: How to Handle Them

Formulas & functions · Updated November 22, 2026

Yes, Excel’s INDEX MATCH can return duplicate values, but the way it handles them depends on your formula structure. By default, INDEX MATCH returns the first match it finds, so if your lookup column contains duplicate values, you’ll get the first corresponding result—not an error. To handle duplicates properly, you need to add helper columns, use array formulas, or switch to functions like XLOOKUP or FILTER. This article explains exactly how duplicates behave, how to return all matches, and how to avoid common pitfalls.

How INDEX MATCH Works with Duplicates

INDEX MATCH is a two-function combo: INDEX returns a value from a given position in a range, and MATCH finds the position of a lookup value in a range. The classic formula is:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

With 0 as the third argument, MATCH finds the first exact match. If your lookup range has duplicate values, MATCH stops at the first one, so INDEX returns the corresponding value from the return range. For example, with names in A2:A6 and sales in B2:B6, if “John” appears in A2 and A5, =INDEX(B2:B6, MATCH("John", A2:A6, 0)) returns the sales from B2, not B5. This is consistent and predictable, but it means you only get one result per lookup.

Why You Might Want to Handle Duplicates

There are two common scenarios where duplicates become a problem:

  • You need all matching rows: For example, a list of orders where a customer appears multiple times, and you want every order.
  • You need a unique result but the lookup column isn’t unique: For instance, you want the latest date or the highest value for each duplicate.

In the first case, a simple INDEX MATCH won’t work—it only returns one match. In the second, you need to modify the lookup to force uniqueness.

Returning All Duplicate Matches

To return all matches, you have a few options. The most straightforward is the FILTER function (Excel 2021 or Microsoft 365). With names in A2:A10 and sales in B2:B10, to get all sales for “John”:

=FILTER(B2:B10, A2:A10="John")

This spills all matching values down the column. If you want multiple columns, use =FILTER(A2:B10, A2:A10="John"). For older Excel versions, you can use an array formula with INDEX and SMALL, but it’s more complex. For example, to list all matching rows in a helper column, you can use:

=IFERROR(INDEX($B$2:$B$10, SMALL(IF($A$2:$A$10="John", ROW($A$2:$A$10)-ROW($A$2)+1), ROWS($A$2:A2))), "")

Enter this as an array formula (Ctrl+Shift+Enter in older Excel, or just Enter in dynamic arrays). Drag down to fill. This works by creating an array of row positions for matches, then extracting the k-th smallest.

Forcing Uniqueness with Helper Columns

If you need a single result but your lookup column has duplicates, you can create a helper column that combines the lookup value with a count of occurrences. For example, with names in A2:A10, add a helper column C with the formula:

=A2 & "-" & COUNTIF($A$2:A2, A2)

This gives “John-1”, “John-2”, etc. Then use INDEX MATCH on the helper column. To get the first occurrence of “John”, use =INDEX(B2:B10, MATCH("John-1", C2:C10, 0)). This is a classic technique that works in all Excel versions.

Using XLOOKUP and Dynamic Arrays

XLOOKUP (Excel 2021 or Microsoft 365) has an optional match_mode argument, but it still returns only one match. However, you can combine it with FILTER or use the new TAKE and DROP functions to get the first or last match. For example, to get the last match for “John”:

=XLOOKUP("John", A2:A10, B2:B10, "not found", 0, -1)

The sixth argument -1 searches from last to first. For the first match, use 1 or omit it. To get all matches, FILTER is still the best choice.

Common Problems and Misconceptions

Misconception: INDEX MATCH returns an error with duplicates. It doesn’t—it returns the first match. Errors like #N/A occur only when no match is found.

Problem: Duplicate values in the return range. If the return range has duplicates, INDEX MATCH will return the value at the matched position, which is correct. But if you expect all instances, you need FILTER.

Problem: Sorting issues. If your data is sorted, MATCH with 1 or -1 can be faster, but with duplicates it may return an approximate match. Always use 0 for exact matches unless you’re sure the data is sorted and you want approximate.

Problem: Array formula entry. In older Excel, forgetting to press Ctrl+Shift+Enter for array formulas will cause errors or wrong results. In Microsoft 365, dynamic arrays handle it automatically.

Choosing the Right Approach

Here’s a quick guide:

  • Need all matches? Use FILTER (Excel 2021+).
  • Need the first or last match? Use XLOOKUP with the search mode argument.
  • Need a single match but lookup has duplicates? Add a helper column with COUNTIF.
  • Using Excel 2019 or earlier? Use array formulas with SMALL and IF, or helper columns.

For example, if you have a product list with duplicate product IDs and you want the latest price, sort by date and use XLOOKUP with search mode -1 to get the last occurrence.

Frequently Asked Questions

Why does INDEX MATCH return the wrong duplicate? It returns the first match in the lookup range. If you need a different one, you must modify the lookup key (e.g., add a helper column with a count) or use FILTER to get all and then pick the one you want.

Can I use INDEX MATCH to return all duplicates without a helper column? In Microsoft 365, you can use FILTER, which is simpler. In older Excel, you need an array formula like =IFERROR(INDEX($B$2:$B$10, SMALL(IF($A$2:$A$10="John", ROW($A$2:$A$10)-ROW($A$2)+1), ROWS($A$2:A2))), "") entered with Ctrl+Shift+Enter.

What’s the difference between INDEX MATCH and VLOOKUP with duplicates? Both return the first match by default. VLOOKUP has the same limitation. The advantage of INDEX MATCH is that it can look to the left, and it’s more flexible with helper columns.

How do I count duplicates with INDEX MATCH? You don’t use INDEX MATCH for counting; use COUNTIF or COUNTIFS. For example, =COUNTIF(A2:A10, "John") counts occurrences.

Does XLOOKUP handle duplicates better? XLOOKUP also returns only one match, but it has a search mode to get the last match. For all matches, you still need FILTER or a helper column approach.

Can I use INDEX MATCH with duplicate values in both lookup and return ranges? Yes, it works fine—it returns the value at the position of the first match. The result may be duplicate, but that’s expected.

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.