**To compare three columns to find matches in Excel:**

- Select cell
**D2.** - Type the formula:
**=IF(AND(A2=B2,B2=C2),”Common”,”Not Common”)** - Press
**ENTER**to insert the formula. - Drag the
**Fill Handle**from**D2**to**D10.**

This **IF & AND** formula searches for the value in A2 between B2 and C2. This formula found 4 matches in this case; they are labeled **“Common”** and others **“Not Common****“**.

Table of Contents

## Compare Three Columns to Find Matches in Excel Using IF & AND Functions

If you want to find matches in three columns of Excel and identify them with terms like **“Common”** or **“Not Common,”** you may use the **IF** function with the **AND** function.

**Syntax**

**=IF(AND(logica1,logical2),value_if_true,value_if_false)**

**Formula**

**=IF(AND(A2=B2,B2=C2),"Common","Not Common")**

**Formula Breakdown**

**AND **functions check if the values of the three columns** A2, B2, **and** C2** are equal or not. When it is found equal the if function gives the result as **“Common” **otherwise **“Not Common”**.

To compare three columns to find matches using **IF** and **AND** functions, follow these steps below:

- Select cell
**D2.** - Type the formula:
**=IF(AND(A2=B2,B2=C2),”Common”,”Not Common”)** - Press
**ENTER**to insert the formula. - Drag the
**Fill Handle**from**D2**to**D10.**

** **

Finally, we see that if employee names are present in all three columns, it will return with a “**Common**” message. Or if it doesn’t find a match, it will return **“Not Common”**.

## Compare Three Columns to Find Matches in Excel Applying IF & COUNTIF Functions

We can achieve the same result using the **IF** and **COUNTIF** functions in Excel. A combination of both functions first searches and then compares with another column.

**Syntax**

**=IF(COUNTIF(range, criteria), value_if_true,value_if_false)**

**Formula**

=IF(COUNTIF(A2:C2, A2)=3, "Common", "Not Common")

**Formula Breakdown**

The **COUNTIF** function searches the common values between the three columns **A2:A10, B2:B10, **and** C2:C10**. If a match is found it will show **“Common”** otherwise **“Not Common”**.

To compare three columns to find matches by applying **IF** and **COUNTIF** functions, follow the steps below:

- Choose cell
**D2.** - Type the formula:
**=IF(COUNTIF(A2:C2, A2)=3, “Common”, “Not Common”)** - To insert the formula press
**ENTER**. - Drag the
**Fill Handle**from**D2**to**D10.**

Finally, we see that **Bale, Massimiliano, Emerson, **and** Roderic** are found in all three columns. So the result is shown as **“Common”. **

## Compare Three Columns to Find Matches in Excel Using IF & EXACT Functions

The **IF** and **EXACT** functions help with creating a logical comparison that determines whether two columns of data exactly match each other in one column.

**Syntax**

**=IF(AND(EXACT(text_1, text_2), EXACT(text_2, text_3)), value_if_true,value_if_false)**

**Formula**

=IF(AND(EXACT(A2, B2), EXACT(B2, C2)), "Common", "Not Common")

**Formula Breakdown**

The **EXACT** function is used to directly compare the values in cells** A2, B2**, and **C2**; **AND** is then used to determine whether the results of both comparisons are true. If they are, **“Common” **is returned; if not, **“Not Common.”**

To compare three columns usingthe **IF** and **EXACT** functions, follow these steps:

- Click on cell
**D2.** - Write the formula:
**=IF(AND(EXACT(A2, B2), EXACT(B2, C2)), “Common”, “Not Common”)** - Press the
**ENTER**key. - Drag the
**Fill Handle**from**D2**to**D10.**

Finally, we get our desired output.

## Compare Three Columns to Find Matches in Excel Using ISNUMBER & MATCH Functions

In Excel, the **ISNUMBER** and **MATCH** functions can help you quickly identify whether a value in one column is found in another column and show you exactly where the matches occur.

**Syntax**

**=IF(ISNUMBER(MATCH(lookup_value, lookup_array, [match_type])), value_if_true,value_if_false)**

**Formula**

**=IF(ISNUMBER(MATCH(A2, B2:C2, 0)), "Common", "Not Common")**

**Formula Breakdown**

Within the range **B2:C2**,** MATCH** searches for the value in cell **A2**. **MATCH’**s third input, **0**, denotes an exact match. **ISNUMBER** checks if a match was found. If a match was found, it returns **“Common”**; otherwise, it returns **“Not Common”**.

To compare three columns and determine matches using the **ISNUMBER** and **MATCH** functions:

- Select cell
**D2.** - Write the formula:
**=IF(ISNUMBER(MATCH(A2, B2:C2, 0)), “Common”, “Not Common”)** - Press the
**ENTER**key. - Drag the
**Fill Handle**from**D2**to**D10.**

This formula searches for the value in **A2** within the range **B2:C2 **using **MATCH. **In this case, this formula found 4 matches and they are shown as **“Common”**.

** **

## Compare Three Columns to Find Matches in Excel with Conditional Formatting

You can set up a new rule in Excel to format matched records using **Conditional Formatting**. This technique can be used to graphically highlight data that satisfy specific criteria or conditions. The process is given below:

- Select the entire
**Dataset**first. - Go to
**Home.** - Click
**Conditional Formatting**. - Select
**New Rule**.**A New Formatting Rule**dialogue box will appear. - Choose
**Use a formula to determine which cells to format**. - Write the equation:
**=AND($A2=$B2,$B2=$C2)**. - Go to
**Format**and Choose the color**AaBbCcYyZz.** - Click
**OK**.

Finally, matched data are highlighted in the **Dataset**.

## Compare Three Columns to Find Matches in Excel Using Duplicate Values Option

To highlight the matching records in Excel, use the **Duplicate Values** option found in the **Conditional Formatting **menu. The procedure is given below,

To compare three columns in Excel by using the **“Duplicate Values”** option, follow these steps:

- Select the entire
**Dataset**first. - Go to the
**Home**tab. - Click
**Conditional Formatting**. - Select
**Highlight Cells Rules**. - Then select
**Duplicate Values.** - Then select the color from
**Custom Format**. - After that click
**OK**.

Finally, this method finds **Duplicate Values** from those three columns. If it finds **Duplicate Values** it highlights the cells otherwise no change.

## Conclusion

This article walks through six alternative approaches to using Excel to compare three columns to look for matches. The first four approaches essentially apply various formulas. You can create these formulas using a variety of Excel functions. These formulas search for the value in **A2** in the range **B2:C2**; if they find a match, they return **“Common”**; if not, they return** “Not Common.” **The final two approaches only employ **Conditional Formatting**.

## Frequently Asked Questions

### How do I compare 3 columns in Excel for duplicates?

To compare three columns in Excel for duplicates:

- Select the first cell in one of the columns you want to compare
- Go to the
**“Home**” tab in the Excel ribbon. - Click on
**“Conditional Formatting”**in the toolbar. - Choose
**“Highlight Cells Rules”**and then select “Duplicate Values.” - In the dialog box, choose the formatting options for the duplicate values (e.g., cell fill color).
- Click
**“OK”**to apply the formatting. - Repeat the above steps for the other two columns you want to compare.

### How do I know if three columns match in Excel?

To determine if three columns match in Excel, you can use a combination of logical functions. Here’s a step-by-step guide:

- Insert a new column next to your data. This will be a helper column where you will check if the values in the three columns match.
- Assuming your data starts in column A, in the first cell of the helper column (let’s say, cell
**D2**), enter the following formula:**=IF(AND(A2=B2, B2=C2), “Match”, “No Match”).** - This formula uses the
**AND**function to check if both conditions (**A2**equals**B2**and**B2**equals**C2**) are true. If it meets both conditions, it returns ‘Match’; otherwise, it returns ‘No Match. - Drag the formula down for the entire range of your data.
- Examine the helper column. If the values in columns
**A, B,**and**C**match for a particular row, the helper column will display**“Match.”**Otherwise, it will show**“No Match.”**

This method provides a simple way to check if the values in three columns match on a row-by-row basis. Adjust the formula as needed based on your specific requirements. If you want to check for matches across the entire columns without considering individual rows, you may use a similar logic but apply it to the header cells instead of individual rows.

### How do I compare 4 columns in Excel?

To compare four columns in Excel for duplicates or matches, you can use a combination of conditional formatting and formulas. Here’s how:

- Select the first cell in one of the columns you want to compare.
- Go to the
**“Home”**tab in the Excel ribbon. - Click on
**“Conditional Formatting”**in the toolbar. - Choose
**“Highlight Cells Rules”**and then select**“Duplicate Values.”** - In the dialog box, choose the formatting options for the duplicate values (e.g., cell fill color).
- Click
**“OK”**to apply the formatting. - Repeat the above steps for the other three columns you want to compare.

5/5 - (2 votes)