You can use the following VLOOKUP formula in Excel to find values in one column that are duplicates of values in another column:
=VLOOKUP(B2, $A$2:$A$8, 1, FALSE)
This particular formula looks up the value in cell B2 in the range A2:A8 and returns B2 if it is found. Otherwise, #N/A is returned.
The following example shows how to use this formula in practice.
Example: Find Duplicates Using VLOOKUP in Excel
Suppose we have the following dataset in Excel that shows the names of various fruits sold by a store during two consecutive weeks:
Now suppose we would like to use a VLOOKUP function to find the names of the fruits in the Week 2 column that are duplicates of a name in the Week 1 column.
To do so, we can type the following formula into cell C2:
=VLOOKUP(B2, $A$2:$A$8, 1, FALSE)
We can then drag and fill this formula down to the remaining cells in column C:
If the name of the fruit appears in column C, then it is a duplicate.
For example:
- Pears is a duplicate.
- Peaches is not a duplicate.
- Kiwis is a duplicate.
- Bananas is a duplicate.
And so on.
Note that we could also use the following VLOOKUP formula to return specific values that indicate whether or not each fruit is a duplicate:
=IF(ISNA(VLOOKUP(B2,$A$2:$A$8,1,FALSE)),"Not a Duplicate","Duplicate")
We’ll type this formula into cell C2 and then drag and fill it down to each remaining cell in column C:
From the output we can see:
- Pears is a duplicate.
- Peaches is not a duplicate.
- Kiwis is a duplicate.
- Bananas is a duplicate.
And so on.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Use VLOOKUP to Return Multiple Columns
Excel: How to Use VLOOKUP to Return All Matches
Excel: How to Compare Two Lists Using VLOOKUP