VLOOKUP does not find duplicates because it only returns the first match for a lookup value, and it treats values that look identical but differ in formatting, extra spaces, or data type as different. To find or flag duplicates, use COUNTIF, or combine VLOOKUP with helper columns and sorting—but for true duplicate detection, COUNTIF or the Remove Duplicates command are the right tools.
Why VLOOKUP Seems to Miss Duplicates
VLOOKUP is designed to return a single value from the first row where the lookup value matches in the leftmost column of a table. If your data has duplicate lookup values, VLOOKUP will always return the first one it encounters, ignoring the rest. This is not a bug—it is how the function works. For example, if column A contains “Apple” in rows 2, 5, and 9, =VLOOKUP("Apple", A2:B10, 2, FALSE) will always return the value from row 2, never rows 5 or 9.
Another common reason VLOOKUP appears to miss duplicates is that the values are not actually identical. Invisible characters like trailing spaces, non-breaking spaces, or line breaks can make “Apple” and “Apple ” look the same but be different to Excel. Also, numbers stored as text versus numbers stored as numbers will not match, even if they display the same. For instance, a value entered as text “123” and a numeric 123 will not match with an exact match lookup.
How VLOOKUP Handles Duplicates
When you use VLOOKUP with the fourth argument FALSE (or 0), it performs an exact match. It scans the first column from top to bottom and stops at the first exact match. With TRUE (or 1, or omitted), it assumes the first column is sorted and returns the closest match, which can be even more confusing with duplicates because it may return a value from a row that is not even a duplicate.
To see all matches, you cannot use VLOOKUP alone. You need to either use a helper column that adds a unique identifier, or use the FILTER function (available in Excel 2021 and Microsoft 365). For example, with data in A2:B10, =FILTER(B2:B10, A2:A10="Apple") will return all values from column B where column A equals “Apple”.
How to Find Duplicates with COUNTIF
The simplest way to identify duplicate rows in a column is to use COUNTIF. With data in A2:A20, enter this formula in B2 and copy down:
=COUNTIF($A$2:$A$20, A2)
This returns the number of times the value in A2 appears in the whole range. Any cell with a result greater than 1 is a duplicate. To flag them, you can use conditional formatting: select A2:A20, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, enter =COUNTIF($A$2:$A$20, A2)>1, and set a format.
Using a Helper Column to VLOOKUP All Duplicates
If you must use VLOOKUP to retrieve values for each duplicate occurrence, you can create a helper column that makes each row unique. For example, with data in A2:B10, add a helper column that counts occurrences of each value. In C2, enter:
=COUNTIF($A$2:A2, A2)
This gives 1 for the first occurrence, 2 for the second, and so on. Then create a lookup key by concatenating the value and the occurrence number. In D2, enter:
=A2&"-"&C2
Now, to look up the second occurrence of “Apple”, you would use =VLOOKUP("Apple-2", D2:E10, 2, FALSE). This is a workaround, but it is clunky. A cleaner approach is to use the FILTER function if you have Microsoft 365.
Common Problems That Make VLOOKUP Fail
- Trailing spaces: Use
=TRIM(A2)to remove extra spaces, or use the TRIM function in a helper column. - Numbers stored as text: Select the column, go to Data > Text to Columns, and click Finish to convert text numbers to real numbers. Or multiply by 1:
=A2*1. - Non-printable characters: Use the CLEAN function to remove them:
=CLEAN(A2). - Case sensitivity: VLOOKUP is not case-sensitive. If you need case-sensitive matching, use EXACT in an array formula or use the INDEX-MATCH combination with EXACT.
- Lookup column not the leftmost: VLOOKUP can only look in the leftmost column of the table array. If your lookup value is in column B and you want to return from column A, VLOOKUP will not work. Use INDEX-MATCH instead:
=INDEX(A:A, MATCH("Apple", B:B, 0)).
VLOOKUP vs. COUNTIF vs. Remove Duplicates
For finding duplicates, COUNTIF is the most direct. For removing duplicates, use the built-in command: select your data, go to Data > Remove Duplicates. This permanently deletes duplicate rows, so make a copy of your data first. VLOOKUP is not designed for duplicate detection at all. If you need to compare two lists and find duplicates between them, use COUNTIF with a range from the other list, or use the new XLOOKUP function (Excel 2021 and Microsoft 365) which has a similar limitation—it also returns only the first match.
For listing all duplicates, the FILTER function is the modern solution. With values in A2:A20 and you want to list all values that appear more than once, you can use:
=UNIQUE(FILTER(A2:A20, COUNTIF(A2:A20, A2:A20)>1))
This returns a list of unique values that have duplicates. Note that COUNTIF with a range argument works in Microsoft 365.
Frequently Asked Questions
Why does VLOOKUP return the wrong value when there are duplicates?
Because VLOOKUP stops at the first exact match. If your table is not sorted and you use approximate match (TRUE), it can also return a value from a row that does not even match. Always use FALSE for exact match unless you have a sorted table and need an approximate match.
How do I highlight all duplicate rows in Excel?
Select the range, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This highlights cells that have duplicates within the selected range. To highlight entire rows based on a duplicate in one column, use a formula rule with COUNTIF as described above.
Can I use VLOOKUP to return the second or third match?
Not directly. You need a helper column that counts occurrences, as shown above, or use the FILTER function to return all matches. Another option is to use the INDEX and SMALL functions in an array formula, but that is complex.
Why does VLOOKUP not match numbers that look the same?
This usually happens when one value is a number and the other is text. For example, if you import data, numbers may be stored as text. Use the Text to Columns feature to convert them, or use the VALUE function on the text values.
Does removing duplicates affect my formulas?
Yes, if your formulas reference cells that are deleted, they will return errors like #REF!. Always make a backup of your workbook before using Remove Duplicates. You can also use the UNIQUE function to create a new list without altering the original data.
Final Tips for Working with Duplicates
Before you start, clean your data. Use TRIM and CLEAN to remove invisible characters. Convert text numbers to real numbers. Then decide what you want: to find duplicates, use COUNTIF; to remove duplicates, use Remove Duplicates; to list all matches, use FILTER. VLOOKUP is for retrieving a single value, not for duplicate analysis. If you need to compare two lists, consider using a PivotTable to count occurrences, or use the new XLOOKUP function with the match mode set to exact match, but remember it still only returns the first match.
For large datasets, avoid volatile functions and array formulas that slow down performance. Use Excel tables (Insert > Table) to make ranges dynamic. And always test your formulas on a small sample before applying to the whole dataset.
Related
- Excel Undo Encrypt with Password? How to Remove It
- Excel Truck Group Jobs: Pay, Hiring & Reviews
- Excel Track Purdue Global: What It Is & How It Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel VLOOKUP Number Format Issue: How to Fix ItA VLOOKUP number-format problem is usually a mismatch between the stored values, not the way they look on…
- Excel Invalid Name Error: Causes and FixesIn Excel, an invalid name error usually means a formula contains a name Excel cannot recognize, such as…
- Excel FILTER Function Returning #N/A? Fixes That WorkIf Excel’s FILTER function shows #N/A, the error usually comes from an error value in the data or…
- VLOOKUP Not Working With Text? How to Fix ItWhen VLOOKUP will not find text, the most common causes are an approximate-match setting, extra or nonprinting spaces,…
- Excel Print Area Not Clearing? Fix It in Microsoft 365If an Excel print area will not clear, select the affected worksheet and choose Page Layout > Print…
- YEAR Formula Not Working? Excel FixesIf Excel’s YEAR formula is not working, first check that its reference contains a real Excel date, not…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.