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

Get FREE Advanced Excel Exercises with Solutions!

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.


Compare 3 Columns for Matches in Excel: 4 Efficient Methods

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 the 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 highlighted.


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.


Download the Practice Workbook

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


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 most convenient one.


<< Go Back to Columns | Compare | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

4 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!

  2. Hello Mrinmoy, thank you for your excel tips. I need help to solve this problem. There are two workbooks containing numbers in column A and some other value in column Z against these numbers in each row, in both the workbooks. I need to compare the numbers in column A in both workbooks and if they match, copy values from column Z in first workbook against column A numbers in second workbook, wherever numbers in column A of both workbooks match. I would highly appreciate, if you could help me in this regard.

    • Hello Murali,

      Unfortunately, I find your query a little confusing. It would’ve been much better if you had explained it with sample data and desired outputs.

      As far as I understand, you need to compare column A in Book1 to column A in Book2. So, you want to create a formula in Book2 so that, if there is a match, it will return the corresponding value from column Z in Book1.

      You can apply the following formula to do that. Then copy the formula down.
      =IF(Sheet1!A1=[Book1.xlsx]Sheet1!A1,[Book1.xlsx]Sheet1!Z1,"")

      Is this what you wanted? I’ve also emailed you the Excel documents. Please check.

      Thanks for being with us.

      Regards,
      Md. Shamim Reza (ExcelDemy Team)

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo