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.

find duplicates in two columns overview

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.

find duplicates in tow columns same row

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

find duplicates in tow columns same row


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.

find duplicates in tow columns same row using if


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.

find duplicates in tow columns same row with conditional formatting

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

find duplicates in tow columns same row with conditional formatting

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

find duplicates in tow columns same row with conditional formatting

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

find duplicates in tow columns same row with conditional formatting

📌 Step 5:

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

find duplicates in tow columns same row with conditional formatting

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

find duplicates in tow columns same row with conditional formatting


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.

find duplicates in tow columns with conditional formatting

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

find duplicates in tow columns with conditional formatting

📌 Step 3:

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

➤ Press OK & you’re done.

find duplicates in tow columns with conditional formatting

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

find duplicates in tow columns with conditional formatting


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.

find duplicates in tow columns by using if countif


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.

find duplicates in tow columns by using if countif


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.

find duplicates in tow columns with if iserror match


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.

find duplicates in more than two rows


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.

find duplicates in more than two 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.

extract daat based on duplicates in two columns

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

extract daat based on duplicates in two columns

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.

extract daat based on partial duplicates in two columns

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

extract daat based on partial duplicates in two columns

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.

find exact duplicates in two columns same row with case sensitive

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

find exact duplicates in two columns same row with case sensitive


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.

find duplicates in two columns with vba

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

find duplicates in two columns with vba

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

find duplicates in two columns with vba

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

find duplicates in two columns with vba

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

find duplicates in two columns with vba

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

find duplicates in two columns with vba

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

find duplicates in two columns with vba


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)

SUMPRODUCT with INDEX and MATCH Functions in Excel

INDEX, MATCH and MAX with Multiple Criteria in Excel

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo