How to Find Duplicate Values in Excel Using VLOOKUP

Excel is a great tool for working with data. One of its handy features is the VLOOKUP function, which helps you find matching or duplicate values in your data. In this article, we’ll show you how to use VLOOKUP to spot duplicates in a simple way. You’ll learn how to compare two columns in one sheet, check for duplicates across two sheets, and even find duplicates between two different Excel files.

How to Find Duplicate Values in Excel Using VLOOKUP

Find the detailed steps to use VLOOKUP in Excel to find duplicates in your Excel data. Here in this guide, we have created two columns of different sections to check the VLOOKUP formula on columns:

• Here is the formula we are going to use:

=VLOOKUP(List1,List2,TRUE,FALSE)

Step 1: Deploy VLOOKUP

• 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.
• In C2 we will write this formula =VLOOKUP(A2:A10,B2:B10,TRUE,FALSE) and then press enter.

• Now, we will see this result:

Step 2: Verify Result

• Here the Himesh is found because the VLOOKUP function searches this name from Section A to Section B. When the same name is found it will output the result from Section A.
• Now drag the list and see all values.

• The #N/A results are found because, in those particular cells, the names from column A are not found in column B.
• In the Result column, you’re seeing a total of 2 duplicate values ( Himesh, Pragya). #N/A values are representing the unique values of column Section A.

VLOOKUP to Find Duplicate Values in Two Excel Worksheets

We can also use the VLOOKUP to find duplicate values between two Excel worksheets. To do so follow the below steps:

Step 1: Choose the Worksheets

• Make two excel worksheet Section A and Section B.

Step 2: Use VLOOKUP

• In the B2 of Section B type the below code and press enter:-

=IF(ISERROR(VLOOKUP(A2,'Section A'!A1:A10,1,0)),"Unique","Duplicate")

VLOOKUP to Find Duplicates in Two Workbooks of Excel

We can also use the VLOOKUP to find duplicate values between two Excel workbooks. To do so follow the below steps:

Step 1: Choose Workbooks

• Now we create a new Section A in a new Workbook with different values.

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

Step 2: Use VLOOKUP

• Now in cell C2 of Sheet1, write down the following formula and press enter.

=IF(ISERROR(VLOOKUP(B2,[VL]Section A!$A$2:$A$10,1,0)),"Unique", "Duplicate")

• You will get the result as Unique because value does not exist in Section A.

• Now Drag Down the formulated cell to see out the result of the whole column.