Find Duplicates in Two Columns in Excel (6 Suitable Approaches)

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.

Find Duplicates in Two Columns in Excel: 6 Suitable Approaches

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:

➤ 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:

➤ Press Enter.

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

Read More: Excel Formula to Find Duplicates in One Column

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.

Read More: How to Find Duplicates in Excel Workbook

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.

Read More: How to Find Similar Text in Two Columns in Excel

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:

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

Read More: How to Find Duplicate Values Using VLOOKUP in Excel

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:

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

Read More: How to Find Duplicates in Two Different Excel Workbooks

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:

➤ 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:

➤ 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:

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

Read More: How to Find Duplicates without Deleting in Excel

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:

➤ 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:

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:

➤ 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:

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

Read More: Excel Find Duplicates in Column and Delete Row

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:

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

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.

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.

<< Go Back to Find Duplicates in Excel Column | Find Duplicates in Excel | Duplicates in Excel | Learn Excel