How to Find Duplicate Values in Excel using VLOOKUP

Last updated on May 17th, 2018

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 find 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, Highlight & Remove Duplicates in Excel.

How you can use VLOOKUP to find duplicate values in two columns

Let`s make two columns which contain different product names. We will look for the Product Name-1 column names in 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 name, the formula will return the name from List-1. Let`s look closely in our example for a better clarification.

  • In cell D2, write down VLOOKUP($B$2:B$10,$C$1:$C$10, TRUE, FALSE) and then press Enter.

  • You will see the below result.

  • 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.
  • Now, drag down the formulated cell D2 downwards to carry out the result for the two columns.

vlookup to find duplicates in 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 total 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 Unique Values in Excel & Detect Duplicates

Using VLOOKUP to find duplicate values in two Excel worksheets

Make 2 new worksheets titled as VL2 and VL3. In column B of both worksheets, create a list of some products name.

In this example, we will check the product names of VL2 with the product names of VL3. The procedure is given below.

  • In C2 of VL3, type the below code and press enter.
    =IF(ISERROR(VLOOKUP(B2,'VL3'!$B$2:$B$10,1,0)),"Unique", "Duplicate")

  • You will see the result Duplicate because the name Television exists in VL2.

  • Now drag down this formulated cell to carry out the result for the whole column.

vlookup to find duplicates in two sheets

  • For a proper view, the list of VL2 is written beside the result in VL3.

Read More: How to Compare Two Columns in Excel Using VLOOKUP

Using VLOOKUP to find duplicates in two Workbooks of Excel

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

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

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

  • Now in cell C2 of VL4, write down the following formula and press enter.
    =IF(ISERROR(VLOOKUP(B2,[VL]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 to see the result for the whole column.

vlookup to find duplicates in two workbooks

This is how you can find out the duplicates between two workbooks.

Download The Working Files

Conclusion

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

Hope you will like this article. Happy excelling.

Siam Hasan Khan on FacebookSiam Hasan Khan on Linkedin
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 attitude to grow continuously. Continuous improvement and life-long learning is my motto.

We will be happy to hear your thoughts

      Leave a reply