In this article, we will see the process on **how to find duplicate values in Excel using VLOOKUP**. We will also see the processes on how to use ** VLOOKUP** to check duplicate values in two Excel Worksheets/Workbooks.

There are many other processes of finding duplicate values which you will find in our previous articles. You can see this article for instance.

**Table of Contents**hide

## Download Practice Workbook

Download these two practice workbook to exercise while you are reading this article.

## 3 Suitable Examples to Find Duplicate Values Using VLOOKUP in Excel

Let’s say, we have a dataset that contains information about several **Products **of **XYZ group.** We will apply ** the VLOOKUP function **to find out the duplicate products between two columns. Here’s an overview of the dataset for today’s task.

### 1. Use VLOOKUP to Find Duplicate Values in Two Columns

Let’s make two columns that contain different product names. We will look for the **Product Name-1** column names in the **Product Name-2** column. Here is the formula that we are going to use:

`=VLOOKUP(List-1, List-2,True,False)`

In this formula, the **List-1** names will be searched in** List-2**. If there exists any **duplicate nam**e, the formula will return the name from **List-1**. Let’s look closely at our example for better clarification.

**Steps:**

- First of all, select cell
**D5**, and write down**the VLOOKUP function**in that cell.

`=VLOOKUP($B$5:$B$13,$C$5:$C$13,TRUE,FALSE)`

- Hence, press
**Enter**on your keyboard. As a result, you will get the duplicate value which is the return value of the**VLOOKUP function.** - Here the
**Air Conditioner**is found because thefunction searches this name from__VLOOKUP__**Product Name-1**to**Product Name-2**. When the same name is found it will output the result from**Product Name-1**.

- Now, drag down the formulated cell
**D5**downwards to carry out the result for the two columns.

- The
**#N/A**results are found because, in those particular cells, the names from column**B**are not found in column**C**. - In the
**Result**column, you’re seeing a total of**4 duplicate**values (**Air Conditioner**,**Microwave Oven**,**Refrigerator**, and**Television**).**#N/A**values are representing the unique values of column**Product Name-1**.

**Read More: How to Use VLOOKUP and HLOOKUP Combined Formula in Excel**

### 2. Apply VLOOKUP to Find Duplicate Values in Two Excel Worksheets

Make 2 new worksheets titled **VL2** and **VL3**. In column **B** of both worksheets, create a list of some product’s name. In this example, we will check the product names of **VL2** with the product names of **VL3**. Let’s follow the instructions below to learn!

**Steps:**

- In
**C5**of**VL3**, type the below formula.

`=IF(ISERROR(VLOOKUP(B5,'VL2'!$B$5:$B$13,1,0)),"Unique", "Duplicate")`

- After that, press
**ENTER**on your keyboard. As a result, you will see the result**Duplicate**because the name**Television**exists in**VL2**.

- Now drag down this formulated cell
**C5**to carry out the result for the rest of the cells in column**C**.

- For a proper view, look at the below
**GIF**.

**Read More:** **Using Excel to Lookup Partial Text Match [2 Easy Ways]**

### 3. Insert VLOOKUP to Find Duplicates in Two Workbooks of Excel

This procedure is the same as the previous one. The one difference is that here, you need to refer to the workbook. The procedure is given below.

- Create a new workbook titled
**VL**and in that workbook create a new worksheet titled**Sheet1**. In**Sheet1**create a product list just like before.

- In our main workbook which we were working on (in our last example), create another worksheet titled
**VL4**and again create a list of products.

- Now in cell
**C5**of**VL4**, write down the following formula and press**ENTER**.

`=IF(ISERROR(VLOOKUP(B5,[VL.xlsx]Sheet1!$B$2:$B$10,1,0)),"Unique", "Duplicate")`

- You will get to see the resulting duplicate as
**Television**exists in**VL4.**

- Now drag down the formulated cell
**C5**to see the result for the rest of cells in column**C**. - This is how you can find out the
**duplicates between the two workbooks**.

**Read More:** **How to Use HLOOKUP Function in Excel (8 Suitable Approaches)**

## Bottom Line

➜ While a value can not found in the referenced cell, the ** #N/A!** error happens in

**Excel**.

➜ ** #DIV/0!** error happens when a value is divided by

**zero(0)**or the cell reference is blank.

**Conclusion**

In this tutorial, we get to see the process of finding duplicate values between two columns/sheets and workbooks in **Excel **using **VLOOKUP**. There are other processes available. You can look at our previous articles to see the other processes for finding duplicates.

Hope you will like this article. Happy excelling.

This is brilliant! May I share the link to this article with another online class that I am taking?

Hi Stanley,

Thanks for the comments. Glad to hear that it is helpful. Yes. You can share it with an online class with an attribution to our blog.

Best regards

It would be most helpful if the “Download” file was in Excel. Thank you

When we have more than one files for an article, we make a compressed file.

Hope you understand. Other articles that have just the Excel file to share, the download options are in Excel File.

Best regards

Kawser Ahmed

Is there a way to have this kind of formula that shows count of duplicates in a single column? I am interested to know if there is a “formula-way” to count how many unique cells and how many duplicates in a single column without referencing other column.

Thanks for reaching out. Kindly mail me your Excel file and queries at [email protected]

I’ll be happy to help.

very helpful post for excel beginner and expert candidate

Thanks, glad we could help you.