Excel MATCH with Multiple Duplicates: How to Handle

Formulas & functions · Updated November 22, 2026

To find the position of a value when duplicates exist, the MATCH function returns the first match by default, but you can use the match_type argument or combine MATCH with other functions to get the last, all, or specific occurrences. For example, =MATCH(2, B2:B10, 0) returns the position of the first 2 in the range B2:B10.

How MATCH Handles Duplicates by Default

The MATCH function in Excel searches for a specified item in a range and returns its relative position. Its syntax is =MATCH(lookup_value, lookup_array, [match_type]). The third argument, match_type, controls behavior with duplicates:

  • 0 (exact match): Returns the position of the first exact match. If duplicates exist, only the first occurrence is returned. This is the most common usage.
  • 1 (approximate match, less than or equal to): Assumes the lookup array is sorted ascending. Returns the position of the largest value that is less than or equal to the lookup value. With duplicates, it returns the last one in the sorted order that meets the condition.
  • -1 (approximate match, greater than or equal to): Assumes the lookup array is sorted descending. Returns the position of the smallest value that is greater than or equal to the lookup value. With duplicates, it returns the first one in the sorted order that meets the condition.

If you omit match_type, it defaults to 1, which can cause unexpected results if your data isn’t sorted. Always specify 0 for exact matching unless you have a specific reason not to.

Finding the Last Occurrence of a Duplicate

To get the position of the last occurrence of a value, you can use a formula that counts occurrences. With data in A2:A10 and the lookup value in D1, the following array formula returns the position of the last match:

=MATCH(2, 1/(A2:A10=D1), 1)

This works because the expression 1/(A2:A10=D1) creates an array of 1s for matches and #DIV/0! errors for non-matches. The MATCH with match_type 1 finds the largest value less than or equal to 2, which is the last 1 in the array. In Excel 365, you can also use =XMATCH(D1, A2:A10, 0, -1) to search from the last cell to the first, returning the last match directly.

Returning All Positions of Duplicates

If you need a list of every row where a duplicate appears, use the FILTER function (Excel 2021 or Microsoft 365). Assuming values in A2:A20 and the target in D1, this formula spills the row numbers of all matches:

=FILTER(ROW(A2:A20), A2:A20=D1)

This returns an array of row numbers. To get relative positions within the range, subtract the header row offset: =FILTER(ROW(A2:A20)-1, A2:A20=D1). In older Excel versions, you’d need a CSE array formula or a helper column with =IF(A2=$D$1, ROW()-1, "") and then filter manually.

Using MATCH with INDEX to Extract Duplicate Values

MATCH is often paired with INDEX to retrieve a value from another column. For example, with names in A2:A20 and sales in B2:B20, to get the sales amount for the first occurrence of “John” in A2:A20:

=INDEX(B2:B20, MATCH("John", A2:A20, 0))

To get the sales amount for the second occurrence, you can use a formula that counts previous matches. In Excel 365, use =INDEX(B2:B20, MATCH(1, (A2:A20="John")*(COUNTIF(A2:A2, "John")=2), 0)) – but this is an array formula and requires Ctrl+Shift+Enter in older versions. Simpler: use FILTER to return all sales for John: =FILTER(B2:B20, A2:A20="John").

Common Errors and Misconceptions

A frequent mistake is using MATCH without the third argument, expecting exact match. This causes incorrect results if the lookup array isn’t sorted. Another misconception is that MATCH can return all duplicates at once – it cannot; it returns only one position. To list all, use FILTER or a pivot table. Also, MATCH is case-insensitive by default. For case-sensitive matching, use EXACT within an array formula or XMATCH with the match_mode argument set to 1 (case-sensitive) in Excel 365.

Comparing MATCH, XMATCH, and VLOOKUP

XMATCH (Excel 2021/365) improves on MATCH by offering a search mode argument: =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]). Use search_mode -1 to find the last match. VLOOKUP also returns the first match only, but it looks up in the first column and returns a value from a specified column. MATCH is more flexible because it works with any column and returns a position, which you can feed into INDEX. For duplicates, XMATCH with search_mode -1 is the cleanest solution.

Performance and Large Datasets

MATCH is fast even on large datasets because it uses binary search for approximate matches (match_type 1 or -1) and a linear scan for exact matches (match_type 0). For hundreds of thousands of rows, exact match may be slower but still acceptable. XMATCH is optimized similarly. If you need to find all duplicates, FILTER can handle large arrays but may be memory-intensive. For very large data, consider using a pivot table to count duplicates.

Frequently Asked Questions

How do I find the second duplicate in Excel?

Use the SMALL function with an array formula, or in Excel 365, use FILTER to get all positions and then index into that array with =INDEX(FILTER(ROW(A2:A20), A2:A20=D1), 2) to get the second row number.

Why does MATCH return the wrong position with duplicates?

Because MATCH returns the first match by default. If you need the last, use XMATCH with search_mode -1, or the array formula =MATCH(2, 1/(range=value), 1). Also check that match_type is 0 for exact matching.

Can I use MATCH to count duplicates?

No, MATCH returns a position, not a count. Use COUNTIF or COUNTIFS to count duplicates: =COUNTIF(A2:A20, D1).

Does MATCH work with text and numbers?

Yes, MATCH works with any data type. For text, it is case-insensitive by default. For numbers, be careful with numbers stored as text – use the VALUE function or ensure consistent formatting.

What is the difference between MATCH and XMATCH?

XMATCH adds options for case-sensitive matching and search direction (first-to-last or last-to-first). It is available in Excel 2021 and Microsoft 365, while MATCH works in all versions. For duplicates, XMATCH with -1 search mode is the simplest way to get the last occurrence.

How do I highlight all duplicate cells in a column?

Use conditional formatting: select the range, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This highlights all duplicates in the selected range, not just the first.

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.