Excel INDEX MATCH No Duplicates: How to Return Only Unique Matches

Formulas & functions · Updated November 22, 2026

The short answer: standard INDEX MATCH returns the first match it finds, so to exclude duplicates you must add a helper column that counts occurrences and then use that count in the MATCH function. The formula =INDEX(return_range, MATCH(1, (lookup_value=lookup_range)*(count_range=1), 0)) with count_range being a helper column of running counts will return only the first unique occurrence.

Why INDEX MATCH Returns Duplicates

INDEX MATCH is a lookup pair: INDEX(column_to_return, MATCH(lookup_value, lookup_column, 0)) finds the position of the first cell in the lookup column that exactly equals the lookup value. If your lookup column contains duplicate values, MATCH stops at the first one. That is by design—it is not a bug. For example, with names in A2:A10 and sales in B2:B10, =INDEX(B2:B10, MATCH("John", A2:A10, 0)) returns the first sale for John, even if John appears three times. To get the second, third, or only unique entries, you need to tell MATCH which occurrence to target.

How to Build a Helper Column to Number Occurrences

The cleanest way to handle duplicates is to add a helper column that numbers each occurrence of a value. In an empty column (say D), with your lookup values in A2:A10, enter this formula in D2 and fill down:

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

This counts how many times the current value has appeared up to that row. For the first John it returns 1, second John returns 2, and so on. Then you can use INDEX MATCH to get the first occurrence by matching both the value and the occurrence number 1. With names in A2:A10, sales in B2:B10, and helper counts in D2:D10, this array formula returns the first unique match:

=INDEX(B2:B10, MATCH(1, (A2:A10="John")*(D2:D10=1), 0))

In Excel 365 and Excel 2021, this works as a dynamic array formula without special entry. In older versions, you must press Ctrl+Shift+Enter to enter it as an array formula. On Mac, the shortcut is Command+Shift+Enter.

Using UNIQUE and FILTER Instead (Excel 365 / 2021)

If you have Excel 365 or Excel 2021, the modern functions UNIQUE and FILTER make this much simpler. To list all unique values from A2:A10, just use =UNIQUE(A2:A10). To return the first sale for each unique name, you can combine them with a dynamic array formula. For example, with names in A2:A10 and sales in B2:B10, this spills a list of first sales:

=LET(unique_names, UNIQUE(A2:A10), MAP(unique_names, LAMBDA(n, INDEX(B2:B10, MATCH(n, A2:A10, 0)))))

This uses LET to store the unique names, then MAP with a LAMBDA to run INDEX MATCH for each one. It returns the first sale for every distinct name. Note that LAMBDA and MAP require Microsoft 365 or Excel 2024; they are not available in Excel 2021.

Returning the Second, Third, or All Matches

Sometimes you do not want to exclude duplicates entirely—you want to pull the nth match. The helper column approach works perfectly for that. Change the occurrence number in the formula to 2 for the second match, 3 for the third, and so on. For example, to get the second sale for John, use:

=INDEX(B2:B10, MATCH(1, (A2:A10="John")*(D2:D10=2), 0))

To list all matches in a spill range, use the FILTER function in Excel 365:

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

This returns all sales for John, duplicates and all. If you want only unique values from that filtered list, wrap it in UNIQUE: =UNIQUE(FILTER(B2:B10, A2:A10="John")).

Common Problems and How to Fix Them

Users often hit a few issues with these formulas:

  • Array formula not entered correctly: In Excel 2019 and earlier, if you do not press Ctrl+Shift+Enter, the formula returns a #VALUE! error. Always confirm the formula bar shows curly braces { } around the formula in those versions.
  • Helper column not filled down: If you only enter the COUNTIF formula in the first row, every row will show 1. Fill down to the last row of your data.
  • Absolute vs relative references: In COUNTIF($A$2:A2, A2), the first range must have the starting row locked with $, and the ending row relative. If you use A2:A2 without the $, filling down shifts the start and breaks the count.
  • #N/A errors when no match exists: If the lookup value has no occurrence with the specified count, MATCH returns #N/A. You can wrap the formula in IFERROR to show a blank or custom message, for example =IFERROR(your_formula, "").
  • Case sensitivity: MATCH is case-insensitive by default. If you need case-sensitive matching, use EXACT inside the array formula: MATCH(1, EXACT(A2:A10, "John")*(D2:D10=1), 0).

Comparing INDEX MATCH with XLOOKUP for Duplicates

XLOOKUP, available in Excel 365 and 2021, also returns the first match by default. To get the second or nth match, you can use the XLOOKUP function with a helper column as well, but the syntax is slightly different. With names in A2:A10, sales in B2:B10, and helper counts in D2:D10, this returns the first unique match:

=XLOOKUP(1, (A2:A10="John")*(D2:D10=1), B2:B10)

Because XLOOKUP is designed to handle arrays natively, you do not need Ctrl+Shift+Enter. For returning all matches, FILTER remains the best choice. The main advantage of INDEX MATCH over XLOOKUP is compatibility with older Excel versions; INDEX MATCH works in every version, while XLOOKUP requires Excel 2021 or 365.

Performance and Large Data Sets

Array formulas that multiply entire ranges (like (A2:A10="John")*(D2:D10=1)) can slow down on very large datasets—say, tens of thousands of rows. The helper column approach is more efficient because it uses COUNTIF, which is fast, and the MATCH part only searches a single column. If you are working with 100,000+ rows, consider using a pivot table to list unique values, or use Power Query (Get & Transform Data) to remove duplicates before analysis. Power Query is available in Excel 2016 and later and can load data, remove duplicates, and return a clean table without formulas.

Frequently Asked Questions

Can I use INDEX MATCH to list all unique values without a helper column? No, not directly. INDEX MATCH returns a single value, not a list. To list unique values, use UNIQUE in Excel 365/2021, or use Advanced Filter (Data > Sort & Filter > Advanced) to copy unique values to another location. A helper column is the classic workaround for older versions.

Why does my formula return #N/A even though the value exists? The most common cause is that the helper column count is not 1 for the first occurrence. Check that the COUNTIF formula was filled down correctly and that the range references are absolute for the start row. Also, if you are using an array formula in an older Excel, forgetting to press Ctrl+Shift+Enter will cause a #VALUE! or #N/A error.

Is there a way to ignore duplicates without changing the data? Yes, the helper column does not alter your original data—it just adds a new column. You can place it far to the right and hide it if you wish. Alternatively, use UNIQUE and FILTER in Excel 365, which do not modify the source data either.

Does this work in Google Sheets? Yes, the same array formula syntax works in Google Sheets, but you do not need Ctrl+Shift+Enter—Google Sheets handles array formulas automatically. The UNIQUE and FILTER functions also exist in Google Sheets, so you can use the same modern approach.

Can I use this to find duplicate entries and flag them? Absolutely. The helper column itself flags duplicates—any row where the count is greater than 1 is a duplicate. You can use conditional formatting (Home > Conditional Formatting > Highlight Cells Rules > Greater Than) on the helper column to highlight counts above 1.

What if I want the last occurrence instead of the first? Use the LOOKUP function with a reversed approach, or use MAXIFS in Excel 2019+ to find the row number of the last match. A simpler trick is to sort your data in descending order so the last occurrence becomes the first, then use the standard INDEX MATCH.

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.