How to Remove Duplicates Using the VLOOKUP Function in Excel – 2 Methods

The sample dataset showcases book names in two columns: “Book List-1” and “Book List-2”. To find duplicates and remove them:

Method 1 – Applying the Standard VLOOKUP Function to Remove Duplicates in Excel

Use the VLOOKUP function. It takes the input value, searches it and returns a match.

Steps:

• Insert another column: Duplicate Names.

• Select D5 and enter the following formula.

The Lookup_value is C5, the table_array is $B$5:$B$14. Col_index_num is 1 and [range_lookup] is (FALSE) for an exact match.

• Press Enter.
• Golden Bowl is a duplicate value.

• Drag down the Fill Handle to see the result in the rest of the cells.
• If there are unique values in those two columns, the function will return the #N/A error.

Filter duplicate and unique values:

• Go to the Home tab and select Sort & Filter > Filter.

• The Filter icon is displayed in every column.

• Click the drop-down icon in Duplicate Values and check #N/A.
• Click OK.

• This is the output.

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

2. Combine the VLOOKUP and the ISERROR Functions to Remove Duplicates

Combine the VLOOKUP and ISERROR functions:

2.1 Eliminate Duplicates in the Same Worksheet

 Steps:

• Use the same dataset.
• Enter the formula in D5.

• Press Enter.
• Golden Bowl is a duplicate value.

• Drag down the Fill Handle to see the result in the rest of the cells.

Filter duplicate and unique values:

• Go to the Home tab and select Sort & Filter > Filter.

The Filter icon is displayed in every column.

• Check Unique as filtering option and click OK to remove duplicates.

• This is the output.

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

2.2 Remove Duplicates in Different Worksheets

Steps:

• Create a dataset in a worksheet.

• Create another dataset in another worksheet.

• In C5, enter the formula:

• Press Enter to see the result.

• Drag down the Fill Handle to see the result in the rest of the cells.

• Remove duplicates using the filter option.

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

2.3 Remove Duplicates in Two Workbooks

Steps:

• Enter the formula in C5:

• Press Enter to see the result.

• Drag down the Fill Handle to see the result in the rest of the cells.

• Remove Duplicate values using the Filter option.

This is the output.

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 a Table_Array, you have to use an absolute cell reference ($) to block the array.

Download Practice Workbook

Download this practice workbook.

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 Duplicates in Excel | Learn Excel