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

`=VLOOKUP(C5,\$B\$5:\$B\$14,1,FALSE)`

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.

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

`=IF(ISERROR(VLOOKUP(C5,\$B\$5:\$B\$14,1,FALSE)),"Unique","Duplicate")`

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

Formula Breakdown

• VLOOKUP(C5,\$B\$5:\$B\$14,1,FALSE):  finds the exact match of C5 in \$B\$5:\$B\$14. The Lookup_value is C5 and the Table_array is \$B\$5:\$B\$14. Col_index_num is 1 and [range_lookup] is (FALSE) for an exact match.
• IF(ISERROR(VLOOKUP(C5,\$B\$5:\$B\$14,1,FALSE)),”Unique”,”Duplicate”): If the value is true, the formula will return “Unique”. If the value is false, the formula will return “Duplicate’’.

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

`=IF(ISERROR(VLOOKUP(B5,Dataset!\$B\$5:\$B\$14,1,FALSE)),"Unique","Duplicate")`

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

Formula Breakdown

• VLOOKUP(B5,Dataset!\$B\$5:\$B\$14,1,FALSE): finds the exact match of C5 in Dataset!\$B\$5:\$B\$14. The Lookup_value is B5, the Table_array is Dataset!\$B\$5:\$B\$14. Col_index_num is 1 and [range_lookup] is (FALSE) for an exact match.
• IF(ISERROR(VLOOKUP(B5,Dataset!\$B\$5:\$B\$14,1,FALSE)),”Unique”,”Duplicate”): If the value is true, the formula will return “Unique”. If the value is false, the formula will return “Duplicate’’.

#### 2.3 Remove Duplicates in Two Workbooks

Steps:

• Enter the formula in C5:

`=IF(ISERROR(VLOOKUP(B5,'C:\Users\Hp\Downloads\[Workbook 2.xlsx]Sheet1'!\$B\$4:\$B\$13,1,FALSE)),"Unique","Duplicate")`

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

Formula Breakdown

• VLOOKUP(B5,’C:\Users\Hp\Downloads\[Workbook 2.xlsx]Sheet1′!\$B\$4:\$B\$13,1,FALSE): finds the exact match of B5 in B4:B13 of sheet 1. The Lookup_value is B5, Col_index_num is 1 and [range_lookup] is (FALSE) for an exact match.
• IF(ISERROR(VLOOKUP(B5,’C:\Users\Hp\Downloads\[Workbook 2.xlsx]Sheet1′!\$B\$4:\$B\$13,1,FALSE)),”Unique”,”Duplicate”): If the value is true, the formula will return “Unique”. If the value is false, the formula will return “Duplicate’’.

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

## Related Articles

<< Go Back to Duplicates in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Asikul Himel

Asikul Islam Himel, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, has contributed over two years to the ExcelDemy project. Starting as an Excel & VBA Content Developer, now he manages projects at You Have Got This Math Project. He wrote 60+ articles for ExcelDemy, reviewed 500+, and focused on quality maintenance. Currently, his responsibilities include project management and team leadership. Himel's interests encompass data analysis, leadership, WordPress applications, and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF