Compare Two Columns in Excel: Matches and Differences

TL;DR: Compare two columns in Excel with =A2=B2 for row-by-row checks, COUNTIF for matches or missing values across lists, and Conditional Formatting for visual results. Use EXACT() when capitalization matters or Go To Special > Row Differences for a quick check without formulas.

The quickest way to compare two columns in Excel is to enter =A2=B2 in a third column. Excel returns TRUE when the two cells match and FALSE when they do not. If the lists are in a different order, use COUNTIF or XLOOKUP instead.

Column comparisons come up when checking reports, reconciling customer or product lists, and cleaning data from different sources. Depending on the task, you may need to:

• Compare values in the same row
• Find entries that appear anywhere in both lists
• Identify values missing from either column
• Highlight duplicates or differences
• Run a case-sensitive comparison
• Compare aligned rows without writing a formula

The methods below cover each of these situations and show when to use one over another. If you are new to spreadsheets, start with this guide to Excel basics.

Join The Ranks of Top-Notch Data Analysts!With Our Data Analyst CourseExplore Program

Which Method Should You Use? Match vs. Differences vs. Duplicates

Most column comparisons fall into three groups. Choose the result you need before picking the formula or feature.

Compare Two Columns Row by Row (A2 vs. B2)

This is the simplest way to compare two columns in Excel when each row represents the same record in both columns. For example, Column A may contain the original product code and Column B the imported code.

1. Place the two lists in Columns A and B, starting in row 2.

2. Select cell C2.

3. Enter =A2=B2 and press Enter.

4. Excel returns TRUE for a match and FALSE for a difference.

5. Drag the fill handle down to compare the remaining rows.

To return clearer labels, use:

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

This comparison ignores capitalization, so North and NORTH count as a match. Use EXACT() if uppercase and lowercase letters must match too.

Compare Two Columns for Matches Across Lists

Use COUNTIF to compare two columns in Excel when the rows do not line up. It checks whether each value in Column A appears anywhere in Column B.

1. Put the list to check in Column A and the reference list in Column B.

2. Select C2 and enter:

=COUNTIF(B:B,A2)>0

3. Press Enter. TRUE means the value from A2 appears somewhere in Column B. FALSE means it does not.

4. Fill the formula down Column C.

If you prefer words instead of TRUE and FALSE, use:

=IF(COUNTIF(B:B,A2)>0,"Match","No Match")

COUNTIF is not case-sensitive. It treats Product-A and product-a as the same text. It also counts every occurrence, so =COUNTIF(B:B,A2) can tell you how many times the value appears in Column B.

Become an AI-Powered Data Analytics ExpertWith Our PCP in Data AnalyticsExplore Program

Compare Two Columns for Differences and Missing Values

To compare two columns in Excel and find differences, test whether each item in the first list exists in the second. The formula below marks values that are missing from Column B.

1. Keep the main list in Column A and the comparison list in Column B.

2. Select C2 and enter:

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

3. Press Enter and fill the formula down.

4. Filter Column C by Missing to show only values that do not appear in Column B.

That check only works in one direction. To find unique values between both columns, run the reverse comparison in D2:

=IF(COUNTIF(A:A,B2)=0,"Missing","Present")

Column C now shows values unique to Column A, while Column D shows values unique to Column B. This approach works even when the two lists use a different order.

Highlight Duplicates Across Two Columns With Conditional Formatting

Conditional Formatting lets you compare two columns in Excel visually by adding color without changing the data.

1. Select both columns or the exact data range, such as A2.

2. Open Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

3. Keep Duplicate selected and choose a format.

4. Select OK.

Excel highlights values repeated anywhere in the selected range. That includes a value repeated twice in Column A, even if it never appears in Column B. If you need a deeper walkthrough, see how to highlight duplicates in Excel.

Highlight Row-by-Row Differences

To compare two columns in Excel and highlight differences in aligned rows, select A2 and open Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =A2<>B2, choose a fill color, and select OK. Excel highlights both cells in every row where the values differ.

Move From Analyzing Data to Building AI SystemsWith our PCP in Agentic AI & Multi-Agent SystemsExplore Program

Compare Two Columns Using XLOOKUP or XMATCH

Modern versions of Excel include XLOOKUP and XMATCH. Both can search for a value anywhere in another column, but they return different results.

Use XLOOKUP to Return the Matching Value

1. Enter the primary list in Column A and the lookup list in Column B.

2. Select C2 and enter:

=XLOOKUP(A2,B:B,B:B,"Not Found")

3. Press Enter and fill the formula down.

If Excel finds A2 in Column B, the formula returns that value. If no match exists, it returns Not Found. XLOOKUP uses an exact match by default. Learn more about the function in this XLOOKUP guide.

Use XMATCH to Return the Match Position

1. Select C2 and enter:

=XMATCH(A2,B:B,0)

2. Press Enter and copy the formula down.

XMATCH returns the relative position of the first exact match. If the value is absent, Excel returns #N/A. Excel 2019 and earlier do not support XLOOKUP or XMATCH, so use =MATCH(A2,B:B,0) instead. This INDEX and MATCH guide explains the older lookup method in more detail.

Compare Two Columns With EXACT for a Case-Sensitive Match

To compare two columns in Excel with case sensitivity, use EXACT(). The normal equals comparison and COUNTIF ignore letter case, while EXACT() treats ABC-101 and abc-101 as different values.

1. Place the values to compare in Columns A and B.
2. Select C2 and enter:
   =EXACT(A2,B2)
3. Press Enter. For example, if A2 contains Product-ABC and B2 contains product-abc, the result is FALSE.
4. Fill the formula down to compare the other rows.

For labels instead of TRUE and FALSE, use:

=IF(EXACT(A2,B2),"Match","No Match")

EXACT() respects spaces and letter case but ignores visual formatting such as font color or bold text. If spacing should not affect the result, clean the values first with =EXACT(TRIM(A2),TRIM(B2)).

Compare Two Columns Without a Formula Using Go To Special

Go To Special offers a fast way to compare two columns in Excel without a formula. In Excel for Windows, it selects the cells in one column that differ from the reference cell in the same row.

1. Select the comparison range, such as A2, starting from A2 so Column A remains the reference column.
2. Press Ctrl+G, then select Special.
3. Choose Row differences and select OK.
4. Excel selects the cells in Column B that differ from Column A in the same row.
5. Before clicking elsewhere, apply a fill color from the Home tab if you want to keep the differences visible.

This feature compares A2 with B2, A3 with B3, and so on. It does not search for a value elsewhere in the other column. Use COUNTIF, XLOOKUP, or XMATCH when the list order is different. Microsoft documents the active-cell behavior in its guide to finding and selecting cells with Go To Special.

Compare Columns Across Different Sheets or Workbooks

The same formulas work when the columns are stored elsewhere. For another sheet in the same workbook, use =COUNTIF(Sheet2!B:B,A2)>0. For a separate open workbook, select its lookup range while building the formula so Excel creates a reference such as =XLOOKUP(A2,'[Inventory.xlsx]Sheet1'!B2:B1000,'[Inventory.xlsx]Sheet1'!B2:B1000,"Not Found"). Keep fixed ranges from shifting by using absolute references in Excel.

Our AI Data Analyst Course will help you learn analytics tools and techniques to become a Data Analyst expert! It's the perfect course for you to jumpstart your career. Enroll now!

Key Takeaways

• Use =A2=B2 for a quick row-by-row comparison
• Use COUNTIF when list order does not matter, and you need matches, missing entries, or unique values
• Use Conditional Formatting to highlight duplicates or aligned differences visually
• Use XLOOKUP to return a matching value and XMATCH to return its position
• Use EXACT() when capitalization must match
• Use Go To Special > Row Differences for a one-time comparison of aligned rows without formulas
• Use Power Query for repeatable comparisons between large tables

FAQs

1. How does comparing two columns in Google Sheets differ from Excel?

Both support formulas such as =A2=B2, COUNTIF, and Conditional Formatting. Excel also provides desktop features such as Go To Special > Row Differences.

2. What is the fastest way to compare very large datasets in Excel?

For a repeatable large-table comparison, load both tables into Power Query and choose Home > Merge Queries. Select the key column in each table, then use an Inner join for common rows or a Left Anti join for values missing from the second table.

3. Why does =A2=B2 return FALSE when the values look identical?

One cell may contain a leading or trailing space, a hidden character, or a number stored as text. Try TRIM and CLEAN for text, or convert both entries to the same data type before comparing them.