How to Compare Two Columns in Excel

The fastest way to compare two columns in Excel depends on what you want to find:

For most list comparison tasks, start with COUNTIF:

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

This checks whether the value in A2 appears anywhere in column D.

If a match depends on two or more fields, see COUNTIF vs COUNTIFS in Excel for the multi-condition pattern.

1. Example Data

Suppose column A contains product IDs from a new order file, and column D contains product IDs from your master product list.

You want to know which new order IDs already exist in the master list and which ones are missing.

2. Compare Two Columns with COUNTIF

Use COUNTIF when you want a simple found/missing result.

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

Result:

How it works:

Lock the lookup range with dollar signs before copying the formula down. If references shift when copied, review Excel cell references.

For more counting patterns, see the Excel COUNT function tutorial.

3. Find Values in Column A That Are Missing from Column D

To flag only values that do not appear in the second list, use:

=IF(COUNTIF($D$2:$D$100,A2)=0,"Missing","")

This is useful when the missing items are the only rows you want to review.

If your list has extra spaces, inconsistent capitalization, or imported text issues, clean the data first with Excel Data Cleaning.

4. Compare Two Columns Row by Row

Use a row-by-row comparison when each row should match the same row in another column.

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

Example:

This method does not search the whole second column. It only compares A2 with B2, then A3 with B3, and so on.

5. Highlight Matches or Differences with Conditional Formatting

Conditional Formatting is best when you want visual review instead of a helper column.

To highlight values in column A that also appear in column D:

1. Select A2:A100.
2. Go to Home > Conditional Formatting > New Rule.
3. Choose "Use a formula to determine which cells to format."
4. Enter this formula:

=COUNTIF($D$2:$D$100,A2)>0

1. Choose a fill color and click OK.

To highlight values in column A that are missing from column D, use:

=COUNTIF($D$2:$D$100,A2)=0

For more formatting examples, see Excel conditional formatting.

6. Return Matching Data with XLOOKUP

Use XLOOKUP when comparison is not enough and you need to return related data.

Suppose the master list has Product ID in column D and Category in column E:

To return the category for the ID in A2:

=XLOOKUP(A2,$D$2:$D$100,$E$2:$E$100,"Missing")

If the product exists, Excel returns the category. If not, it returns Missing.

For a deeper walkthrough, use the Excel XLOOKUP tutorial.

7. Compare Two Columns with VLOOKUP

If you need compatibility with older Excel files, VLOOKUP can also check whether values in one column appear in another column.

=IFNA(VLOOKUP(A2,$D$2:$D$100,1,FALSE),"Missing")

This returns the matching value if found and Missing if not found.

For a clearer label:

=IF(ISNA(VLOOKUP(A2,$D$2:$D$100,1,FALSE)),"Missing","Found")

If VLOOKUP returns #N/A unexpectedly, see How to Fix VLOOKUP #N/A in Excel.

8. Create a List of Missing Values with FILTER

If you use Microsoft 365 or newer Excel, FILTER can return a separate list of values from column A that do not appear in column D.

=FILTER(A2:A100,COUNTIF(D2:D100,A2:A100)=0,"No missing values")

This spills all missing values into a clean output list.

Use this when you want a reusable exception report instead of row-by-row labels. For more dynamic examples, see the Excel FILTER function tutorial.

9. Compare Two Columns and Find Duplicates

Sometimes "compare two columns" really means "find values that appear more than once." Use COUNTIF to flag repeated values:

=IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","Unique")

This checks duplicates inside one column. To compare duplicates across two lists, use the found/missing formulas above, then review repeated values before deleting anything.

For a safer cleanup workflow, see How to Remove Duplicates in Excel. For a review-first duplicate workflow, use How to Find Duplicates in Excel.

10. Common Problems When Comparing Columns

If your formulas return unexpected results, check these common issues:

To test whether two values are truly identical, use:

=A2=B2

If the result is FALSE, the cells are not identical even if they look similar.

11. Which Method Should You Use?

Use this decision table:

Start with the simplest method that answers the question. If you only need to know whether a value exists, COUNTIF is usually enough. If you need to bring back related data, move to XLOOKUP.

12. Practice Example

Use these lists:

Tasks:

Reference answers:

13. Summary

To compare two columns in Excel, use COUNTIF for found/missing checks, Conditional Formatting for visual review, XLOOKUP when you need to return related data, and FILTER when you want a separate list of missing values. Before trusting the result, clean extra spaces, check data types, and lock your ranges before copying formulas down.

14. Related Guides

• Return matching data with the Excel XLOOKUP tutorial.
• Choose between lookup values and matching-row reports with Excel FILTER vs VLOOKUP.
• Learn more COUNTIF examples in the Excel COUNT function tutorial.
• Decide when a list check needs multiple criteria with COUNTIF vs COUNTIFS in Excel.
• Build dynamic missing-value lists with the Excel FILTER function tutorial.
• Clean repeated records with How to Remove Duplicates in Excel.
• Review repeated values first with How to Find Duplicates in Excel.
• Fix missing lookup results with How to Fix VLOOKUP #N/A in Excel.
• Review Excel conditional formatting for visual comparison workflows.