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.
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")❸ After that, press the ENTER button.
❹ Now, drag the Fill Handle icon to the end of column F.
That’s it.
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.
❺ 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)❽ Now, press the OK button.
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")❸ After that, press the ENTER button.
❹ Now, drag the Fill Handle icon to the end of column F.
That’s it.
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.
❻ 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
Get FREE Advanced Excel Exercises with Solutions!
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!
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)