Excel Hacks: Uncovering Duplicates with VLOOKUP

Photo of author
Written By Chris

Just a simple "data guy". Changing the world one formula at a time.

Duplicates in data are a foregone conclusion, no matter the industry you’re in. Identifying them is usually your first step before diving into a new project.

If you’re tackling a single worksheet, handling duplicate entries is easy enough with conditional formatting or the “Removing Duplicates” option under the Data tab. But what about finding duplicates when working with multiple Excel files or worksheets?

This is where the VLOOKUP formula comes in and, once again, proves itself an indispensable asset in your Excel toolbox.

How to Find Duplicate Values in Excel Using VLOOKUP

To find duplicate values in Excel using VLOOKUP, you can use a formula to search for a value in a range and return a result if the value is found. Then, you can apply conditional formatting to highlight the cells with duplicate values or add a nested function to customize the output.

The simple VLOOKUP formula structure to return duplicate values is:

vlookup anatomy

But that may not help truly understand how this operation works. So keep reading to learn more about what you can do with the duplicates you find. I’ve got three simple steps you can use to customize your VLOOKUP for the better.

Download the sample data if you want to follow along.

Highlight Your Duplicates With Conditional Formatting

In the sample below, I’ve got a list of NBA players who ended up on the injured list in 2021. The first worksheet was for all players injured on the Philadelphia 76ers during that time. The second worksheet is all injured players in 2021 on other teams in the NBA. 

We’re going to play General Manager for a day and want to figure out if any players in 2021 were injured for multiple teams. As GM for the 76ers, the Philadelphia player list is our source and where the VLOOKUP begins.

Start by entering the following VLOOKUP formula in cell C2.

=VLOOKUP(A2,'2021 Non-76ers Injury List'!$A$2:$A$555,1,0)

What this does is use the lookup value in A2, which is the player name, and search on the second worksheet in column A for an exact match. After you hit enter, drag the formula down to the remaining cells.

If you aren’t comfortable with all the arguments of a VLOOKUP, check out a recent article of mine where I walk you through the proper syntax and structure to follow.

If you entered the function correctly, you should see an output like the one below.

vlookup output for injured team

Congratulations, you found yourself five duplicates using VLOOKUP. Now what?

Depending on the file you’re working on, you may want to delete them. So go ahead, and your job is done. But as GM of the 76ers, we need this information for our medical team and scouting department 😉.

Just to clean it up a bit, let’s create a nested function by adding IFNA to cell C2 and copying it down.

=IFNA(VLOOKUP(A2,'2021 Non-76ers Injury List'!$A$2:$A$555,1,0),"")

Now our formula shows us what our duplicates are and leaves the rest of the cells blank. And if we add a bit of conditional formatting, all the better.

conditional formatting vlookup duplicates

Pull in Additional Data

Great work! But let’s be honest. Simply pulling in the player’s name doesn’t do a whole lot. Instead, let’s let our scouting department know what other team it was the player played for. 

To do this, we have to modify our VLOOKUP in Excel and expand the range it’s looking at. The easiest way is to click on the formula bar after you’ve selected cell C2, change your table array to correspond with the desired data, and adjust the column index accordingly.

It may sound like a lot, but I promise you it’s simple. I’ll walk you through it for the visual learners.

For starters, we know that our second worksheet has only two columns of data, found in columns A and B. As data sets go, this is pretty easy to work with. Right now, our VLOOKUP is only searching in column A. Now, we’ll expand it to column B, also known as the Team column.

adjust vlookup to expand range for new team

Remember to keep the beginning of your table array set to column A since that’s where your lookup value is found. All we did was change the end of the array from $A$555 to $B$555.

Next, change the third argument of your VLOOKUP, the column index number, from 1 to 2. This tells Excel to look up and find the match in column A of the second sheet and bring back whatever pairs to it in column B, the 2nd column.

=IFNA(VLOOKUP(A2,'2021 Non-76ers Injury List'!$A$2:$B$555,2,0),"")

It’s your choice if you want to remove or change the conditional formatting. Because we set it to values not equal to blanks, the colors will still apply.

pulled in teams with conditional formatting

Customize Your Output Value

Ok. Down to the last method I wanted to show you. Another way to deal with duplicates you found in Excel with a VLOOKUP is to customize the value returned when you execute the formula.

We already did this when we used the IFNA function to return a blank cell if no match was found. Now let’s customize it to leave a note for our medical staff on the player.

If you can’t tell, I’m really digging this basketball GM thing!

Changing the returned output value is a simple switch of the IFNA to the IF(ISNA) nested formula. The difference here is that the IF(ISNA) function allows for two arguments instead of the one provided by IFNA.

In other words, the new customization lets us change the value if there is a match, not just when an error is returned.

Here’s the completed formula.

completed duplicate vlookup with adjusted output

You can see that the VLOOKUP portion stays the same. The ISNA allows us to leave the cell blank if there’s an error (or no match). And the IF statement at the beginning ensures we can add the last argument, telling Excel what the value should be if a match is found.

final customization of NBA injury VLOOKUP in Excel

And there you have it! There are other ways to accomplish this. That’s what’s great about Excel. You aren’t limited to just one option (most of the time).

I love using VLOOKUPs. There isn’t a day in the week that I don’t put it to use. And finding duplicates in Excel is just another example of its ability.

Leave a Comment