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