How to Remove Duplicates Using VLOOKUP in Excel

Get FREE Advanced Excel Exercises with Solutions!

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


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the step-by-step process.


2 Suitable Ways to Remove Duplicates Using VLOOKUP in Excel

In the following section, we will use two effective and tricky methods to remove duplicates using VLOOKUP in Excel. This section provides extensive details on these methods. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference. 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.

sample dataset


1. Apply Standard VLOOKUP Function to Remove Duplicates in Excel

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

📌 Steps:

  • First, create another column beside Book List-2 naming Duplicate Names.
  • Here, we will apply the VLOOKUP function.

 Remove Duplicates Using VLOOKUP in Excel

  • Then, select cell D5 and write down the following formula.

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

Here, Lookup_value is C5, Table_array is $B$5:$B$14. Col_index_num is 1 and [range_lookup] is (FALSE) as we want the exact match

  • Next, press Enter.
  • Therefore, the function will return a value if it’s a duplicate. 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 error.

find out the duplicate value to Remove Duplicates Using VLOOKUP in Excel

  • Consequently, we have got our duplicate and unique values, and now we have to filter them.
  • To do that go to the Home tab, and select Sort & Filter > Filter.

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

apply filter to Remove Duplicates Using VLOOKUP in Excel

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

  • Finally, we have successfully removed duplicates from the dataset.

Remove Duplicates Using VLOOKUP in Excel

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


Similar Readings


2. Combine VLOOKUP and ISERROR Functions for Removing Duplicates

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


2.1 Eliminate Duplicates in Same Worksheet

In this method, we will eliminate duplicates in the same worksheet. Let’s walk through the following process to do the task.

📌 Steps:

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

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

  • Next, press Enter.
  • Therefore, the function will return a value if it’s a duplicate. So The Golden Bowl is a duplicate value.

Eliminate Duplicates Using VLOOKUP in Excel

  • Now do the same for the rest of the columns to find out all the duplicates.

  • Consequently, we have got our duplicate and unique values, and now we have to filter them.
  • Next, to do that go to the Home tab, and select Sort & Filter > Filter.

apply filter feature to Remove Duplicates Using VLOOKUP in Excel

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

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

check unique option

  • Therefore, we have removed the duplicates from the dataset.

🔎 How does the formula work?

  • VLOOKUP(C5,$B$5:$B$14,1,FALSE): this formula will find out the exact match of cell C5 value in the range of cells $B$5:$B$14. Here, Lookup_value is C5, Table_array is $B$5:$B$14. Col_index_num is 1 and [range_lookup] is (FALSE) as we want the 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’’.

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


2.2 Remove Duplicates in Different Worksheets

In this method, we will eliminate duplicates in the different worksheets. In order to accomplish the task, let’s follow the steps below.

📌 Steps:

  • First, create a dataset in a worksheet.

dataset for different worksheets to Remove Duplicates Using VLOOKUP in Excel

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

  • Next, in cell C5, apply the VLOOKUP with the ISERROR formula. The final formula is:

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

  • Then, press Enter to get the result.

find out the duplicate value

  • Next, drag the cell to apply the same formula to the end of the cell.

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

get the output

🔎 How does the formula work?

  • VLOOKUP(B5,Dataset!$B$5:$B$14,1,FALSE): this formula will find out the exact match of cell C5 value in the range of cells Dataset!$B$5:$B$14. Here, Lookup_value is B5, Table_array is Dataset!$B$5:$B$14. Col_index_num is 1 and [range_lookup] is (FALSE) as we want the 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’’.

Read More: How to Remove Duplicates in Excel Sheet (7 Methods)


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

📌 Steps:

  • First, in cell C5, apply the VLOOKUP with the ISERROR formula. The final formula is:

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

  • Next, press Enter to get the result.

Erasing Duplicates Using VLOOKUP in Excel

  • Also, apply the same formula to the rest of the cells.

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

find out the unique and duplicate value to Remove Duplicates Using VLOOKUP in Excel

🔎 How does the formula work?

  • VLOOKUP(B5,’C:\Users\Hp\Downloads\[Workbook 2.xlsx]Sheet1′!$B$4:$B$13,1,FALSE): this formula will find out the exact match of cell B5 value in the cell ranges B4:B13 of sheet 1. Here, Lookup_value is B5, Col_index_num is 1 and [range_lookup] is (FALSE) as we want the 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 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. Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!


Similar Articles to Explore

Asikul Himel

Asikul Himel

Hi! I am Asikul Islam Himel. Glad you are here. I am a Team Leader of ExcelDemy, running an excellent team of five efficient Excel & VBA Content Developers. Here at ExcelDemy, we give the best sustainable solutions by posting articles related to MS Excel-related problems. I have completed my under graduation degree from Bangladesh University of Engineering and Technology and my program was Naval Architecture and Marine Engineering. I have found passion in data analysis and research-based fields. I am currently working to grow my leadership quality. I have a great interest in project management and critical thinking. In my free time, I love to travel and read books.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo