How to Compare 3 Columns for Matches in Excel (4 Methods)

In the case of analyzing data in Excel, mutual comparison between columns or lists is one of the most convenient approaches. There are multiple ways available whether you need to compare two or more columns. But we will be quite specific in terms of the number of comparable columns throughout the article. You will be learning 4 distinct methods to compare 3 columns for matches in Excel, all step by step.


Download the Practice Workbook

You are recommended to download the Excel file and practice along with it.


4 Methods to Compare 3 Columns for Matches in Excel

We have used a sample yearly revenue statement to demonstrate 4 methods to compare 3 columns for matches in Excel. We will try to find the matchings among columns C, D, and E while discussing the methods below.

yearly revenue statement sample dataset

So, without having any further discussion let’s jump straight into the methods one by one.


1. Compare 3 Columns in Excel for Matches Using the IF Function along with AND Function

You can combine both the IF function along with the AND function to run a search operation to find the matches and identify them with specific works like “Match”, “No Match”, “Yes”, “No”, “True”, “False”, etc. Anyways, follow the steps below to see how it works. Let’s go:

🔗 Steps:

At first, select Cell F5 to store the matching result.

Then, type

=IF(AND(C5=D5,D5=E5),"Yes","No")
in the cell.

After that, press the ENTER button.

compare 3 columns in excel for matches using If with AND function

Now, drag the Fill Handle icon to the end of column F.

That’s it.

fill handle


2. Highlight the Matching Data by Juxtaposing 3 Columns in Excel by Setting Up New Rule

You can set up a New Rule and format matched records in Excel. However, here are the steps that will help you learn this method. Follow accordingly:

🔗 Steps: 

First of all, select the whole dataset.

Then, go to the Home ribbon.

And click on the Conditional Formatting.

From the drop-down menu, select New Rule.

Highlight the Matching Data by Juxtaposing 3 Columns in Excel by Setting Up New Rule

At this point, the New Formatting Rule window will pop up.

From then popped up window, select Use a formula to determine which cells to format.

After that type

=AND($C5=$D5,$D5=$E5)
within the Format values where this formula is true: box.

Now, press the OK button.

New formatting rule

When you are done with all the previous steps, you will get all the matched data is highlighted.


Similar Readings:


3. Compare 3 Columns for Matches in Excel Using IF with COUNTIF Function

In this section, we have incorporated the IF function and the COUNTIF function to find the matchings among columns C, D, and E. Moreover, we will specify the matches with “Yes” and “No” for the mismatches. Here are the steps below to follow, go ahead.

🔗 Steps:

At first, select Cell F5 to store the matching result.

Then, type

=IF(COUNTIF(C5:D5,C5)+COUNTIF(D5:E5,E5)=2,"No","Yes")
in the cell.

After that, press the ENTER button.

Compare 3 Columns for Matches in Excel Using IF with COUNTIF Function

Now, drag the Fill Handle icon to the end of column F.

That’s it.

fill handle


4. Highlight the Matched Records by Scanning 3 Columns in Excel

You can use the Duplicate Values option under the Conditional Formatting option to highlight the matching records in Excel. In this regard, follow the steps below that will help guide you.

🔗 Steps: 

First of all, select columns C, D, and E.

Then, go to the Home ribbon.

And click on the Conditional Formatting.

After that navigate to the Highlight Cells Rules option.

Now select the Duplicate Values… option from the drop-down list.

Highlight the Matched Records by Scanning 3 Columns in Excel

Thus a new window named Duplicate Values will open. Hit the OK button from it.

As soon as you are done with all the previous steps, you will get all the matched records being highlighted like this:


Things to Remember

📌 Always be careful while inserting range within the formulas.

📌 Select the dataset first before moving into Conditional Formatting.

📌 Don’t forget to pick up the color to format cells while using Conditional Formatting.


Conclusion

To sum up, running comparisons in between columns to find matches or mismatches is one of the most convenient tools to analyze data in Excel. Getting the fact, we have facilitated you with four super easy methods that you can use to compare 3 columns in Excel for matches. You are recommended to practice them all along with the attached Excel file and find your best convenient one.


Read More

 

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

2 Comments
  1. Hi Mrinmoy, thank you for your excel tips. I need help to compare three columns between multiple sheets and find the same values and give me yes or no result. Please can you help. Thanks.

    • Hello Mr. Masud,
      You can easily solve your problem by combining the IF and AND functions.
      Suppose, you have 3 values to compare in three cells C7, D7, and E7. For this instance, let’s say C7 is in sheet1, D7 is in sheet2, and E7 is in sheet3.
      Now you are in sheet3 and you want to get a feedback (Yes or No) in cell F7.
      All you need to do is, type the following formula in cell F7 of sheet3.
      =IF(AND(Sheet1!C7=Sheet2!D7,Sheet2!D7=Sheet3!E7),”Yes”,”No”)
      After that, press ENTER and you will get your required result.
      Regards!

Leave a reply

ExcelDemy
Logo