Excel Formulas to Compare Data: Top Methods

Formulas & functions · Updated November 22, 2026

The fastest way to compare data in Excel is to use the IF function for row-by-row checks (e.g., =IF(A2=B2,"Match","No Match")) or VLOOKUP/XLOOKUP to find matches across two lists. For a full list of differences, use conditional formatting with the COUNTIF function. This article explains the most effective formulas, when to use each, and how to avoid common mistakes.

Why Compare Data in Excel?

Comparing data is a core Excel task used in accounting, inventory management, HR, and data analysis. You might need to check if two columns have identical values, find records that exist in one list but not another, or spot discrepancies between two versions of a report. Excel offers several formula-based approaches, each suited to a different scenario. Understanding the differences helps you pick the right tool and avoid errors that can arise from mismatched data types or duplicate entries.

Basic Row-by-Row Comparison with IF

The simplest comparison checks whether values in the same row of two columns are equal. This is useful when you have two columns of data that should align (e.g., a list of expected vs. actual amounts).

Assume columns A and B contain the values you want to compare, starting in row 2. In cell C2, enter:

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

Drag the formula down. If A2 equals B2, it returns “Match”; otherwise “Different”. For case-insensitive text comparison, Excel treats “APPLE” and “apple” as equal by default. To make the comparison case-sensitive, use EXACT:

=IF(EXACT(A2,B2),"Exact","Not Exact")

This works in all Excel versions. On Mac, the formulas are identical; only the keyboard shortcuts for filling down differ (Ctrl+D on Windows, Cmd+D on Mac).

Finding Matches Between Two Lists with VLOOKUP or XLOOKUP

When you have two separate lists (e.g., a list of employee IDs in column A and a list of approved IDs in column D), you often need to know which items appear in both. The classic approach is VLOOKUP.

Assume list 1 is in A2:A20 and list 2 is in D2:D10. In cell B2, enter:

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

This checks if the value in A2 exists anywhere in D2:D10. If it does, VLOOKUP returns the value; ISNA returns FALSE, so the IF returns “Found”. If not, VLOOKUP returns #N/A, ISNA returns TRUE, and you get “Not Found”.

In Excel 2021 or Microsoft 365, XLOOKUP is simpler and more robust:

=IF(ISNA(XLOOKUP(A2,$D$2:$D$10,$D$2:$D$10)),"Not Found","Found")

XLOOKUP works the same way but does not require the lookup column to be the first column of a range, and it handles arrays more gracefully. If you are using an older version, stick with VLOOKUP.

Highlighting Differences with Conditional Formatting

Instead of writing formulas, you can visually highlight differences. For row-by-row differences, select both columns (e.g., A2:B20), go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter:

=$A2<>$B2

Set a fill color, and click OK. This highlights cells where the row values differ. Note the use of relative references (no $ before row numbers) so the rule applies to each row.

To highlight values in column A that do not appear in column D, select A2:A20 and use this formula in the conditional formatting rule:

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

This works in all versions. For Excel 365, you could also use the FILTER function to extract non-matching items into a new range, but conditional formatting is often more practical for quick visual checks.

Comparing Entire Rows or Tables

Sometimes you need to compare entire rows across two tables. For example, you have a master price list and a new price list, and you want to find rows where any column differs.

Assume the master list is in A1:C20 and the new list is in E1:G20, with the same product IDs in column A and E. In cell H2, enter this array formula (press Ctrl+Shift+Enter in older Excel; in Excel 365, just Enter):

=IF(COUNTIF($E$2:$E$20,$A2)=0,"Missing",IF(SUMPRODUCT(--($A2:$C2<>$E2:$G2))>0,"Changed","Same"))

This first checks if the product ID exists in the new list. If not, it marks “Missing”. If it exists, it compares each cell in the row using SUMPRODUCT, which counts the number of differences. If any difference exists, it returns “Changed”. This formula works in all versions, but note that SUMPRODUCT requires the ranges to be the same size.

For a more modern approach in Excel 365, you can use the FILTER function to list all changed rows:

=FILTER(A2:C20,COUNTIF(E2:E20,A2:A20)=0)

This returns rows from the master list that are missing in the new list. To find rows where any value differs, you would need a more complex LET-based formula, which is beyond the scope of this article.

Common Pitfalls and How to Avoid Them

Comparing data often fails due to subtle issues. Here are the most frequent problems:

  • Leading/trailing spaces: “John ” and “John” look the same but are not. Use =TRIM(A2)=TRIM(B2) to ignore spaces.
  • Number stored as text: A number formatted as text will not equal the same number as a numeric value. Use VALUE(A2) to convert, or check the cell’s number format.
  • Duplicate entries: VLOOKUP and COUNTIF only check existence, not uniqueness. If you need to compare specific instances, add a helper column with a running count.
  • Case sensitivity: By default, Excel comparisons ignore case. Use EXACT for case-sensitive matching.
  • Hidden characters: Non-printing characters from imported data (e.g., line breaks) can break comparisons. Use CLEAN to remove them.

Always verify your data types before comparing. A quick way is to use the ISTEXT or ISNUMBER functions on a sample cell.

Choosing the Right Formula for Your Task

To decide which method to use, consider the size of your data and the nature of the comparison:

  • Two columns, same row: Use IF or EXACT for a simple match/mismatch flag.
  • Two lists, find items in both: Use VLOOKUP (older versions) or XLOOKUP (Excel 365/2021).
  • Two lists, highlight non-matching items: Use conditional formatting with COUNTIF.
  • Entire rows across tables: Use SUMPRODUCT with IF, or consider Power Query (Get & Transform) for large datasets.
  • Large datasets (thousands of rows): Formulas can slow down. Consider using Power Query to merge queries and compare columns—it’s faster and more transparent.

For most everyday tasks, the IF and VLOOKUP formulas are sufficient. If you frequently compare large tables, learning Power Query will save you time.

Frequently Asked Questions

How do I compare two columns for differences in Excel?
Use the IF formula in a third column: =IF(A2=B2,"Match","No Match"). For a visual highlight, use conditional formatting with the rule =$A2<>$B2.

Can I compare two lists and extract unique values?
Yes. In Excel 365, use =UNIQUE(VSTACK(A2:A20,D2:D10)) to get all unique values from both lists. To find values only in list A, use =FILTER(A2:A20,COUNTIF(D2:D10,A2:A20)=0).

Why does VLOOKUP return #N/A even though the value exists?
This usually happens because of leading/trailing spaces, number stored as text, or the lookup column not being the first column in the table array. Use TRIM and ensure the data types match. Also, check that the range is locked with $ signs when copying the formula.

What is the difference between VLOOKUP and XLOOKUP?
XLOOKUP is newer (Excel 2021/365) and more flexible: it can look to the left, handle arrays, and has a built-in if-not-found argument. VLOOKUP is older but works in all versions. For comparing data, XLOOKUP is easier to read and less error-prone.

How do I compare two Excel sheets for differences?
Use the same formulas but reference cells from different sheets, e.g., =IF(Sheet1!A2=Sheet2!A2,"Match","No Match"). For a full sheet comparison, you can use the built-in ‘Compare and Merge Workbooks’ feature (if enabled) or use a third-party tool.

Can I compare data without formulas?
Yes. Use conditional formatting with the ‘Duplicate Values’ rule for two columns, or use the ‘Go To Special’ feature (F5 > Special > Row differences) to select cells that differ from a comparison cell. These methods are quick but less flexible than formulas.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.