6 Ways to Compare Three Columns to Find Matches in Excel

To compare three columns to find matches in Excel:

1. Select cell D2.
2. Type the formula: =IF(AND(A2=B2,B2=C2),”Common”,”Not Common”)
3. Press ENTER to insert the formula.
4. 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“.

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:

1. Select cell D2.
2. Type the formula: =IF(AND(A2=B2,B2=C2),”Common”,”Not Common”)
3. Press ENTER to insert the formula.
4. 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:

1. Choose cell D2.
2. Type the formula: =IF(COUNTIF(A2:C2, A2)=3, “Common”, “Not Common”)
3. To insert the formula press ENTER.
4. 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 using the IF and EXACT functions, follow these steps:

1. Click on cell D2.
2. Write the formula: =IF(AND(EXACT(A2, B2), EXACT(B2, C2)), “Common”, “Not Common”)
3. Press the ENTER key.
4. 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:

1. Select cell D2.
2. Write the formula: =IF(ISNUMBER(MATCH(A2, B2:C2, 0)), “Common”, “Not Common”)
3. Press the ENTER key.
4. 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:

1. Select the entire Dataset first.
2. Go to Home.
3. Click Conditional Formatting.
4. Select New Rule. A New Formatting Rule dialogue box will appear.
5. Choose Use a formula to determine which cells to format.
6. Write the equation: =AND($A2=$B2,$B2=$C2).
7. Go to Format and Choose the color AaBbCcYyZz.
8. 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:

1. Select the entire Dataset first.
2. Go to the Home tab.
3. Click Conditional Formatting.
4. Select Highlight Cells Rules.
5. Then select Duplicate Values.
6. Then select the color from Custom Format.
7. 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:

1. Select the first cell in one of the columns you want to compare
2. Go to the “Home” tab in the Excel ribbon.
3. Click on “Conditional Formatting” in the toolbar.
4. Choose “Highlight Cells Rules” and then select “Duplicate Values.”
5. In the dialog box, choose the formatting options for the duplicate values (e.g., cell fill color).
6. Click “OK” to apply the formatting.
7. 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:

1. 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.
2. 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”).
3. 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.
4. Drag the formula down for the entire range of your data.
5. 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:

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