How to Find Duplicate Values in Excel using VLOOKUP Formula

Have you ever tried to find duplicate values in excel by comparing two columns? If not, then this guideline is for you. Especially if you are working in an organization, school, or even in a shop, you can easily sort out the repeated values and filter them accordingly.

First, let’s see how many ways we can use the VLookup excel formula in our daily life.

  1. To sort out the new entries in your organization.
  2. To calculate the salary of your employees as per their attendance.
  3. To find the repeated items in-store or shop and then keep their record automatically.

And so on….

Do check the method to convert Numbers into words in Excel

In today’s tutorial, we will only learn the VLookup function of Microsoft Excel at the beginner level to check values that appeared duplicated in excel by comparing two columns.

Find duplicate in Excel using VLookup Formula

How to find duplicates in Excel by comparing two columns.

To understand the VLookup function let’s set an example of football players of school, some of the players have already played in one match while some new player is added in the next match and declared as team B. Our target is to check which person has already played and which are new ones.

Before applying VLookup, we must have knowledge of the VLookup Syntax Working.

It is elaborated below:vlookup syntax explanation

So let’s see how VLookup helps to solve the issue.

vlookup formula

  1. We have two columns showing two teams Team A and Team B and the merged list is arranged in alphabetical order in Team B.
  2. We want to filter the players in Team B that has already played in Team A and mentioned the new players.
  3. Column D has Team A, Column F has Team B having players of Team A and Column H has Team B arranged in alphabetical order.
  4. We will apply the VLookup formula in Column I to filter the old and new players’ entries.
  5. In Column, I use the following formula: =IFERROR(VLOOKUP(H4,$D$4:$D$11,1,0),” New Player”)and press enter.
  6. Just copy the formula to the entire column and your required filtered list showing the Duplicate and New entries.

vlookup function

You can change the values of columns for your convenience, but the whole procedure will be the same.

Hope the above tutorial will help to understand the use of the VLookup function to find duplicates in Excel.

Do comments with your feedback.

Leave a Comment