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.

**Table of Contents**hide

## 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

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

**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

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

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.

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

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

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

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

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

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

`=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*)

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

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

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.

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

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.