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)**

**Table of Contents**hide

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

`=COUNTIF($B$5:$B$15, B5)`

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

`=IF(COUNTIF($B$5:B5,B5)>1,"Duplicate","")`

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

`=IF(ROWS(G$5:G5)<=COUNTIF(Table1[Salesman],G$4),INDEX(D$5:D$16,SMALL(IF(C$5:C$16=G$4,ROW(Table1[Salesman])-4),ROWS(G$5:G5))),””)`

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

`=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.

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

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

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

`=$B5=$C5`

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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:

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

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

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

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:

`=EXACT(B5,C5)`

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

`=C5+RAND()`

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

`=INDEX($B$5:$B$15,MATCH(LARGE($D$5:$D$15,F8),$D$5:$D$15,0))`

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