# How to Remove Duplicates Using VLOOKUP in Excel (2 Methods)

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 how to use the VLOOKUP function to remove duplicates in Excel.

## 2 Suitable Ways to Remove Duplicates Using VLOOKUP in Excel

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 the Standard VLOOKUP Function to Remove Duplicates in Excel

We can remove those duplicates using the simple 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 will 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.

Step 1:

• Create another column beside Book List-2 naming Duplicate Values.
• Here we will apply the VLOOKUP function Step 2:

• In cell D4, apply the VLOOKUP The generic VLOOKUP function is,

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• Insert values into the function and the final form is

`=VLOOKUP(C4,\$B\$4:\$B\$13,1,FALSE)`
• Here, Lookup_value is C4
• Table_array is \$B\$4:\$B\$13
• Col_index_num is 1
• [range_lookup] is (FALSE) as we want the exact match • Apply the function by pressing Enter. The function will return a value if it’s a duplicate. Step 3:

• 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 Step 4:

• We have got our duplicate and unique values, now we have to filter them. To do that, select the header of the Duplicate Values column, go to the Home tab, and click on Sort & Filter from the Editing group of commands. Click on Filter. • Now we have the filter icon in every column of the dataset. • Click on the Drop-down icon in the Duplicate Values column, and from the sorting option, check on #N/A and click OK. • So we have successfully removed duplicates from the dataset. ### 2. Create a Formula Using VLOOKUP and ISERROR Functions to Remove Duplicates

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

#### i. Remove Duplicates in the Same Worksheet

Step 1:

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

`=IF(ISERROR(VLOOKUP(C4,\$B\$4:\$B\$13,1,FALSE)),"Unique","Duplicate")`
• Here, Lookup_value is C4
• Table_array is \$B\$4:\$B\$13
• Col_index_num is 1
• [range_lookup] is (FALSE) as we want the exact match
• If the value is true, the formula will return “Unique”
• If the value is false, the formula will return “Duplicate” • Apply the formula by pressing Enter. Step 2:

• So the first value is a duplicate value. Now do the same for the rest of the columns to find out all the duplicates. Step 3:

• To remove those duplicate values, apply the filter option in the dataset. • Select Unique as a filtering option from the drop-down list and click OK to get the duplicates removed. • So we have removed the duplicates from the dataset. #### ii. Remove Duplicates in the Different Worksheets

Step 1:

• Create a dataset in a worksheet. • Now 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 Step 2:

In cell C4, apply the VLOOKUP with the ISERROR formula. The final formula is,

`=IF(ISERROR(VLOOKUP(B4,Dataset!\$B\$4:\$B\$13,1,FALSE)),"Unique","Duplicate")`
• B4 is the lookup_value
• Lookup_array is Dataset!\$B\$4:\$B\$13. Click on the Dataset worksheet to go there. • And select the array. • Col_index_num is 1
• [range_lookup] is (FALSE) as we want the exact match
• If the value is TRUE, the formula will return “Unique”
• If the value is FALSE, the formula will return “Duplicate” • Press Enter to get the result. Drag the cell to apply the same formula to the end of the cell. Step 3:

• Now that we have found the duplicate values, simply remove them by using the filter option. #### iii. Remove 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.

Step 1:

• In cell C4 of the current workbook, apply the VLOOKUP with the ISERROR The final formula is,

`=IF(ISERROR(VLOOKUP(B4,'[Workbook2.xlsx]Sheet1'!\$B\$4:\$B\$13,1,FALSE)),"Unique","Duplicate")`
• B4 is the lookup_value
• Lookup_array is ‘[Workbook2.xlsx]Sheet1’!\$B\$4:\$B\$13. Click on the other workbook to go there. • And select the following array. • Col_index_num is 1
• [range_lookup] is (FALSE) as we want the exact match
• If the value is TRUE, the formula will return “Unique”
• If the value is FALSE, the formula will return “Duplicate” Step 2:

• Press Enter to get the result. Also, apply the same formula to the rest of the cells. Step 3:

• Now 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.

## 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.

## Similar Articles to Explore #### Asikul Himel

We will be happy to hear your thoughts 