Compare Two Sheets for Matches in Excel: A Practical Guide

How-to basics · Updated November 3, 2026

To compare two Excel sheets for matching records, use a shared key such as an ID or email address and check whether each key appears on the other sheet. A COUNTIF formula is a straightforward option; XLOOKUP can also find a match and return related details.

What “matching” means in a spreadsheet

Before choosing a method, decide what counts as a match. You might want to know whether the same customer IDs appear on both sheets, find records that are missing from one list, or check whether every value in two corresponding rows is identical. These are related tasks, but they are not interchangeable.

For comparing lists, a reliable shared identifier is usually the best starting point. An ID, order number, or email address can link records even when the rows appear in a different order. Names are less dependable: two people can share a name, and the same person’s name may be spelled or formatted differently on different sheets.

For example, imagine Sheet1 contains this month’s order IDs and Sheet2 contains last month’s. The useful question may be whether each current order ID appeared last month—not whether the order happens to be on the same row. Row-by-row comparisons only make sense when both sheets have already been sorted and aligned in the same way.

Find whether a value appears on the other sheet

For a simple yes-or-no check, add a helper column beside the list on Sheet1. The formula below looks for the ID in A2 anywhere in the specified ID range on Sheet2. It does not require the matching records to be in the same row.

Assumed layout: IDs are in Sheet1 cells A2:A100 and Sheet2 cells A2:A100, and the formula is entered in Sheet1 B2. If A2 contains 1042 and that ID appears on Sheet2, the result is Match; if it does not, the result is No match.

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

Fill the formula down alongside the rest of the Sheet1 list. The dollar signs keep the Sheet2 lookup range fixed as the formula moves, while the reference to A2 changes to A3, A4, and so on. In Excel for Microsoft 365 on Windows, you can drag the fill handle at the lower-right corner of the formula cell, or copy and paste the formula into the remaining rows.

If a worksheet name contains spaces, enclose it in apostrophes in the reference. For example, a tab named January List would be referenced as ‘January List’!$A$2:$A$100. The same formula structure works when the sheets are in the same workbook. Comparing separate workbooks is possible too, but external links can require the source workbook to be available and may prompt you to update links.

Find missing records in either direction

A check from Sheet1 against Sheet2 answers only one direction of the question. It identifies Sheet1 records that are absent from Sheet2, but it does not identify records that exist only on Sheet2. To find those, create a similar helper column on Sheet2 and check its IDs against Sheet1.

Assumed layout: IDs are in Sheet2 A2:A100 and Sheet1 A2:A100, and the formula is entered beside the first ID on Sheet2. If the value in Sheet2 A2 is 1088 and that value is absent from Sheet1, the formula returns Missing from Sheet1.

=IF(COUNTIF(Sheet1!$A$2:$A$100,A2)=0,"Missing from Sheet1","Present")

To build a complete reconciliation, review both directions. A record labelled Match on the first sheet is shared; a record labelled Missing from Sheet2 appears only on Sheet1. The reverse check reveals the records appearing only on Sheet2. This distinction is helpful when comparing exports, because one list may contain new records while the other contains records that have since been removed.

For a compact summary, filter each helper column to show only its missing result. Excel’s filter controls are available from Data > Filter, or from Home > Sort & Filter > Filter. Filtering changes which rows are displayed, not the underlying data, so clear the filter when you want to see all records again.

Return information from the matching row

Sometimes a match is not enough: you may also want to bring back a description, status, or amount associated with the matching ID. XLOOKUP can find the ID in one column and return data from another. It is available in Microsoft 365 and Excel 2021 or later; older Excel versions may need a different lookup method.

Assumed layout: Sheet1 has an ID in A2; Sheet2 has IDs in A2:A100 and corresponding status, region, and date in B2:D100. Enter this formula on Sheet1. If A2 is found and the matching Sheet2 status is Active, the first returned value is Active; if there is no matching ID, the result is Not found.

=XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$B$2:$D$100,"Not found",0)

The formula returns the matching row from columns B through D. In a modern Excel version, the three results can spill into neighbouring cells, so keep those cells empty. The final 0 requests an exact match. XLOOKUP uses exact matching by default, but stating the match mode makes the intent clear.

Returning related data is useful for investigation, but it is not by itself a complete comparison. If a value is not found, that says the key is absent from the lookup range; it does not explain why. Check whether the ID was mistyped, whether the lookup range includes all rows, and whether the records use consistent formatting.

Compare corresponding cells or whole rows

If each row on one sheet is meant to correspond to the same row on the other sheet, compare the fields directly. This approach assumes the rows are already aligned—for example, both sheets were sorted by the same unique ID. Without that condition, a formula can report differences simply because the same record is on a different row.

Assumed layout: the same record is on row 2 of both sheets, with the value being compared in column B. Enter the formula beside the records. If both B2 cells contain Paid, the result is Same; if one contains Pending, the result is Different.

=IF(B2=Sheet2!B2,"Same","Different")

To compare several fields, place a similar formula beside each field or create a combined row check. A field-by-field check is easier to interpret: it shows whether a status, date, or amount differs, rather than merely flagging the row. For dates and numbers, consider whether the stored values are genuinely different or only displayed differently because of cell formatting.

Excel’s ordinary equality comparison is not case-sensitive. If capitalization matters—for example, if ABC and Abc must count as different—use EXACT instead.

Assumed layout: the values to compare are in Sheet1 A2 and Sheet2 A2. If they contain ABC and Abc, respectively, this formula returns Different; identical text with identical capitalization returns Same.

=IF(EXACT(A2,Sheet2!A2),"Same","Different")

Prepare the data before comparing

Many apparent mismatches come from inconsistent data rather than genuinely different records. A value may have an extra space, a different date interpretation, or a number stored as text. Before changing the comparison method, inspect a few records that appear to be missing and check both the displayed value and the underlying cell content.

  • Check for leading or trailing spaces. “North” and “North ” can behave differently in comparisons. TRIM can remove ordinary extra spaces, although it does not remove every special whitespace character.
  • Check numbers stored as text. An ID that looks like 1042 may be stored as text on one sheet and as a number on the other. Decide on one consistent representation before comparing.
  • Check dates carefully. Two dates can display in different formats but represent the same date value. Conversely, text that looks like a date may not be stored as a real Excel date.
  • Preserve meaningful leading zeros. Codes such as 0042 are not necessarily the same identifier as 42. If zeros matter, store both values consistently as text rather than converting codes to numbers.

For cleaning ordinary spaces in a text field, TRIM can be used in a helper column, then filled down and applied to both sheets before comparison. CLEAN removes certain nonprinting characters, but neither function is a universal fix for all copied or imported data.

Assumed layout: the uncleaned text value is in A2. If A2 contains the text “ North ” with ordinary extra spaces, this formula returns North.

=TRIM(CLEAN(A2))

Keep the original columns until you have verified the cleaned results. If the data includes nonbreaking spaces from a web page or system export, those may need a separate cleanup step. Avoid replacing original IDs without checking whether the formatting carries meaning.

Handle duplicates and other common pitfalls

COUNTIF answers whether a value appears at least once. It does not tell you how many times it appears, and it will still return Match if an ID occurs twice on one sheet and once on the other. If duplicates matter, inspect their counts rather than treating presence as proof that the lists are identical.

Assumed layout: the ID being checked is in Sheet1 A2 and the comparison range is Sheet2 A2:A100. If ID 1042 appears twice in that range, the formula returns 2.

=COUNTIF(Sheet2!$A$2:$A$100,A2)

COUNTIF also treats the asterisk and question mark as wildcard characters in criteria. If identifiers can literally contain those characters, a COUNTIF check may not mean what you intend. Choose a comparison method suited to the actual ID format and test it with representative values rather than assuming every identifier is plain text.

Other common causes of confusing results include lookup ranges that stop before the last row, formulas filled down incorrectly, blank cells treated as records, and comparing non-unique names instead of stable IDs. If the source data changes regularly, convert each list to an Excel table using Insert > Table so ranges can expand as rows are added. Check the table’s headers and ensure new records are included in the comparison.

Choose the right method for the job

The best method depends on what you need to learn. Use a presence check for a quick reconciliation, a lookup when you need details from the matching record, and direct cell comparisons when rows are already aligned.

  • Shared or missing IDs: Use COUNTIF in a helper column, then check each sheet in both directions.
  • Matching details to bring back: Use XLOOKUP when it is available and the key is unique.
  • Differences in aligned rows: Compare corresponding fields, making sure the rows have first been sorted or matched by a key.
  • Large or repeated imports: Consider Power Query, especially when you need to repeat the reconciliation. In Excel for Microsoft 365 on Windows, use Data > Get Data to import data, then use the merge option in the Power Query Editor to match tables on a selected key.

Power Query is useful for repeatable data preparation, but its merge settings still matter. Select the correct columns as keys and choose the join type that matches your question: for instance, a left anti join can isolate rows from the first table with no match in the second. Review the preview and the resulting row counts. It is more setup than a helper formula for a small, one-time comparison.

Conditional formatting can make results easier to scan, but it should complement a clear comparison formula rather than replace it. One simple approach is to highlight the helper column’s Match or No match results through Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. This highlights the visible result labels on the current sheet; it does not independently verify the records.

Frequently Asked Questions

Can I compare two sheets when the rows are in a different order?
Yes. Compare a unique key in one sheet against the key column in the other with COUNTIF or XLOOKUP. Do not compare row 2 with row 2 unless the records have already been aligned.

Does Excel’s comparison distinguish uppercase and lowercase?
Ordinary equality comparisons are not case-sensitive. Use the EXACT function when capitalization must match exactly.

How do I find entries that are on one sheet but not the other?
Add a helper-column check on each sheet. Filter the first check for missing entries, then repeat in the opposite direction to find entries unique to the other sheet.

Why does Excel say there is no match when the values look identical?
Check for extra spaces, numbers stored as text, date values, leading zeros, and incomplete lookup ranges. A displayed value can look identical while its stored value or formatting differs.

Can I compare two separate Excel workbooks?
Yes. You can reference a range in another workbook or import the data with Power Query. External references may depend on the source file’s location and can require link updates, so keep the source accessible and verify the results after moving files.

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.