VLOOKUP – How to Delete Duplicates in the Index Column

Duplicates may go unnoticed to the eyes; moreover, they will be ignored by the VLOOKUP function. Remember that this formula only retrieves the first occurrence of a value in the index column.

I will show you how to get rid of them very easily.

To illustrate this point, let’s see a simple example…

See the table below… If you VLOOKUP “UK” to return the Sales, you will get 1000 (the second instance of UK with 1000 in Sales is not considered)

Country, Sales

UK, 1000

US, 1500

UK, 1000

This may get complicated when your second instance is the newest entry, for example:

Country, Sales

UK, 1000

US, 1500

UK, 2000

Don’t worry, deleting duplicates is very easy.

Excel 2003 users do the following

  1. Select a cell of the data region
  2. Go to: Data>Filter>Advanced Filter>
  3. Select the action: filter list in place or copy to another location
  4. Pick the option Unique records only
  5. Ok

If you choose to filter list in place, the row with duplicates will be hidden; on the other hand, if you choose filter to another location then the duplicates are deleted.

Take into account that Excel 2003 only deletes all-field-duplicates. In other words, you cannot choose any given column to delete duplicates from, Excel 2003 evaluate the whole row field by field.

See the video…

Excel 2007 users do the following

  1. Select a cell of the data region
  2. Go to Insert>Table or press CTRL+T (a table is created)
  3. Go to: Design>Tools>Remove Duplicates
  4. Select the fields you want to delete duplicates from
  5. Ok

You will receive a message announcing the number of duplicates found and deleted and how many unique items remain.

See the video…

Final comments…

Sometimes you need to decide which duplicates to delete; by highlighting them first, you can then delete them manually. I love the highlighting duplicates feature of Excel 2007.

Go to:  Home>Styles>Conditional formatting>Highlight Cells Rules>Duplicate values.

I hope it helps…

Duplicates are really a headache; you get them again and again, don’t you?