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.


Download Practice Workbook

Download this practice sheet to exercise while you are reading this article.


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.

Apply the Standard VLOOKUP Function to Remove Duplicates


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

Apply the Standard VLOOKUP Function to Remove Duplicates

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

  • Apply the function by pressing Enter. The function will return a value if it’s a duplicate.

Apply the Standard VLOOKUP Function to Remove Duplicates

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

Apply the Standard VLOOKUP Function to Remove Duplicates

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.

Apply the Standard VLOOKUP Function to Remove Duplicates

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

Apply the Standard VLOOKUP Function to Remove Duplicates

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

Apply the Standard VLOOKUP Function to Remove Duplicates

  • So we have successfully removed duplicates from the dataset.

Apply the Standard VLOOKUP Function to Remove Duplicates

Read More: Excel VBA: Remove Duplicates Comparing Multiple Columns (3 Examples)


Similar Readings


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”

VLOOKUP with the ISERROR to Remove Duplicates in the Same Worksheet

  • Apply the formula by pressing Enter.

VLOOKUP with the ISERROR to Remove Duplicates in the Same Worksheet

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.

VLOOKUP with the ISERROR to Remove Duplicates in the Same Worksheet

Step 3:

  • To remove those duplicate values, apply the filter option in the dataset.

VLOOKUP with the ISERROR to Remove Duplicates in the Same Worksheet

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

VLOOKUP with the ISERROR to Remove Duplicates in the Same Worksheet

  • So we have removed the duplicates from the dataset.

VLOOKUP with the ISERROR to Remove Duplicates in the Same Worksheet

Read More: How to Remove Both Duplicates in Excel (5 Easy Ways)


ii. Remove Duplicates in the Different Worksheets

Step 1:

  • Create a dataset in a worksheet.

VLOOKUP with the ISERROR to Remove Duplicates in the Different Worksheets

  • 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

VLOOKUP with the ISERROR to Remove Duplicates in the Different Worksheets

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.

VLOOKUP with the ISERROR to Remove Duplicates in the Different Worksheets

  • And select the array.

VLOOKUP with the ISERROR to Remove Duplicates in the Different Worksheets

  • 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”

VLOOKUP with the ISERROR to Remove Duplicates in the Different Worksheets

  • Press Enter to get the result. Drag the cell to apply the same formula to the end of the cell.

VLOOKUP with the ISERROR to Remove Duplicates in the Different Worksheets

Step 3:

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

VLOOKUP with the ISERROR to Remove Duplicates in the Different Worksheets

Read More: How to Remove Duplicates Based on Criteria in Excel (4 Methods)


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.

VLOOKUP with the ISERROR to Remove Duplicates in two Workbooks

  • And select the following array.

VLOOKUP with the ISERROR to Remove Duplicates in two Workbooks

  • 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”

VLOOKUP with the ISERROR to Remove Duplicates in two Workbooks

Step 2:

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

VLOOKUP with the ISERROR to Remove Duplicates in two Workbooks

Step 3:

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

VLOOKUP with the ISERROR to Remove Duplicates in two Workbooks

Read More: How to Remove Duplicate Rows Based on One Column in Excel


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

Leave a reply

ExcelDemy
Logo