How to Remove Duplicates Using VLOOKUP in Excel

When you have to deal with massive data from multiple worksheets across your Excel workbook, the VLOOKUP function is your savior. The VLOOKUP function is one of the most useful and at the same time one of the most sophisticated functions in Excel. This function helps you to assemble your data and also helps you to remove duplicate values in an organized way. Today, in this article we will discuss two suitable methods to use the VLOOKUP function to remove duplicates in Excel.

Remove Duplicates Using VLOOKUP in Excel: 2 Suitable Ways

In the following section, we will use two effective and tricky methods to remove duplicates using VLOOKUP in Excel. This section provides extensive details on these methods. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference. Consider a situation where you are given a dataset of some book names in two columns named “Book List-1” and “Book List-2”. Now there are some duplicate values in those columns. We need to find those duplicates and remove them using the VLOOKUP function.

1. Apply Standard VLOOKUP Function to Remove Duplicates in Excel

We can remove those duplicates using the VLOOKUP function. In simple words, the VLOOKUP function takes the input value, searches it in the worksheets, and returns the value matching the input. Now when we use the VLOOKUP function to find any matching value, if it returns a valid output, means this value is duplicate otherwise it is a unique value. Let’s demonstrate this method using these steps.

📌 Steps:

• First, create another column beside Book List-2 naming Duplicate Names.
• Here, we will apply the VLOOKUP function.

• Then, select cell D5 and write down the following formula.

Here, Lookup_value is C5, Table_array is $B$5:$B$14. Col_index_num is 1 and [range_lookup] is (FALSE) as we want the exact match

• Next, press Enter.
• Therefore, the function will return a value if it’s a duplicate. So The Golden Bowl is a duplicate value.

• Now, drag the cell to the end of the column to find the other duplicate values.
• If there are any unique values in those two columns, the function will return the #N/A error.

• Consequently, we have got our duplicate and unique values, and now we have to filter them.
• To do that go to the Home tab, and select Sort & Filter > Filter.

• Now we have the Filter icon in every column of the dataset.

• Next, click on the drop-down icon in the Duplicate Values column, and from the sorting option, check on #N/A and click OK.

• Finally, we have successfully removed duplicates from the dataset.

Read More: How to Remove Duplicates Based on Criteria in Excel 

2. Combine VLOOKUP and ISERROR Functions for Removing Duplicates

We can also add some other functions with the VLOOKUP function to remove duplicates in Excel. We can combine the VLOOKUP and ISERROR functions to do our task. Let’s learn!

2.1 Eliminate Duplicates in Same Worksheet

In this method, we will eliminate duplicates in the same worksheet. Let’s walk through the following process to do the task.

📌 Steps:

• First, we will use the same dataset from the previous methods. In cell D5, apply the VLOOKUP function in the ISERROR function. Insert the values into the function and the final form is:

• Next, press Enter.
• Therefore, the function will return a value if it’s a duplicate. So The Golden Bowl is a duplicate value.

• Now do the same for the rest of the columns to find out all the duplicates.

• Consequently, we have got our duplicate and unique values, and now we have to filter them.
• Next, to do that go to the Home tab, and select Sort & Filter > Filter.

• Now, we have the Filter icon in every column of the dataset.

• Next, select Unique as a filtering option from the drop-down list and click OK to get the duplicates removed.

• Therefore, we have removed the duplicates from the dataset.

Read More: How to Use Formula to Automatically Remove Duplicates in Excel

2.2 Remove Duplicates in Different Worksheets

In this method, we will eliminate duplicates in the different worksheets. In order to accomplish the task, let’s follow the steps below.

📌 Steps:

• First, create a dataset in a worksheet.

• Afterward, create another dataset in another worksheet. Using the dataset from two worksheets, we will remove duplicates from the data using the VLOOKUP with the ISERROR formula.

• Next, in cell C5, apply the VLOOKUP with the ISERROR formula. The final formula is:

• Then, press Enter to get the result.

• Next, drag the cell to apply the same formula to the end of the cell.

• Now that we have found the duplicate values, simply remove them by using the filter option.

Read More: How to Find & Remove Duplicate Rows in Excel

2.3 Erase Duplicates in Two Workbooks

In the following example, we have two datasets in two different workbooks. And we want to remove duplicates from the dataset. Follow these steps to learn the procedure.

📌 Steps:

• First, in cell C5, apply the VLOOKUP with the ISERROR formula. The final formula is:

• Next, press Enter to get the result.

• Also, apply the same formula to the rest of the cells.

• After that,  simply sort out the Duplicate values using the Filter option to remove those duplicates. And we have our final result.

Things to Remember

⏩ The VLOOKUP function always searches for lookup values from the leftmost top column to the right. This function never searches for the data on the left.

⏩ If you enter a value less than 1 as the column index number, the function will return an error.

⏩ When you select your Table_Array, you have to use the absolute cell references ($) to block the array.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the step-by-step process.

Conclusion

Today we learned how to remove duplicates in Excel using the VLOOKUP function. We hope this article proves useful to you. If you have any ideas or suggestions, you are welcome to share your thoughts in the comment box. Keep learning new methods and keep growing!

Related Articles 

• How to Remove Duplicate Rows Based on One Column in Excel 
• How to Remove Duplicate Rows in Excel Based on Two Columns
• How to Remove Duplicates from Columns in Excel
• Hide Duplicate Rows Based on One Column in Excel 
• How to Remove Duplicate Rows in Excel Table

<< Go Back to Remove Duplicates in Excel | Duplicates in Excel | Learn Excel