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.

**Table of Contents**hide

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

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

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

**Similar Readings**

**How to Delete Duplicates in Excel but Keep One (7 Methods)****Excel VBA: Remove Duplicates from an Array (2 Examples)****How to Remove Duplicates and Keep the First Value in Excel (5 Methods)****Remove Duplicate Rows Except for 1st Occurrence in Excel (7 Ways)****How to Remove Duplicate Names in Excel (7 Simple Methods)**

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

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

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

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

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

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