Priti
When we deal with huge datasets, duplicate entry is a common problem. In Excel, we can find duplicate values using different functions. However, this article will show the use of VLOOKUP function for finding duplicate values and then removing them. Thus, this article will demonstrate four methods to show how to remove duplicates using the VLOOKUP function in Excel.
📁 Download Excel File
You can download the Excel file used for the demonstration from the link below.
Learn to Remove Duplicates in Excel Using VLOOKUP with These 4 Quick Methods
Firstly, let us get acquainted with our dataset used throughout the article as an example. We have two lists of names where some occur repeatedly, and in the third column, we will find duplicate values using formulas. Hence, we will use this dataset to demonstrate 4 simple methods for removing duplicates in Excel using VLOOKUP.
1. Using VLOOKUP and Remove Duplicates
VLOOKUP function finds something in a table or a range by row. This method will use this function to detect the duplicates and then remove them in Excel. Let us see the steps now.
⬇️⬇️ STEPS ⬇️⬇️
- First, select cell D6 and enter the formula.
=VLOOKUP(C6,$B$6:$B$10,1,FALSE)
- Then, we will have output “Oscar” in cell D6 since it already appears in List-1. Now, we will drag the Fill Handle down and paste the formula in all other cells.
- Now, we will see a #N/A error in some of the cells as the values in corresponding cells is not repeated. In brief, we got the duplicate values and distinct values.
- Next, select Filter from the Sort and Filter group of commands to remove the duplicate values in the Data tab.
- After that, we will have a drop-down option in each header.
- Click on Duplicates drop-down menu and select #N/A.
- Finally, we will have only unique values, and duplicate values will be removed.
📕 Read More: 3 Ways to Combine Duplicate Rows and Sum Values in Excel
2. Utilizing VLOOKUP Without Errors
VLOOKUP function finds something in a table or a range by row. The ISERROR Function determines the error in numeric expression and returns TRUE if it finds an error and FALSE otherwise. On the other hand, the IF Function returns one value if the logical comparison is TRUE and another value if that is FALSE. This method will use these functions to find the duplicates and then remove them in Excel. Let us see the steps now.
⬇️⬇️ STEPS ⬇️⬇️
- First, select cell D6 and enter the formula.
=IF(ISERROR(VLOOKUP(C6,$B$6:$B$10,1,FALSE)),””,”Duplicate”)
🔨 Formula Breakdown
Here, the formula we inserted in cell D6 is:
IF(ISERROR(VLOOKUP(C6,$B$6:$B$10,1,FALSE)),””,”Duplicate”)
👉 In this function, C6 contains the value that we have to look up.
👉 $B$6:$B$10 is the array where the function will search for the lookup value.
👉 Then 1 shows that we have to look up the array’s first column, and FALSE represents that it needs to be an exact match.
👉 After that, ISERROR function returns TRUE if the value does not match and returns an error, and FALSE otherwise.
👉 Lastly, the IF function returns a blank cell if the output from the ISERROR function is TRUE, which means this value is not duplicate and returns “Duplicate” if the value matches.
- Then, we will have the output “Duplicate” in cell D6 since it already appears in List-1. We will drag the Fill Handle down and paste the formula in all other cells.
- Now, we will see a blank in some of the cells as the values in corresponding cells are not repeated. In brief, we found which cells contain duplicate values and which do not.
- Next, select Filter to remove the duplicate values from the command Sort and Filter in Data tab.
- After that, we will have a drop-down option in each header.
- Now, click on the Duplicates drop-down menu and select (Blanks).
- Finally, we will have unique values, and duplicate values will be removed.
3. Removing Duplicates from Different Worksheets
VLOOKUP function finds something in a table or a range by row. The ISERROR Function determines the error in numeric expression and returns TRUE if it finds an error and FALSE otherwise. On the other hand, the IF Function returns one value if the logical comparison is TRUE and another value if that is FALSE. We will now demonstrate removing duplicates from different worksheets using these functions. We will use another worksheet named Dataset, which contains List-1 names.
Let us go through the steps.
⬇️⬇️ STEPS ⬇️⬇️
- First, enter the formula in the worksheet where you want the outcome. We are selecting cell C6 in the worksheet named “Different Worksheet”.
=IF(ISERROR(VLOOKUP(B6,Dataset!$B$6:$D$10,1,FALSE)),””,”Duplicate”)
🔨 Formula Breakdown
Here, the formula we inserted in cell C6 is:
IF(ISERROR(VLOOKUP(B6,Dataset!$B$6:$D$10,1,FALSE)),””,”Duplicate”)
👉 In this function, B6 contains the value that we have to look up.
👉 Dataset!$B$6:$D$10 is the array from the Dataset worksheet where the function will search for the lookup value.
👉 Then 1 shows that we must look up in the first column of the array, and FALSE represents that it needs to be an exact match.
👉 After that, the ISERROR function returns TRUE if the value does not match and returns an error, and FALSE otherwise.
👉 Lastly, the IF function returns a blank cell if the output from the ISERROR function is TRUE, which means this value is not duplicate and returns “Duplicate” if it matches.
- Then, we will have output “Duplicate” in cell C6 since it already appears in List-1 of the Dataset We will drag the Fill Handle down and paste the formula in all other cells.
- Now, we will see a blank in some of the cells as the values in corresponding cells are not repeated. In brief, we found which cells contain duplicate values and which do not.
- Next, to remove the duplicate values, go to the Data tab and select Filter from the command editing.
- After that, we will have a drop-down option in each header.
- Now, click on the Duplicates drop-down menu and select (Blanks).
- Finally, we will have unique values, and duplicate values will be removed.
📕 Read More: 5 Ways to Remove Duplicate Rows Based on One Column in Excel
4. Removing Duplicates from Different Workbooks
VLOOKUP function finds something in a table or a range by row. The ISERROR Function determines the error in numeric expression and returns TRUE if it finds an error and FALSE otherwise. On the other hand, the IF Function returns one value if the logical comparison is TRUE and another value if that is FALSE. We will show removing duplicates from different workbooks using these functions. Thus, we will use a new workbook named Dataset.
Let us see the steps.
⬇️⬇️ STEPS ⬇️⬇️
- First, enter the formula in the workbook where you want the outcome. We are entering the formula cell C6 in the worksheet named “Different Workbooks” of our main workbook.
=IF(ISERROR(VLOOKUP(B6,'[Dataset.xlsx]Different Workbook Dataset’!$B$6:$D$10,1,FALSE)),””,”Duplicate”)
🔨 Formula Breakdown
Here, the formula we inserted in cell C6 is:
IF(ISERROR(VLOOKUP(B6,'[Dataset.xlsx]Different Workbook Dataset’!$B$6:$D$10,1,FALSE)),””,”Duplicate”)
👉 In this function, B6 contains the value that we have to look up.
👉 [Dataset.xlsx]Different Workbook Dataset’!$B$6:$D$10 is the array where the function will search for the lookup value which is from a different workbook.
👉 Then 1 shows that we have to look up in the first column of the array, and FALSE represents that it needs to be an exact match.
👉 After that, the ISERROR function returns TRUE if the value does not match and returns an error, and FALSE otherwise.
👉 Lastly, the IF function returns a blank cell if the output from the ISERROR function is TRUE, which means this value is not duplicate and returns “Duplicate” if it matches.
- Then, we will have the output “Duplicate” in cell C6 since it already appears in List-1. We will drag the Fill Handle down and paste the formula in all other cells.
- Now, we will see a blank in some of the cells as the values in corresponding cells are not repeated. In brief, we found which cells contain duplicate values and which do not.
- Next, to remove the duplicate values, go to the Data tab and select Filter from the command editing.
- After that, we will have a drop-down option in each header.
- Now, click on the Duplicates drop-down menu and select (Blanks).
- Finally, we will have unique values, and duplicate values will be removed.
📄 Important Notes
🖊️ #N/A error means that “No value is available”. VLOOKUP function returns this error when it cannot find the exact match.
🖊️ Sometimes a #REF! error might appear while using the VLOOKUP function if the column index number exceeds the column number in the table array.
📝 Takeaways from This Article
📌 This article demonstrated four formulas for removing duplicates in Excel using the VLOOKUP function.
📌 In the first method, we showed the VLOOKUP function to remove duplicates in Excel.
📌 Then, we demonstrated a formula that applies VLOOKUP, ISERROR, and IF functions to remove duplicates in the same worksheet in Excel.
📌 Next, we showed removing duplicates from different worksheets using VLOOKUP, ISERROR, and IF functions in Excel.
📌 Finally, we employed VLOOKUP, ISERROR, and IF functions for removing duplicates from different workbooks in Excel.
Conclusion
This article has demonstrated four ways to remove duplicates in excel using VLOOKUP. All these methods are incredibly effective and simple to use. We hope this article helps you. Please leave a remark if you have any queries. The author will do their best to find an appropriate answer.
For more guides like this, visit Excelden.com.
