Farihat
Organizing data using various techniques is quite common in Microsoft Excel. Records are arranged in a tabular form here. In cases of data tracking, there are significant examples where duplicate data exist. Data analysts mostly evaluate this repetition to avoid data overlap. Multiple uses are there using this feature of Excel. This article will discuss five approaches to compare 3 columns for matches in Excel. It applies to both adjacent and non-adjacent columns and you can eventually modify it according to your requirements. All the methods have step-by-step instructions with a figurative description.
📁 Download Excel File
Download the Excel file below.
Learn to Compare 3 Columns for Matches in Excel with These 5 Suitable Approaches
The article is going to introduce five ways to compare 3 columns for matches in Excel. The following discussion will cover the uses of the IF, ABS, AND, and COUNTIF functions in three ways. Rest two methods are based on Conditional Formatting. Therefore, users will get a walkthrough of these solutions. You will find a dataset to modify in the following methods below to help you understand.
1. Using IF and ABS Functions
The most simple formula to get the duplicates is using the IF function. The ABS function gets you the positive value to complete the formula to compare 3 columns for matches in Excel.
⬇️⬇️ STEPS ⬇️⬇️
- First, select cell F6.
- Second, enter the following formula here.
- Third, hit the Enter key.
- Fourth, select cell G6.
- Fifth, copy the formulas down their cells using the Fill Handle icon.
- Sixth, you will find whether all three columns have the same values.
🔨 Formula Breakdown
IF(ABS((C6-D6))+ABS((D6-E6))=0,”Same”,””)
👉 Here, ABS returns the absolute value from the subtraction result between C6 and D6 as well as between D6 and E6.
👉 Finally, IF gets you “Same” if the addition returns zero or a blank cell otherwise.
Read More: Excel Formula: Compare Two Columns and Return a Value
2. Merging IF and AND Functions
The AND function merges two conditions or values so that both can participate at the same time. Nesting it to the IF function can help you to compare 3 columns for matches in Excel.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cell F6.
- Next, enter the following formula here.
- Then, hit the Enter key.
- Now, select cell G6.
- Here, using the Fill Handle icon, copy the formulas down to their cells.
- Finally, you will find whether all three columns have equal values.
🔨 Formula Breakdown
IF(AND(C6=D6,D6=E6),”Equal”,””)
👉 Here, AND returns the result if C6 and D6 are equal as well as if D6 and E6 are equal or not.
👉 Finally, IF gets you “Equal” if the return from AND fulfills the condition.
Read More: 4 Ways to Compare Three Columns in Excel and Return a Value
3. Combining IF and COUNTIF Functions
The COUNTIF function counts cell numbers. Counting the cell numbers and adding conditions on the return using the IF function can let you compare 3 columns for matches in Excel.
⬇️⬇️ STEPS ⬇️⬇️
- In the beginning, select cell F6.
- Now, enter the following formula here.
- Next, hit the Enter key.
- After that, select cell G6.
- Then, copy the formulas down their cells using the Fill Handle icon.
- Finally, you will find if all three columns have matches between them or not.
🔨 Formula Breakdown
IF(COUNTIF(C6:D6,C6)+COUNTIF(D6:E6,E6)=2,””,”Match”)
👉 Here, COUNTIF counts the cell numbers from C6:D6 for C6 and from D6:E6 for E6.
👉 Finally, IF gets you a blank cell if the addition returns zero or “Same” otherwise.
Read More: How to Count Matches in Two Columns in Excel
4. Using Conditional Formatting
Conditional Formatting is a handy feature in Excel. To compare 3 columns for matches in Excel, you can do it, by going to the Duplicate option only. This method can format cells that get duplicates from less than 3 columns as well.
⬇️⬇️ STEPS ⬇️⬇️
- To begin with, select the cell range C6:E9.
- Then, go to the drop-down option of Conditional Formatting from the Styles group, under the Home tab.
- After that, select the Highlight Cells Rules and the Duplicate Values option from it.
- Now, you should get a dialogue box for Duplicate Values.
- Next, select Duplicate from the first drop-down option.
- Here, the Format values with command from the second drop-down option, can be modified from the drop-down. We have continued with the default format.
- Then, hit the OK button.
- Therefore, Excel will automatically format all the duplicate data from the table like the following image.
Read More: 5 Ways to Compare Three Columns Using Vlookup in Excel
5. Formatting Rows for Specific Formula
If you need to know exactly which rows are with duplicate values and format them differently than unique ones, this is the method for you. You can even change the format style from here as well. All you need to do is select the range and copy (or type) the formula to compare 3 columns for matches in Excel eventually.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select the data range as the following image.
- Next, click on the Conditional Formatting under the Style group under the Home tab.
- Then, select the New Rule option.
- Now, you should get a dialogue box for Edit Formatting Rules.
- Here, select the Use a formula to determine which cells to format option from the Rule Type.
- Next, Enter the following formula in the Rule Description option.
- Then, you can change the format. We have continued with the default format.
- After that, hit the OK button.
- Moreover, Excel will automatically format all the duplicate data by rows from the table like the following image.
📄 Important Notes
🖊️ All the data formats must be formatted properly. Otherwise, Excel may auto-format to something inappropriate for the user.
🖊️ You can modify the formula if you want something specific if Excel finds a match.
📝 Takeaways from This Article
The article lets the readers understand the variety of options available to generate a list based on criteria in Excel.
📌 Primarily, you can determine matches using the IF function conditions. Adding the ABS function to it make the calculation precise.
📌 Also, you can use the AND function with the IF function to function to ranges altogether.
📌 A complex yet effective formula using the COUNTIF function can be of help as well. The IF function adds a condition on the return of the COUNTIF function and eventually, you get whether there are matches.
📌 You can use Conditional Formatting to highlight the duplicate values.
📌 Here, Excel can also utilize formula through Conditional Formatting to compare 3 columns for matches in Excel.
Conclusion
To compare 3 columns for matches in Excel, four examples are common. You can use the IF, ABS, AND, and COUNTIF functions in three ways. Based on Conditional Formatting, you can try two methods with Highlight and Format by Formula to compare 3 columns for matches in Excel as well. In case of further queries, readers are requested to leave a comment for the author. The author will try their best to come up with a suitable solution. Follow ExcelDen to get more access to solutions regarding your Excel problems.
Related Articles
- 4 Ways to Compare Two Columns in Excel for Missing Values
- 2 Ways to Compare 4 Columns Using VLOOKUP Function in Excel
