VLOOKUP to Find Duplicates in Two Columns

You may need to compare two columns in Excel to find duplicates or unique. In this article, we are going to show you how to find duplicates from two columns using VLOOKUP. For this session, we are using Excel 2019, you can use your preferred version.

Before diving into the session, let’s get to know about the workbook which is the base of our example.

Dataset - VLOOKUP to Find Duplicates in Two Columns

Here we have two columns where several athletes are listed. Since our agenda is to find duplicates from two columns there are some duplicate values listed on purpose.

Note that this is a simple dataset to keep things straightforward. In real life scenario, you may encounter a much larger and complex dataset.

Practice Workbook

You are welcome to download the practice workbook from the link below.

Find Duplicates in Columns using VLOOKUP

We will compare the two columns and set the result in the newly introduced Result column.

Result column - VLOOKUP to Find Duplicates in Two Columns

1. Conventional VLOOKUP function

Whenever we hear vlookup, most of the time the first thing that comes to mind is the VLOOKUP function. We can use this function to find the duplicates within two columns.

Feel free to go through this VLOOKUP article to know about the function.

The VLOOKUP Formula

The formula for comparing two columns to find duplicates will be something like the following one

VLOOKUP(column1, column2,True,False)

This is the generic formula, it forms in such a way that the first column is set at the lookup_value field while the second column is at the lookup_array.

Now let’s write the formula using the cell reference from our example data.

 =VLOOKUP($B$4:$B$12,$D$4:$D$10,TRUE,FALSE)

VLOOKUP Formula - VLOOKUP to Find Duplicates in Two Columns B4:B12 is the cell reference of Athlete List 1 and D4:D10 is for Athlete List 2. You may wonder why we have set TRUE at the column_num field of VLOOKUP.

Usually, we set a numeric value in this field that denotes from which column the value should be fetched. Since there is only one column we can set 1 (only 1 can be set). And Excel counts 1 as TRUE and vice versa.

VLOOKUP Formula result - VLOOKUP to Find Duplicates in Two Columns

Here the first value was a duplicate one and the formula returned the value.

This formula traverses the list at once and returns the value with respect to the row of the Athlete List 1 column since it was at the lookup_value field.

VLOOKUP Formula result 2 - VLOOKUP to Find Duplicates in Two Columns

Here Messi was in both the column and we have found it at the second position (though it was listed after another duplicate value)

For a value that is not in both columns, it will return the #N/A error.

VLOOKUP Formula result 3 - VLOOKUP to Find Duplicates in Two Columns

Now drag it down to the last of this column.

VLOOKUP Formula result drag - VLOOKUP to Find Duplicates in Two Columns

Error Free Representation

Earlier we have seen the VLOOKUP formula that returned an error for a non-duplicate value. But it’s not an ideal convention to leave the error as it is.

We need to make sure that our presentation doesn’t have any errors. To eradicate error values, we can use various functions like IFERROR, ISNA, IFNA, ISERROR.  

Let’s use IFERROR. The IFERROR function returns a custom result when a formula generates an error, and a standard result when no error is detected. To know more visit this article IFERROR.

The formula will be the following one

=IFERROR(VLOOKUP($B$4:$B$12,$D$4:$D$10,TRUE,FALSE),"")

IFERROR VLOOKUP Formula - VLOOKUP to Find Duplicates in Two Columns

Here we have inserted the VLOOKUP formula inside IFERROR. As long as VLOOKUP doesn’t return any error this will trigger, if any error occurs then return the if_error value.

Here we have left the If_error field empty.

IFERROR VLOOKUP Formula result 2 - VLOOKUP to Find Duplicates in Two Columns

As long as the duplicates are found, the formula will derive those values. But when a value will not be found then it will return an empty cell.

IFERROR VLOOKUP Formula result 2 - VLOOKUP to Find Duplicates in Two Columns

Drag the formula to the last cell of the column.

IFERROR VLOOKUP Formula result Drag down - VLOOKUP to Find Duplicates in Two Columns

In place of IFERROR, we can use IFNA. The mechanism of these two functions is a bit similar, but the IFNA function only works for #N/A. Visit the Microsoft Support site for more information.

=IFNA(VLOOKUP($B$4:$B$12,$D$4:$D$10,TRUE,FALSE),"")

IFNA-VLOOKUP formula - VLOOKUP to Find Duplicates in Two Columns

The formula is similar to the earlier one (IFERROR-VLOOKUP), and the result will be the same as well.

IFNA-VLOOKUP formula result - VLOOKUP to Find Duplicates in Two Columns

Drag it down to the last cell.

IFNA-VLOOKUP formula result 2- VLOOKUP to Find Duplicates in Two Columns

Reader Understandable Representation

So far we have seen the duplicate value derivation from the list. But it’s hard to understand for a newcomer at first sight.

To make it understandable we can use the IF and ISERROR function along with the VLOOKUP.

To know about these helper functions, visit these articles: IF, ISERROR.

Let’s see the formula

=IF(ISERROR(VLOOKUP($B$4:B$12,$D$4:$D$10,TRUE,FALSE)),"","Duplicate Entry")

ISERROR VLOOKUP Formula - VLOOKUP to Find Duplicates in Two Columns

Here we have checked whether the VLOOKUP portion returns an error or not through ISERROR. It returns TRUE or FALSE. When VLOOKUP returns an error then the ISERROR function produces TRUE and FALSE otherwise.

We have set “Duplicate Entry” at the if_false_value (because for duplicate values, ISERROR will return FALSE).

ISERROR VLOOKUP Formula result- VLOOKUP to Find Duplicates in Two Columns

Since we have left the if_true_value empty when the value is not a duplicate one then the result will be an empty cell.

ISERROR VLOOKUP Formula result 2 - VLOOKUP to Find Duplicates in Two Columns

Drag down the formula to the rest of the cells.

ISERROR VLOOKUP Formula result 3- VLOOKUP to Find Duplicates in Two Columns

Instead of the ISERROR, we can use the ISNA function. It performs a similar operation to the ISERROR function, but only for #N/A. To know about the function, visit this ISNA article.

So our formula will be

=IF(ISNA(VLOOKUP($B$4:B$12,$D$4:$D$10,TRUE,FALSE)),"","Duplicate Entry")

ISNA - VLOOKUP Formula - VLOOKUP to Find Duplicates in Two Columns

We have replaced ISERROR with ISNA. This change will not affect the ultimate result.

ISNA - VLOOKUP Formula result - VLOOKUP to Find Duplicates in Two Columns

Drag down the formula to the rest of the cells.

ISNA - VLOOKUP Formula result drag down- VLOOKUP to Find Duplicates in Two Columns

2. Combination of IF MATCH performs Vlookup

Apart from the use of the conventional VLOOKUP function, we can use the MATCH function with IF that will operate similarly to VLOOKUP.

Feel free to go through the MATCH article, if required.

The Formula

Unlike VLOOKUP we need to insert a single cell in the lookup_value field at a time. Let’s see the formula

=IF(MATCH(B4,$D$4:$D$10,0)>0,B4,"")

For simplicity here we are checking whether the value from Athlete List 1 has a duplicate entry within the Athlete List 2 column (the lookup_array)

IF MATCH Formula - VLOOKUP to Find Duplicates in Two Columns

You know that MATCH returns the location when it finds the value. The location value is always greater than 0. So we checked whether MATCH returns any value greater than 0 or not within IF.

At the if_true_value field, we have set the cell name so that we can get the value when it is a duplicate one.

IF MATCH Formula result - VLOOKUP to Find Duplicates in Two Columns

Here we have found the duplicate value.

This formula will return #N/A when there is no duplicate.

IF MATCH Formula result 2- VLOOKUP to Find Duplicates in Two Columns

You may think why not this return empty since we have set an empty string at the if_false_value field.

When MATCH can not locate the position then it returns #N/A and the logical operation can not recognize it as either greater or less than 0. So, returns this value.

Write the formula for the rest of the values.  

IF MATCH Formula result AutoFill- VLOOKUP to Find Duplicates in Two Columns

Reader Understandable Representation

Hope you have understood that the error contains result is not our ultimate goal. We need to eradicate the errors. In the earlier section, we have seen how to do so using IFERROR or IFNA. This time we are leaving that for you.

Let’s represent the result in an understandable way using the ISNUMBER function. To know about the function, visit this ISNUMBER article.

The formula will be the following one

  =IF(ISNUMBER(MATCH(B12,$D$4:$D$10,0)),"Duplicate Entry","Unique")

ISNUMBER - MATCH Formula - VLOOKUP to Find Duplicates in Two Columns

The ISNUMBER checks whether MATCH returns a number or not. If it returns a number then ISNUMBER will return TRUE and FALSE otherwise (for #N/A).

The if_true_value or if_false_value will be returned based on the inside output.

ISNUMBER - MATCH Formula result - VLOOKUP to Find Duplicates in Two Columns

Here we have set “Unique” as the if_false_value. For a non-duplicate value, we will find this as our result.

ISNUMBER - MATCH Formula result 2 - VLOOKUP to Find Duplicates in Two Columns

Write the formula for the rest of the values.

ISNUMBER - MATCH Formula result Autofill- VLOOKUP to Find Duplicates in Two Columns

When Columns in Different Sheets

So far we have seen examples where columns are in the same sheet. Circumstances may arise when two columns can be on different sheets.

List 1 - VLOOKUP to Find Duplicates in Two Columns

Here the Athlete List 1 column is in the List 1 sheet and the Athlete List 2 column is in the List 2 sheet

List 2 - VLOOKUP to Find Duplicates in Two Columns

To compare the columns you need to use the sheet name ahead of the column reference within our formula.

=IF(ISNA(VLOOKUP('List 1'!$B$4:$B$12,$B$4:$B$10,TRUE,FALSE)),"","Duplicate Entry")

Different sheet formula - VLOOKUP to Find Duplicates in Two Columns

Here we have listed the Result column on the List 2 sheet. That’s why we don’t need to name the List 2 sheet explicitly.

Hope you have understood the functionality of this formula (we have used it earlier).   

Different sheet formula result 1- VLOOKUP to Find Duplicates in Two Columns

You will find the desired result. Drag down the formula to the rest of the cells

Different sheet formula result 2- VLOOKUP to Find Duplicates in Two Columns

Conclusion

That’s all for today. We have listed several ways to find duplicates in two columns using VLOOKUP. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other methods which we might have missed here.

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo