Find Matches or Duplicate Values in Excel (8 Ways)

In Microsoft Excel, there are several suitable methods to find matches or duplicate values with formulas or VBA. You can extract data based on the duplicates or matches, highlight the duplicate values, perform calculations, and so on. In this article, you’ll get to learn all handy methods to find matches or duplicate values with proper illustrations.

Read more: How to Find and Highlight Duplicates in Excel (3 Handy Ways)

Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.

8 Suitable Ways to Find Matches or Duplicate Values in Excel

1. Finding Matches or Duplicate Values in Single Column

At first, we’ll find matches or duplicates in a single or similar column. We’ll find the number of occurrences, display the customized messages for duplicate values and extract data based on duplicate values too in this section.

1.1 Using COUNTIF to Count Matches or Duplicate Values in Excel

Here in Column B, there are several names and some of them are with repetitions. We’ll find out the number of occurrences for each name individually in Column C.

📌 Steps:

➤ Select the output Cell C5 and type:

➤ Press Enter and the function will return 4, which means the first name in Column B– ‘Andrew’ is present 4 times in that column.

➤ Now use Fill Handle to autofill the rest of the cells in Column C and you’ll get the total occurrences of all names individually.

1.2 Keeping the First Occurrence Only While Finding Matches or Duplicate Values

Assuming that we want to show the message-’Duplicate’ starting from the second occurrence of every name and leave the first one blank.

📌 Steps:

➤ Select Cell C5 and type:

➤ Press Enter, fill down the entire column and you’ll find the message-’Duplicate’ for every second and further occurrence of each name present in Column B.

1.3 Extracting Data Based on Only Duplicate Values in Excel

Now in the following dataset, the sales units for each salesman in successive four days are present. We want to know the sales units of one salesman only, let’s say it’s Bob, for successive four days. It means we have to find the duplicates of the name- ‘Bob’ in Column C first and based on the findings, we have to extract his sales units.

📌 Step 1:

➤ Create a table for the range of cells- C4:D16 with headers by selecting the ‘Format As Table’ drop-down from the Styles group of commands.

📌 Step 2:

➤ In Cell G5, type the following formula:

➤ Press Enter and you’ll get the first sales values of Bob.

📌 Step 3:

➤ Use Fill Handle to fill down Column G until you get the error value.

➤ Thus you’ll get all the sales value in units for Bob.

Similarly, you can extract the sales units for any other salesperson by replacing the name Bob in Cell G4.

2. Finding Matches or Duplicate Values within Similar Rows in Two Columns in Excel

In the 2nd 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 6.

2.1 Using Equal Sign as Logical Argument to Detect Duplicates within Similar Rows

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

2.2 Using IF Function to Find Duplicate Values within Same Rows

By using the logical function- IF, you can find and 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 and you’re done.

2.3 Applying Conditional Formatting to Highlight Matches within Same Rows

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:

➤ Select the Format option and 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 and 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 and you’re done.

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

3. Finding Matches or Duplicate Values within Any Rows in Excel

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

Read more: Excel Formula to Find Duplicates in One Column

📌 Step 1:

➤ Select the range of cells first.

📌 Step 2:

➤ Under the Home tab and 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 and you’re done.

Like the picture below, you’ll see all the matches with the selected and highlighted colors…

3.2 Combining IF and COUNTIF Functions to Detect Duplicates

We can use IF and 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 and 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.

3.3 Using IF, AND, COUNTIF Functions to Find Specific Duplicate Values

Now if you want to type a name and see if that is present in both columns then this method is the best suited. In Cell F8, the name ‘Kyle’ has been written and if the name is present in both Columns B and 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 and you’ll see it’s showing YES, so the name ‘Kyle’ is present in both Columns B and C.

3.4 Combining IF, ISERROR. MATCH Functions to Find Matches or Duplicates

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 and 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 and you’ll find the results at once.

4. Finding Matches or Duplicate Values in More Than Two Columns in Excel

4.1 Using IF-AND Functions to Find Duplicates within Same Rows

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 and 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 and you’ll get all the matches in the same rows right away.

4.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 and you’ll get all the matches found within the same rows.

5. Extracting Data Based on Matches in Excel

5.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 and C represent the names of some people with the amounts of their donations. In Column E, a few names are present and we’ll find the donations of those few people in Column F by finding duplicates in two Columns- B and E.

📌 Steps:

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

➤ Press Enter, autofill the entire column and 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 and you’re done.

5.2 Inserting Wildcard Characters inside VLOOKUP or INDEX-MATCH Functions to Extract Data Based on Partial Matches

Now we have full names in Column B and with short names in Column E, we’ll search for the partial matches in Column B and then extract the donation amounts of the selected people in Column F. We have to use Wildcard Characters (Asterisk: *) here before and 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 and 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 and use the Fill Handle to fill down the entire column.

6. Finding Case-Sensitive Matches or Duplicates in Excel

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

7. Pulling Out Top Five Values with Duplicates or Matches in Excel

In the following table, there are few duplicates in the column of Marks. Now we’ll draw out the top 5 names who have got the highest marks.

📌 Step 1:

➤ Create a Helper Column beside the original table.

➤ Now select Cell D5 and type:

➤ Use Fill Handle to autofill the entire Column D.

You’ll get all the original marks with the added values between 0.01 and 0.99.

📌 Step 2:

➤ Create an output table with Rank and Top 5 headers.

➤ Put the numbers 1 to 5 in the column of Rank.

➤ Now select Cell G8 and type:

➤ Press Enter and autofill the next four cells up to G12 and you’ll get the top five names who have got the highest marks.

8. 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 and you’re done with your steps.

You’ll be displayed the matches in column D like in the picture below.

Concluding Words

I hope, all these methods mentioned above to find matches or duplicate values 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

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

How to Find & Remove Duplicate Rows in Excel

How to Highlight Duplicate Rows in Excel (3 Ways)

How to Find Matching Values in Two Worksheets in Excel (4 Methods)

Excel Top 10 List with Duplicates

Excel Find Similar Text in Two Columns

How to Compare Rows in Excel for Duplicates

Finding out the number of duplicate rows using COUNTIF formula