How do I compare two columns in Excel for matches?

Use =A2=B2 for row-by-row comparison or MATCH/XLOOKUP to check whether values from one list exist in another.

How do I find missing values between two columns?

Use =ISNA(MATCH(A2,$B$2:$B$100,0)) to flag values in column A that are missing from column B.

Use MATCH or XLOOKUP after cleaning both columns. MATCH with ISNA is a strong missing-value flag.

They may contain hidden spaces, non-printing characters, different data types, or different date formats.

Tutorials - 2026-05-12

Compare two columns in Excel to find matches, differences, missing values, duplicates, and lookup mismatches using formulas and formatting.

Comparing columns is useful for reconciling lists, checking imports, matching IDs, and finding missing records. Common goals: Find exact matches Find values missing from another list Highlight differences row by row Identify duplicates

\\\ =ISNA(MATCH(A2,$B$2:$B$100,0)) \\\ TRUE means the value in A2 was not found in column B.

\\\ =XLOOKUP(A2,$B$2:$B$100,$B$2:$B$100,"Missing") \\\ This gives a readable missing-value result.

XLOOKUP in Excel MATCH function Conditional Formatting in Excel Excel Duplicate Values

Goal Best formula Same row comparison =A2=B2 Value from A missing in B =ISNA(MATCH(A2,$B:$B,0)) Return match or Missing =XLOOKUP(A2,$B:$B,$B:$B,"Missing") Count occurrences =COUNTIF($B:$B,A2)

When reconciling two lists, clean both sides first. Trim spaces, standardize case when needed, and confirm IDs are the same data type. Many "missing" values are actually formatting or cleanup problems. For financial or operational reconciliation, add helper columns: \\\ =TRIM(CLEAN(A2)) =COUNTIF(CleanListB,CleanA2) \\\ Then filter for zero matches.

Use XLOOKUP, MATCH, COUNTIF, and Conditional Formatting depending on whether you need results, flags, counts, or visual highlights.

How do I compare two columns in Excel for matches? Use =A2=B2 for row-by-row comparison or MATCH/XLOOKUP to check whether values from one list exist in another.
How do I find missing values between two columns? Use =ISNA(MATCH(A2,$B$2:$B$100,0)) to flag values in column A that are missing from column B.
What is the best way to compare two columns for missing values? Use MATCH or XLOOKUP after cleaning both columns. MATCH with ISNA is a strong missing-value flag.
Why do two values look the same but not match? They may contain hidden spaces, non-printing characters, different data types, or different date formats.