If you want to learn how to remove duplicates using VLOOKUP in Excel, we’ve got you covered.
Managing a huge dataset comes with its own challenges, varying from duplicates to entry errors. Luckily, Excel is a tool with varying functions and formulas that can help you manage your dataset effectively.
Today, we will explore how to use VLOOKUP to remove duplicates in a dataset so you don’t have to waste time manually going through hundreds or thousands of rows of data.
3 ways to remove duplicates in Excel using VLOOKUP
- Scenario on hand: We have a sample dataset of Disney movies with duplicates.
- What we want to accomplish: We want to remove the duplicates in the data.
We have 3 methods for this:
- Splitting data and using VLOOKUP
- Using VLOOKUP and ISERROR
- Removing duplicates from different worksheets
Method 1 – Splitting the data
The first method is to split the data into two columns. Our dataset has 24 Disney movies, which we divided into two columns of 12.
Step 1 – Prepare the dataset
In the first step, you need to prepare the dataset by splitting it into two columns.
We created a duplicate of the dataset on another sheet so we don’t lose out on the other data (i.e.) Year of Release.
Step 2 – Enter the formula
In the second step, add a third column named “Duplicate Names” and enter the standard VLOOKUP formula.
Here’s the formula:
=VLOOKUP({Lookup value in the second column}, {First column’s range with $}, {column index from which to return value}, FALSE)
=VLOOKUP(C7,$B$7:$B$18,1,FALSE)
After entering the formula, press Enter and drag the Fill Handle to auto-fill the formula across the table. The entries which are not duplicated will have an N/A in the third column.
Note: In the formula, we’ve used the absolute cell reference using the $ sign ($B$7:$B$18) to keep it unchanged while dragging the Fill Handle to auto-fill the formula across the table.
Step 3 – Delete duplicate entries
On the entries where you see a result, select both cells (lookup value and result), right click and press Delete:
Upon pressing Delete, select “Shift cells up”
Converge the data into one list again and then split it into two to repeat the process.
In the first round, the VLOOKUP formula did not mark “Snow White and the Seven Dwarfs” as a duplicate because both entries were in the same column.
In the image above, we see that it detected “Snow White and the Seven Dwarfs” as a duplicate when the entries were in different columns.
While this method uses the simple, standard VLOOKUP formula, combing through the data to remove duplicates from the data manually might get tedious.
Our second method might be more helpful if you have a large dataset.
Method 2 – Using VLOOKUP and ISERROR
In the second method, we use VLOOKUP combined with ISERROR. This method makes it easier to remove duplicates from the dataset.
Step 1 – Prepare the dataset
In this method, we again split the data into two columns and added a third column for identifying duplicates.
Then, enter the following formula in the first cell of the column to identify the duplicates:
=IF(ISERROR(VLOOKUP({Lookup value in the second column}, {First column’s range with $}, {column index from which to return value}, FALSE),”Unique”,”Duplicate”)
=IF(ISERROR(VLOOKUP(C7,$B$7:$B$17,1,FALSE)),”Unique”,”Duplicate”)
This adds “Unique” or “Duplicate” in front of repeated names.
Next, drag the Fill Handle to auto-fill the formula across the column.
Step 2 – Delete the duplicate values
The second step is to delete the duplicate values. In this method, we have to delete the entries manually, but it will be much easier because the “Duplicate Names” column is now standardized.
To sort all the duplicate values together, click “Sort” from the “Data” tab:
In the window, add the duplicate identification in column, “Cell Values” to sort on, and A to Z in sorting order.
This brings all the rows with “Duplicate” to the top.
You can then manually delete the duplicate entries from column two and then repeat the splitting process.
Method 3 – Removing duplicates from different worksheets
In the third method, we again use VLOOKUP combined with ISERROR for data on two separate sheets.
Step 1 – Prepare the dataset
This method is best for a dataset with multiple columns. In our example, we have a list of 24 Disney movies with their year of release. We split the data into two worksheets without messing up the preceding column.
In the second sheet, we added a duplicate identifier column at the end.
Then, enter the following formula in the first cell of the column to identify the duplicates:
=IF(ISERROR(VLOOKUP({Lookup value in the second sheet}, {First Sheet Name!Column’s range with $}, {column index from which to return value}, FALSE),”Unique”,”Duplicate”)
=IF(ISERROR(VLOOKUP(C7,Sheet2!$C$3:$C$15,1,FALSE)),”Unique”,”Duplicate”)
This adds “Unique” or “Duplicate” in front of repeated names.
Next, drag the Fill Handle to auto-fill the formula across the column.
Step 2 – Delete the duplicate values
The second step is to delete the duplicate values using the same “Sort” method.
To sort all the duplicate values together, select the data and then click “Sort” from the “Data” tab:
In the window, add the duplicate identification in column “Cell Values” to sort on and A to Z in sorting order.
This brings all the rows with “Duplicate” to the top.
You can then manually delete the duplicate entries from the second and then repeat the splitting process.
Wrapping up
VLOOKUP is an incredible formula that you can use for different cases. This tutorial explored how to use VLOOKUP to remove duplicates from a dataset. The easiest way is using the standard VLOOKUP formula. However, using this method, deleting the duplicates from the data becomes a hassle. We explore two other methods that make duplicate deletion easier.
Learn more about VLOOKUP through these helpful guides:
