How to Find Duplicate Values Using VLOOKUP in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Find Duplicate Values Using VLOOKUP in Excel: 3 Suitable Examples

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.

how to find duplicate values in excel using vlookup


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 to find duplicates 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 name, 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 the VLOOKUP function searches this name from Product Name-1 to Product Name-2. When the same name is found it will output the result from Product Name-1.

Use VLOOKUP to Find Duplicate Values in Two Columns

  • 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 Find Duplicates in Excel Workbook


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")

Apply VLOOKUP to Find Duplicate Values in Two Excel Worksheets

  • 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.

Apply VLOOKUP to Find Duplicate Values in Two Excel Worksheets

  • For a proper view, look at the below GIF.

how to find duplicate values in excel using vlookup

Read More: Excel Formula to Find Duplicates in One Column


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.

Insert VLOOKUP to Find Duplicates in Two Workbooks of Excel

  • 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.

Insert VLOOKUP to Find Duplicates in Two Workbooks of Excel

Read More: How to Find Duplicates in Two Different Excel Workbooks


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.


Download Practice Workbook

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


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.


Related Articles


<< Go Back to Find Duplicates in Excel | Duplicates in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Siam Hasan Khan
Siam Hasan Khan

Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.

8 Comments
  1. 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

  2. Reply
    John Grimes, CPA Jun 8, 2019 at 4:54 AM

    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

  3. 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.

  4. Reply
    excellentcomputereducation May 10, 2021 at 5:38 PM

    very helpful post for excel beginner and expert candidate

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo