Compare Two Excel Sheets for Matching Data in Excel

How-to basics · Updated November 18, 2026

To compare two Excel sheets for matching data, use a shared ID column and check whether each ID from the first sheet appears in the second; then compare the related values if needed. In Microsoft 365 for Windows, a helper-column formula such as =IF(COUNTIF(Sheet2!$A$2:$A$100,A2)>0,"Match","Missing") quickly flags IDs that are present or absent.

Choose a comparison method

The right approach depends on what “matching” means for your work. You might want to know whether a customer number appears in both lists, whether the balance for each customer is unchanged, or whether two sheets are identical row by row. These are different checks, and a formula that answers one may not answer the others.

  • Find records missing from either list: compare a unique ID column with COUNTIF or XLOOKUP.
  • Check whether values for the same record agree: look up each ID, then compare its related fields.
  • Compare rows in the same order: compare corresponding cells directly. This only works when row order is meaningful and consistent.
  • Repeat comparisons on refreshed data: use Power Query to merge the lists and inspect unmatched records.

For a one-time check of a modest list, formulas are usually easiest to understand and audit. For recurring reports or large tables, Power Query can make the process more repeatable. Neither method can decide what counts as the same record: you must select a dependable key and define how to handle duplicates, blanks, and differences in formatting.

Prepare the sheets before comparing

Start by checking that the two sheets use the same kind of identifier in the same field. A customer ID in one sheet should be compared with the customer ID column in the other, not with a name column that may contain spelling variations. If possible, use a unique key such as an order number or account ID. Names alone are often unreliable because two people can share a name or one person’s name can be entered differently.

Give the columns clear headings, remove completely empty rows from the data area, and check that IDs have consistent formatting. For example, the values 00127 and 127 may represent different IDs, even if a cell’s number format makes them look alike. A number stored as text can also behave differently from a number stored as a numeric value. Don’t strip leading zeros unless the business rules say they are insignificant.

Duplicates matter, too. A lookup can find an ID without telling you that it appears twice. Before comparing, decide whether duplicate IDs are valid. If they are not, use Excel’s Data > Remove Duplicates only on a copy or after confirming that removing records is appropriate; otherwise, use a duplicate check and investigate them without deleting data.

Find IDs that are missing from the other sheet

Assume both sheets have IDs in column A, with headings in row 1 and data in rows 2 through 100. Add a helper column on Sheet1, enter the formula below in C2, and fill it down. In this example, Sheet1 cell A2 contains ID 104, and Sheet2 contains 104 somewhere in A2:A100, so the result is “Match.” If it is absent, the result is “Missing.”

With IDs in Sheet1!A2:A100 and Sheet2!A2:A100, and the formula entered in Sheet1!C2 for the ID in A2:

=IF(COUNTIF(Sheet2!$A$2:$A$100,A2)>0,"Match","Missing")

The dollar signs keep the lookup range fixed as you fill the formula down, while A2 changes to A3, A4, and so on. To find IDs in Sheet2 that are absent from Sheet1, use the same check in a helper column on Sheet2 and point COUNTIF at Sheet1’s ID range. Checking in both directions is important: a one-way check reveals omissions from one list, but not records that exist only in the other.

COUNTIF is not case-sensitive, which is normally suitable for IDs. If uppercase and lowercase letters distinguish records in your system, use a case-sensitive method rather than assuming this formula treats them as different.

Compare values for records with matching IDs

Finding an ID in both sheets does not prove the records agree. To compare a value such as a status, price, or balance, look up the corresponding row by ID and compare that field. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It uses an exact match by default, so it is a practical choice for this task.

Assume Sheet1 has IDs in A2:A100 and amounts in B2:B100, and Sheet2 has the corresponding IDs and amounts in the same columns. Enter this formula in Sheet1!C2. If A2 is 104, B2 is 250, and the amount returned for ID 104 on Sheet2 is also 250, the result is “Same.” If the ID is not found, the formula returns “Missing ID.”

=IFERROR(IF(B2=XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100),"Same","Different"),"Missing ID")

Fill the formula down to check the other records. If you need to compare more than one field, add checks for each field or create a combined comparison that reports which columns differ. A single “Different” result is useful for screening, but it does not explain the nature of the difference. For financial or operational data, separate flags for fields such as amount, date, and status can make follow-up clearer.

If IDs can appear more than once, XLOOKUP returns the first matching item, which might not be the correct record to compare. Resolve duplicates first or compare using a key that uniquely identifies each row, such as a combination of order ID and line number.

Compare multiple fields with COUNTIFS

If your question is whether a complete record exists in the second list, you can test more than one field at once. COUNTIFS counts rows that meet all of its specified conditions. This is useful when a single column is not unique, or when you want to verify a combination such as order number plus product code.

Assume Sheet1 and Sheet2 each have order IDs in column A and product codes in column B, with data in rows 2 through 100. Enter this formula in Sheet1!C2 to test whether the pair of values in A2 and B2 occurs together on Sheet2. If the pair appears there at least once, the result is “Match”; otherwise, it is “No match.”

=IF(COUNTIFS(Sheet2!$A$2:$A$100,A2,Sheet2!$B$2:$B$100,B2)>0,"Match","No match")

This checks whether a row with those field values exists; it does not compare every column in the row, nor does it verify that the number of repeated copies is the same on both sheets. If duplicate rows matter, compare counts for each key or use Power Query to examine the full sets of records.

Compare rows directly when order is reliable

When rows are intentionally in the same order on both sheets, compare corresponding cells. This is suitable for checking two versions of a fixed report, but it is risky for independently sorted lists: one inserted row can make every row below it appear different even when the records themselves match.

Assume the sheets have the same layout, with fields in columns A, B, and C and the first record in row 2. Enter this formula in Sheet1!D2. If all three cells in row 2 match their counterparts on Sheet2, it returns “Same”; otherwise, it returns “Different.”

=IF(AND(A2=Sheet2!A2,B2=Sheet2!B2,C2=Sheet2!C2),"Same","Different")

Fill down to compare more rows. Extend the AND function with another cell comparison for each additional column you need to check. This direct method compares values, not necessarily how they are displayed: two date cells may show different formats while representing the same date, and a formula cell may display the same result as a hard-coded value. Decide whether your comparison should focus on displayed results, underlying values, or formulas before interpreting a flagged row.

Use conditional formatting to see results

A helper column is often more useful than coloring cells alone because it records a clear result that can be filtered, counted, or reviewed. After adding a status column, select the results and use Home > Conditional Formatting > Highlight Cells Rules to apply a color rule to text such as “Missing” or “Different.” You can then filter the helper column to show only the exceptions.

Another quick option is Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, which highlights duplicates within the selected range. It does not, by itself, tell you whether a value in one worksheet appears in another, so don’t treat it as a complete cross-sheet comparison. A helper formula is easier to audit when you need to check two separate lists.

Use Power Query for repeatable comparisons

Power Query is useful when you receive updated versions of two tables regularly, need to compare many rows, or want a refreshable list of records that do not match. On Excel for Microsoft 365 for Windows, turn each data range into a table with Ctrl+T, then use Data > From Table/Range to open each table in Power Query. In Power Query Editor, use Home > Merge Queries > Merge Queries as New, select the key column in each table, and choose a join kind.

  • Left Anti: returns rows in the first table with no matching key in the second.
  • Right Anti: returns rows in the second table with no matching key in the first.
  • Full Outer: keeps rows from both tables, including unmatched ones, so you can review what exists on only one side.

For an ordinary match, choose the matching key column in each preview and make sure the columns have compatible data types. Expand the merged results when you need to compare fields from both tables, then load the result back into Excel. After the source tables change, refresh the query to update the comparison. Power Query is available in Excel for Windows and Mac, but availability and features can vary by platform and build; Excel for the web has more limited query authoring capabilities. On Mac, check the Data tab for the available Power Query commands in your version.

Common causes of false mismatches

A result marked “Missing” or “Different” may reflect inconsistent data rather than a genuinely different record. Before changing values, inspect a few flagged examples and consider these common causes:

  • Extra spaces or invisible characters: “North” and “North ” may look identical. TRIM can remove ordinary leading and trailing spaces, while CLEAN can remove many nonprinting characters; imported data can contain other whitespace that needs separate treatment.
  • Different data types: text “104” and numeric 104 may look alike but are not always handled identically. Standardize the source data deliberately instead of converting IDs blindly.
  • Different date or number precision: dates may include hidden time values, and decimal values may differ beyond the displayed number of decimal places. Check the underlying values and the precision your process requires.
  • Duplicates or incomplete keys: a non-unique ID can pair a record with the wrong row. Add fields to the key or resolve duplicate records before comparing related values.
  • Different spelling or case rules: decide whether “ABC” and “abc,” or alternative spellings of a name, should count as the same value.

Keep a copy of the original sheets before cleaning or changing data. A comparison identifies places to investigate; it does not establish which version is correct. Confirm discrepancies against the source system or the person responsible for the data before overwriting records.

Frequently Asked Questions

How do I find rows that are in one Excel sheet but not the other?
Use COUNTIF in a helper column to test each key against the other sheet, and repeat the check in the opposite direction. The two checks reveal records unique to each list.

Can Excel compare two sheets automatically?
Yes. Formulas can flag matches and differences, while Power Query can merge tables and produce a refreshable comparison. You still need to choose the key fields and decide how to handle duplicates and inconsistent data.

How do I compare two sheets without a unique ID?
Use a combination of columns that together identify a record, such as order number and product code. If no reliable combination exists, inspect and resolve ambiguous rows rather than trusting a name or position alone.

Does XLOOKUP compare every column in a row?
No. XLOOKUP finds a value using a lookup key and returns a selected value from another range. To compare several fields, add comparisons for those fields or use a multi-column method such as Power Query.

Can I compare sheets in Excel for the web or on a Mac?
Most worksheet formulas in this guide also work in current Excel for the web and Excel for Mac. Power Query features and menus can differ by platform and version, so use the Data tab in your edition to check which query commands are available.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.