Excel Duplicate Finder Using VLOOKUP

A common problem when working with data in Microsoft Excel is working out how to remove duplicates. In case you’re still looking, this is where the VLOOKUP function (vertical lookup) helps you find duplicate cells.

The tool ‘searches’ for a value declared by you in a column, and then returns a value from a different column in the same row.

How To Use VLOOKUP TO Find Duplicates

To use VLOOKUP, simply type =VLOOKUP() in the formula bar. You will need to enter your ‘lookup value’ between the brackets (parentheses) followed by a comma. This can be an actual value, or a blank cell that will hold a value, for example:

=VLOOKUP(H2,) – we’ve chosen to use cell H2.

Now you should enter your table array, or lookup table, the range of data you want to search, plus a comma. For example:

=VLOOKUP(H2,B3:F25,) – So, we have our H2, which can either hold data or be blank, and now we’ve included the range of data we want to search for duplicates, i.e. B3:F25.

So, we now need to enter a column index number. This will be a column to the right of your data where you think the answers are. Our VLOOKUP formula should now look like this:

=VLOOKUP(H2,B3:F25,3) – the 3 denotes column 3 where your answers are located.

The final part to complete a simple VLOOKUP is to determine whether we want to find a partial match (FALSE), or an exact match (TRUE). If we opt for an exact match, are VLOOKUP formula will look like this:

=VLOOKUP(H2,B3:F25,3,TRUE)

Why We Love VLOOKUP

Simply put, think of VLOOKUP as using a phone directory that has over 1 million entries. You know the person’s name (data you know), but not their phone number (data you don’t know).

You can use VLOOKUP to search the data using what you already know (the person’s name), and then the VLOOKUP function will display something you don’t know – the person’s phone number.

Of course, you then need to tell VLOOKUP where to display that data!

So now you know how to perform a simple VLOOKUP, how do you use this tool to find and even replace duplicate values?

We’ll show you this next.

---

---

Find & Remove Duplicate Values

One of the great things about VLOOKUP is that you can use it to find and remove duplicate data values, ensuring that your information is more accurate and that there’s no crossover. 

It’s quick and easy to create a formula in Excel to identify duplicate data across a spreadsheet. Once you’ve identified duplicates, you can easily remove them, either by manually deleting them or by replacing them with fresh data. As you can find where the duplicates are in the data, you can also identify their source, which makes it easier for you to improve your spreadsheet and ensure that any mistakes don’t happen again. 

---

---

Using VLOOKUP To Find Duplicates

With VLOOKUP it’s easy to find duplicates in your data. If you’re using one sheet, you can easily identify duplicates by adding an extra column to the end of your table. 

Name each list simply, for example, list 1, list 2 etc. 

Then you need to create a simple formula and add this to the blank column at the end of the table. 

So, if we only had two lists, we would create a formula similar to the one below:

=VLOOKUP(List1,List2,TRUE,FALSE)

You then need to drag down, so that the formula runs across the entirety of each list you’re checking. 

When you press enter, the formula will identify any duplicate data between the two lists and place it in the third column. 

---

---

Using VLOOKUP To Remove Duplicates

Once you’ve identified duplicates in your data, you can remove them using VLOOKUP. This is useful for when the duplicates aren’t required and were a mistake. 

All you need to do is click on Sort & Filter, then select the Filter function.

Once you do this, a dropdown menu will appear at the top of each column. 

Click on the dropdown for the duplicate column, and then select all and click N/A.

Your duplicate values will then be replaced with the phrase N/A throughout the workbook, and you can then search the sheet to remove all N/A data or leave it in to highlight where duplicate data was included.

Conditional Formatting To Highlight Information

If you want to highlight specific information if it conforms to a rule, then you can do this by combining VLOOKUP and conditional formatting. 

Conditional formatting allows you to set a rule, and then highlight data within your Excel workbook that meets your criteria. 

To do this using VLOOKUP, you need to select the data you want to run your conditional formatting on, then go to Conditional Formatting under Home. 

Go to New Format, and select Use a formula to determine which cells to format in the Select a Rule Type list box

Then, enter your formula, which you can create depending on the function you want it to have. Include VLOOKUP at the start, then add your formula in brackets, outlining the rule and data involved. 

Click on format, and then go to fill and select a colour so that you can easily see the cells that meet your criteria. 

Then, you’ll be able to apply your formula and see which cells contain data that meets your needs.

---

---

How To Duplicate Rows

When you’re applying a VLOOKUP formula, it’s possible to apply it to duplicate rows in your Excel worksheet. 

To do this, you need to simply copy and paste the same formula to the new section of the worksheet, adjusting the target cells to suit. 

Then you’ll easily be able to apply your VLOOKUP formula to multiple rows and use it for several data points. 

This approach only applies if you’re keen to use the same formula: if you need to make a new one, then you can create it using the tips in this article. 

Search & Find Duplicate Data

Duplicate data can crop up in even the most well-maintained of Excel sheets, but if you don’t want duplicates, then it’s easy to find and identify them. 

Using VLOOKUP, you can quickly search your data sheet for duplicate values and find them, then remove or analyse them, depending on your preference. 

If you have two lists, named 1 and 2, and need to check for data across each, this is easy. Just create the formula =VLOOKUP(List-1, List-2,True,False) and add it to a third column. 

The List-1 data will be searched in List-2. If there are any duplicates, then these will be listed in the third column where the formula was placed.

With this duplicate data in your third column, you can understand the workings of your datasheet and eliminate the duplicate data if you want to. 

How To Find Case-sensitive Duplicates In Excel

If you want to find a case-sensitive duplicate, then it’s possible to update your VLOOKUP formula to account for this. 

A case-sensitive duplicate is one that is the same word as another but has been written partially or fully in capital letters. So, for example, the words EXCEL, excel and Excel are all the same term, and might refer to the same program, but VLOOKUP won’t automatically identify them as duplicates as it is not case-sensitive. 

As such, if you want to identify all the duplicates in a data set, no matter what case they are, you need to either create a helper column or by adapting your formula. Both approaches are similar in that they both include semi-colons to make the formula account for capital letters. 

How To Count Duplicates In Excel

If you want to count duplicate examples in Excel, then you can use the COUNTIF function and create a formula and place it in an additional column on the end of the workbook, such as:

=COUNTIF($C$2:$C$8,E2)

All you need to do then is highlight the cells you want to count, and the formula will give you numerical data on how many duplicates there are for each piece of duplicate data. 

This formula can be used in conjunction with VLOOKUP and will count how many instances of duplication are included for each value that VLOOKUP identified. 

Conclusion

Duplicates are bound to crop up in any data, and whether you want to know where they are to find and assess them, or you want to get rid of them, VLOOKUP can help. 

This useful tool can make the process of identifying duplicates in your data and eradicating them, if required, simple and straightforward. If you’re still not sure about how to make the most of VLOOKUP after reading this article, then consider using the Excel Experts’ Excel consultancy services. 

FAQs

Why does Excel not recognise duplicates?

Excel does not recognise duplicates in raw data as it assumes that the user who made the sheet added the duplicates in deliberately. However, if you need to identify and remove duplicates that are in the data by accident, Excel has handy tools to help, incuding VLOOKUP. So, you get the best of both worlds with this versatile tool. 

How do I filter duplicates in Excel?

To find duplicates in Excel, you can use VLOOKUP to create a custom formula. If you have two lists, 1 and 2, and need to check for data across each, you use the formula =VLOOKUP(List-1, List-2,True,False).

The List-1 data will be searched in List-2. If there are any duplicates, then these will be listed in the third column where the formula was placed. 

How to find duplicates across multiple Excel sheets?

Finding duplicates across multiple Excel sheets using VLOOKUP is similar to doing it in one sheet, only you’ll need to desginate each sheet a name, and then include this name in the VLOOKUP formula. 

How to find Excel duplicates, but ignore blanks? 

If you’re using the VLOOKUP tool to find and remove duplicates, but want to keep any blank cells, then you’ll need to find a blank cell next to the data you’re removing duplicates from, and type the filter =A3=A2. Drag down to autofill this next to every cell you’re removing duplicates from, then select the entire data range, including the formula cells. Go to Data, then Filter, then enable the filter function. 

How to find duplicate email addresses in Excel?

To find and remove duplicate email address data in Excel using VLOOKUP, you can use the same technique as mentioned above. 
For more tips and tricks about how to use VLOOKUP to identify and remove duplicate data and more, follow our regularly updated blog.

---