Microsoft Excel has provided numerous methods to find duplicates in two or more columns. In this article, you’ll learn all simple & useful tricks along with the VBA editing method to find duplicates or matches in two columns in Excel.

The above screenshot is an overview of the article which represents the dataset & an example of the function to find duplicate values. You’ll get to learn more about the dataset along with all suitable functions in the following methods in this article.

**Download Practice Workbook**

You can download our Excel workbook that we’ve used to prepare this article. You’ll find the worksheets with practice sections.

**6 Suitable Approaches to Find Duplicates in Two Columns in Excel**

**1. Finding Duplicates within Similar Rows in Two Columns**

In the 1st section, we’ll find out the duplicates within the same rows only in two columns. But these methods are not applicable for case-sensitive matches. If you need to find exact matches with considering letter cases right now, then we have a solution for this too in method 5.

**1.1 Using Equal Sign as Logical Argument to Detect Duplicates within Similar Rows in Two Columns**

We have two lists of names in **Columns B & C**. And we can find duplicates within the same row by simply using the equal sign as a logical function.

**📌**** Steps:**

➤ Select **Cell D5** & type:

`=B5=C5`

➤ Press **Enter**, you’ll get the 1st return value. If matches are found, the value will return as **TRUE** & if not found, it will return as **FALSE**.

➤ Now use **Fill Handle **to autofill the rest of the cells in **Column D** to find all matches.

**1.2 Using IF Function to Find Duplicates within Same Rows in Two Columns**

By using the logical function-** IF**, you can find & show the duplicates in another column.

**📌**** Steps:**

➤ In **Cell D5**, the formula will be:

`=IF(B5=C5,B5,"")`

➤ Press **Enter**.

➤ Use **Fill Handle** to autofill other cells in **Column D **& you’re done.

**1.3 Applying Conditional Formatting to Highlight Matches within Same Rows in Two Columns**

If you want to find duplicates within the same rows in two columns then probably conditional formatting suits best to highlight the matches and it won’t let you type any function to search for matches.

**📌**** Step 1:**

➤ Select the whole range of cells that is considered for finding duplicates.

**📌**** Step 2:**

➤ Under the **Home** tab, from the drop-down of **Conditional Formatting** in the **Styles** group of commands, select the **New Rule** command. A dialogue box will appear.

**📌**** Step 3:**

➤ Now select the rule type to use a formula to determine within cells to format.

➤ In the **Rule Description** editor, type **=$B5=$C5**

➤ Select **Format** option & another dialogue box will appear.

**📌**** Step 4:**

➤ From the **Fill** tab, select a color that you want to use for highlighting the duplicates.

➤ Press **OK** & you’ll be shown the Sample format with selected color in the **New Formatting Rule** dialogue box.

**📌**** Step 5:**

➤ Press **OK** for the last time & you’re done.

In the picture below, the matches in the same rows are now visible with the selected color.

**2. Finding Duplicates within Any Rows in Two Columns**

**2.1 Applying Conditional Formatting to Locate All Duplicates in Two Columns**

To find duplicates in two columns in any rows, you can directly use the related command in **Conditional Formatting** for highlighting.

**📌**** Step 1:**

➤ Select the range of cells first.

**📌**** Step 2:**

➤ Under the **Home **tab & from the **Conditional Formatting** drop-down, select **Duplicate Values** from the **Highlight Cells Rules**. A dialogue box will appear.

**📌**** Step 3:**

➤ Select the color you want to show for highlighting the duplicates.

➤ Press **OK** & you’re done.

Like the picture below, you’ll see all the matches with the selected & highlighted colors.

**2.2 Combining IF & COUNTIF Functions to Detect Duplicates in Two Columns**

We can use** IF & COUNTIF** functions together to find data from the 1st column in the 2nd column for matches.

**📌**** Steps:**

➤ In **Cell D5**, we have to type the following formula:

`=IF(COUNTIF($C$5:$C$15,$B5)=0,"",$B5)`

➤ Press** Enter **& then use **Fill Handle** to autofill the rest of the cells in **Column D**. Thus you’ll get all the names from List 1 that are present in List 2.

**2.3 Using IF, AND, COUNTIF Functions to Find Specific Duplicate in Two Columns**

Now if you want to type a name & see if that is present in both columns then this method is the best suited. In **Cell F8**, the name ‘Kyle’ has been written & if the name is present in both **Columns B & C**, then the output will show the message- **YES**, otherwise it’ll return as **NO**.

**📌**** Steps:**

➤ The related formula in **Cell F9** will be:

`=IF(AND(COUNTIF(B5:B15,F8),COUNTIF(C5:C15,F8)),"YES","NO")`

➤ Press **Enter** & you’ll see it’s showing **YES**, so the name ‘Kyle’ is present in both **Columns B & C**.

**2.4 Combining IF, ISERROR. MATCH Functions to Find Matches in Two Columns**

If you want to use the **MATCH** function to find matches or duplicates in two columns within any rows, then you have to insert **ISERROR **within **IF & MATCH** functions otherwise if a match/duplicate is not found you’ll be shown an Error message.

**📌**** Steps:**

➤ In **Cell D5**. the formula with **MATCH **function will be:

`=IF(ISERROR(MATCH($B5,$C$5:$C$15,0)),"",$B5)`

➤ Press **Enter**, autofill the entire column by using** Fill Handle** & you’ll find the results at once.

**3. Finding Duplicates in More Than Two Columns**

**3.1 Using IF-AND Functions to Find Duplicates within Same Rows in More Than Two Columns**

To find matches or duplicates in more than two columns, we have to use **AND** function to add multiple logics. So, in our modified dataset, now we have another column (List 3) with more names & we’ll find all the matches within the same rows in **Column E**.

**📌**** Steps:**

➤ In **Cell E5**, the formula based on the criteria will be:

`=IF(AND(B5=C5,C5=D5),B5,"")`

➤ Press **Enter**, autofill the rest of the cells with **Fill Handle** & you’ll get all the matches in the same rows right away.

**3.2 Using IF-OR Functions to Find Duplicates within Similar Rows in Any Two of Multiple Columns**

Now here’s another case where we’ll find the duplicates in any of the two columns within the same rows from more than two columns and if the matches are found then the message will show ‘Found’, otherwise it’ll return as a blank.

**📌**** Steps:**

➤ We have to type in **Cell E5**:

`=IF(OR(B5=C5,C5=D5,D5=B5),"Found","")`

➤ Press **Enter**, autofill the rest of the cells in the column & you’ll get all the matches found within the same rows.

**4. Extracting Data Based on Duplicates in Two Columns**

**4.1 Using VLOOKUP or INDEX-MATCH to Extract Data Based on Duplicates in Two Columns**

Based on the duplicates found in two columns, we can also pull out data with **VLOOKUP **or **INDEX-MATCH **formulas. In our modified dataset, **Columns B & C** represent the names of some people with the amounts of their donations. In **Column E,** a few names are present & we’ll find the donations of those few people in **Column F** by finding duplicates in two **Columns- B & E**.

**📌**** Steps:**

➤ In **Cell F5**, the related formula with **VLOOKUP** will be:

`=VLOOKUP(E5,$B$5:$C$15,2,FALSE)`

➤ Press** Enter**, autofill the entire column & you’ll get the donation amounts of those selected people from **Column E**.

You can also use the** INDEX-MATCH** formula here to find similar results. In this case, the formula in **Cell F5 **will be:

`=INDEX($B$5:$C$15, MATCH($I5,$B$5:$B$15,0),2)`

Then press **Enter**, autofill the entire column & you’re done.

**4.2 Inserting Wildcard Characters inside VLOOKUP or INDEX-MATCH Functions to Extract Data Based Partial Matches in Two Columns**

Now we have full names in** Column B** & with short names in **Column E**, we’ll search for the partial matches in **Column B** & then extract the donation amounts of the selected people in **Column F**. We have to use **Wildcard Characters (Asterisk-’*’) **here before & after the cell references from **Column E** as **Asterisk(*)** will search for the additional texts.

**📌**** Steps:**

➤ The related formula in **Cell F5** will be:

`=VLOOKUP("*"&E5&"*",$B$5:$C$15,2,FALSE)`

➤ Press **Enter**, autofill the entire column & you’ll find the results at once.

And if you opt to use **INDEX-MATCH** functions, then you have to type in **Cell F5**:

`=INDEX($B$5:$C$15, MATCH("*"&$I5&"*",$B$5:$B$15,0),2)`

Then press** Enter **& use the **Fill Handle** to fill down the entire column.

**5. Finding Case-Sensitive Duplicates in Two Columns**

All methods mentioned above were case-insensitive. Now if you want to find duplicates in two columns in the same rows with case Case-Sensitive on, then this section will show you the way. Here, in two columns of List 1 & 2, some names are present in both columns but not with similar case letters. With the **EXACT** function, we’ll find out which names are the same with considering the case-sensitive on.

**📌**** Steps:**

➤ In **Cell D5**, the formula will be:

`=EXACT(B5,C5)`

➤ Press **Enter**, autofill the rest of the cells by using **Fill Down **& you’ll get all the exact matches with case sensitive on. The matches will be shown as **TRUE**, and the mismatched result will return as **FALSE **logical value.

**6. Using VBA Editor to Find Duplicates in Two Columns**

If you love to code with **VBA Editor** for Excel functions, then this method might be suitable for you. We are going to show the duplicates in **Column D** with the help of **VBScript**.

**📌**** Step 1:**

➤ Press **Alt+F11** to open the **VBA** window.

➤ From the **Insert** tab, select **Module**. A new module for the VBA editor will appear where you’ll type the codes.

**📌**** Step 2:**

➤ In the editor window, copy the following codes:

```
Sub Duplicates()
Dim List2 As Variant
Dim data1 As Variant
Dim data2 As Variant
Set List2 = Range("C5:C15")
For Each data1 In Selection
For Each data2 In List2
If data1 = data2 Then data2.Offset(0, 1) = data1
Next data2
Next data1
End Sub
```

➤ Click on the** Run** button or press **F5 **to activate the subroutine.

**📌**** Step 3:**

➤ Close the VBA window or press **Alt+F11** again to return to the Excel workbook.

➤ Now select the range of cells from List 1 that need to be inspected for matches in List 2.

**📌**** Step 4:**

➤ From the **Developer** tab, select **Macros**, a dialogue box will open.

**Note:** If you don’t find the **Developer **option at the top or ribbon section, then you have to enable it by opening **Excel Options** first. There you’ll find the **‘Customize Ribbon’ **option. From the **Main Tabs** option, put a Select mark on **Developer**. Press **OK** & the Developer tab should now appear at the top of your Excel workbook.

**📌**** Step 5:**

➤ As you have already activated the macro, so now this macro name will be visible in the dialogue box. Press **Run **& you’re done with your steps.

You’ll find all the matches in column D like in the picture below.

**Concluding Words**

I hope, all these methods mentioned above to find duplicates in two columns under multiple criteria will now prompt you to apply in your regular Excel chores. If you got any questions or feedback please let me know in the comment section. Or you can check out our other useful articles related to Excel functions on this website.

**You May Also Like to Explore**

**Index Match with Multiple Matches**

**Excel Count Matches in Two Columns (4 Easy Ways)**

**Excel Compare Two Cells Text (9 Examples)**