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
XLOOKUPwith 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
- Excel Conditional Formatting for Past Dates: How to Set It Up
- Excel Conditional Formatting for Past Dates
- Excel Conditional Formatting Duplicate Rows: Full Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- CLEAN Function in Excel: Examples and Common FixesIf the text you want to clean is in A2, enter =CLEAN(A2) in another cell. For example, if…
- XLOOKUP to Compare Two Sheets in Excel Step by StepIf Sheet1 has IDs in A2:A100 and Sheet2 has IDs in A2:A100, enter this in Sheet1 cell B2…
- Change Text Filter to Number Filter in Excel?To change a Text Filters menu to Number Filters in Excel, convert the values in that column from…
- Power Query If Function: Syntax, Examples, and TipsPower Query’s if is a conditional expression, not a function: write if condition then value_if_true else value_if_false. For…
- Daily Attendance Sheet in Excel With FormulasTo make a daily attendance sheet in Excel, put employee or student names in column A, dates across…
- How to Count Unique Values in a Row in Excel EasilyTo count distinct, nonblank values across a row in Microsoft 365, use =IFERROR(COLUMNS(UNIQUE(FILTER(B2:K2,B2:K2<>""))),0). This assumes the values are…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.