# How to find duplicates in Excel between two columns

Finding **duplicates** in **Excel** between two columns can be a useful task when working with large datasets. **Duplicates** can occur when data is entered multiple times, or when data is imported from multiple sources. In this tutorial, we will explore two methods to find **duplicates** in **Excel** between two columns.

Here we have a dataset, in this dataset, we have two columns **Column A** and **Column B** which contain duplicates that we need to find. In this tutorial, we will learn how to find duplicates in Excel between two columns but first, let’s take a look at the **Dataset**.

## Method – 1 Using Conditional Formatting.

## Step – 1 Select the columns.

- Select the cells in both columns in which you want to find the duplicates.
- Click the
**Home**tab in the Excel ribbon and select the**Conditional Formatting**command from the**Styles**group. - From the drop-down menu, select
**Highlight Cell Rules**and then click**Duplicate Values**.

## Step – 2 Finding the duplicates.

- In the
**Duplicate Values**dialog box, check if**Duplicate**is selected. - In the
**Values**box and**Light Red Fill with Dark Red Text**is selected in the**Format with**box. - Click
**OK**to apply the formatting.

## Method – 2 Using COUNTIF Function.

## Step – 1 Write the formula.

- Select a blank to write the formula.
- The syntax of the formula will be

**=COUNTIF(First_Column_Range,Cell_In_Second_Column)**

- In our case the formula will be

**=COUNTIF(A:A,B2)**

where **A: A** is the first column of data and **B2** is the first cell in the second column of data.

## Step – 2 Finding the duplicates.

- Press
**Enter**to apply the formula to the cell. - Drag the fill handle of the cell down to apply the formula down to the end of the column.
- Any cell with a value of 1 or greater indicates a duplicate between the two columns.

## Method – 3 Using Equal operator.

## Step – 1 Write the formula.

- Select a blank cell where you want to write the formula.
- The syntax of the formula will be

**=IF(First_Cell_Address=Second_Cell_Address,”Found”,”Not Found”)**

- In our case the formula will be

**=IF(A2=B2,”Found”,”Not Found”)**

## Step – 2 Finding the duplicates.

- Press
**Enter**to apply the formula to the cell. - Drag the fill handle of the cell down to apply the formula down to the end of the column.

## Method – 4 Using LookUp Function

We can also use VLOOKUP to find the duplicates between two columns. In VLOOKUP, we will use the entries of fist column as the lookup keys and search them in the second column. If a match is found the matching name of the fruit will be shown, otherwise Not Found will be displayed.

## Step – 1 Write the formula.

- Select a blank cell to write the formula.
- Syntax of the formula will be

**=IFERROR(VLOOKUP(First_Column_Cell,Second_Column_Range,1,0),”Not Found”)**

- In our case the formula will be

**=IFERROR(VLOOKUP(A2,$B$2:$B$7,1,0),”Not Found”)**

## Step – 2 Finding the duplicates.

- Press
**Enter**to apply the formula to the cell. - Drag the fill handle of the cell down to apply the formula down to the end of the column.
- The formula will be implemented and you’ll see that matching values with respect to the first column entries will be displayed when a match is found. In case of no match,
**“No Match”**will be displayed as shown above.