How to Compare Two Columns in Excel: Find Matches and Differences

Comparing two columns can mean several different things: finding exact matches, identifying values missing from one list, flagging differences row by row, or checking whether two datasets contain the same entries. The best Excel method depends on which question you need to answer.

Compare the same row

If A2 and B2 should contain the same value, use:

=IF(A2=B2,"Match","Different")

This is useful when two exports should align row by row.

Find values in one column that are missing from another

For a modern Excel version, you can use COUNTIF to test whether a value from column A appears anywhere in column B:

=IF(COUNTIF($B:$B,A2)=0,"Missing","Found")

For larger or more complex workbooks, XLOOKUP can also provide a clear match/not-found workflow.

Highlight differences visually

Conditional Formatting can highlight cells in one column that do not have a corresponding value in another column. This is useful when the goal is review rather than producing a separate report.

Clean the data before comparing

Two values can look identical while containing extra spaces or different data types. If the comparison gives surprising results, inspect the source data and consider cleaning it first. The Excel data-cleaning guide covers common issues such as extra spaces and numbers stored as text.

Compare two lists regardless of order

If the lists are not in the same order, do not compare A2 with B2 blindly. Instead, test whether each value exists somewhere in the other column using COUNTIF, XLOOKUP, or another lookup approach.

Find a list of differences

For a modern Excel workflow, FILTER can return the entries from one list that are not found in another when the comparison logic is built around COUNTIF. This creates a separate review list without altering the source data.

Common mistakes

• Comparing row positions when the lists are in different orders.
• Ignoring extra spaces or inconsistent capitalization where those differences matter.
• Comparing numbers stored as text with real numbers.
• Changing or deleting source data before confirming the comparison result.

Which method should you use?

Practical checklist

• Define whether you need row-by-row comparison or list membership.
• Clean inconsistent source data before diagnosing a mismatch.
• Keep source columns unchanged until the comparison is verified.
• Use a separate result column or sheet for review when possible.
• Choose the simplest method that answers the actual comparison question.

Related Excel guide: How to Find Differences Between Two Excel Sheets.

Migration 074: column-comparison refinement.

First decide what “compare” means

There are two common jobs. If row 2 in Column A belongs with row 2 in Column B, you need a row-by-row comparison. If the two columns are independent lists, you need a membership test: “Does this value appear anywhere in the other list?” Choosing the wrong comparison model is a common reason for false results.

Row-by-row comparison

=IF(A2=B2,"Match","Different")

Use this when the records are already aligned. It does not tell you whether the value exists somewhere else in Column B.

List membership

To check whether the value in A2 appears anywhere in Column B:

=IF(COUNTIF($B$2:$B$100,A2)>0,"Found","Missing")

This works even when the matching value appears on a different row.

Find the differences safely

For a reconciliation task, flag the missing entries first and review them before changing either source list. If the lists come from different systems, clean spaces, data types and obvious formatting inconsistencies before concluding that a value is genuinely missing.

Which method should you use?