Excel: How to Find Duplicates Using VLOOKUP


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

Leave a Reply

Your email address will not be published.