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.

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.

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

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.

 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.

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.

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.

Now drag it down to the last of this column.

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

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.

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.

Drag the formula to the last cell of the column.

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.

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

Drag it down to the last cell.

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

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).

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.

Drag down the formula to the rest of the cells.

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

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

Drag down the formula to the rest of the cells.

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

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)

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.

Here we have found the duplicate value.

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

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.  

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

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.

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

Write the formula for the rest of the values.

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.

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

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

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).   

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

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.