Compare Two Columns for Matches in Excel (Easiest Way in 2024)

In this article, we will show you how to compare columns for matches in Excel. Simply follow the steps below. 

How to Compare Two Columns in Excel for Matches and Differences

Two columns can be compared for matches and differences using three effective methods: the ‘Equals’ operator, the ‘EXACT’ formula, and the ‘IF’ function. We will discuss how to use each method in the following sections.

Method 1: Using the Equals Operator

To compare columns for matches in Excel using the Equals operator, simply follow the process below.

1. Add a New Column for Comparison Results Next to the Second Column

In our example, we have a list of names from ‘Training A Participants’ in column A (cells A2 to A9) and ‘Training B Participants’ in column B (cells B2 to B9). We will compare these two columns to identify which participants attended both training sessions by finding matches between the two columns.

Select an empty column where we will place our comparison results. In our case, we will use column C.

2. Input a Formula to Check if Names in the Two Columns Match

Click in the first cell of the newly added column (cell C2) and type the formula `=A2=B2`. Press Enter to apply this formula/ This checks if the name in column A (Training A) matches the name in column B (Training B).

3. Drag the Formula Down the Column to Apply It to All Rows

Click and drag down the ‘Fill Handle’ (lower right corner of the cell) to fill all cells in this column with the formula.

4. Examine the Results to Identify Matches and Differences

Review the new column where each cell now shows either `TRUE` if the corresponding names in columns A and B match or `FALSE` if they do not. This indicates whether each participant attended both training sessions or just one.

Method 2: Using the EXACT Formula

To compare columns for matches in Excel using the EXACT formula, we will work with a dataset containing ‘Training A Participants’ in Column A and ‘Training B Participants’ in Column B.

1. Apply the EXACT Formula to Compare Participants in Each Row 

In the top cell of the new column (cell C2), enter `=EXACT(A2, B2)`. This function compares the names exactly, including their case (uppercase vs. lowercase). Hit Enter to confirm.

2. Copy the EXACT Formula Down to All Relevant Data Rows

Use the fill handle at the bottom right corner of the formula cell. Drag it down to propagate the formula throughout the column, ensuring every pair of names is compared.

3. Check the Output to Determine Precise Name Matches

Look through the newly filled column. A result of `TRUE` means an exact match was found between the names in columns A and B, while `FALSE` indicates a mismatch, highlighting differences in details like capitalization.

Method 3: Using the IF Function

Follow the steps below to compare the lists of participants from two different training sessions using the EXACT function.

1. Create a Column to Display Results from IF Function Comparisons

Use the next column to the right of ‘Training B Participants’ for the results. In our case, it will be column C.

2. Use the IF Formula to Label Each Comparison as ‘Match’ or ‘No Match’

In the first cell of your new results column (cell C2), type `=IF(A2=B2, “Match”, “No Match”)`. This formula assesses whether the names match and labels them accordingly. Press Enter to execute.

3. Extend the IF Function Down the Column for All Comparisons

Grab the fill handle of the cell where you input the IF formula and drag it downward to fill the entire column, applying the formula to each row.

4. Review the Results to Identify Which Participants Match and Which Do Not

Scan the results in the new column. The label “Match” indicates that the names in columns A and B are identical, while “No Match” shows they are different, clarifying attendance records for each training.

We hope that you now have a better understanding of how to compare two columns for matches in Excel. If you enjoyed this article, you might also like our article on how to compare lists in Excel for matches or our article on how to compare two columns in Excel for missing values.