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.

**approach**

### 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.

**=IF(ABS((C6-D6))+ABS((D6-E6))=0,”Same”,””)**

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

**approach**

### 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.

**=IF(AND(C6=D6,D6=E6),”Equal”,””)**

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

**approach**

### 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.

**=IF(COUNTIF(C6:D6,C6)+COUNTIF(D6:E6,E6)=2,””,”Match”)**

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

**approach**

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

**approach**

### 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.

**=AND($C6=$D6,$D6=$E6)**

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