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
EXACTfor case-sensitive matching. - Hidden characters: Non-printing characters from imported data (e.g., line breaks) can break comparisons. Use
CLEANto 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
IForEXACTfor a simple match/mismatch flag. - Two lists, find items in both: Use
VLOOKUP(older versions) orXLOOKUP(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
- Excel Browser Power Query: What It Is & How to Use
- Excel Blank Calendar Template: How to Get One
- Excel Biweekly Timesheet Template with Lunch
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula to Create a List Based on CriteriaIn Microsoft 365 or Excel 2021, use the FILTER function to create a list from rows that meet…
- Excel COUNTIF Not Text? Use COUNTIF with WildcardsTo count cells that do not contain text in Excel, use =COUNTIF(range,"*") — this counts numbers, dates, and…
- How to Group the Same Numbers in Excel: Sort and CountTo group identical numbers in Excel, sort the entire data range by the column containing those numbers: select…
- BETWEEN Function in Snowflake: Syntax & ExamplesThe BETWEEN operator in Snowflake is a conditional operator used to filter rows where a value falls within…
- Number Sequence in Excel: How to Continue a SeriesTo continue a number sequence in Excel, enter the first two values, select both cells, then drag the…
- Excel Formula for Calculating Overtime HoursTo calculate daily overtime in Excel, subtract unpaid break hours and the regular-hours threshold from the shift length,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.