Yes. To match two Excel sheets, compare a shared key such as an ID or email address. If Sheet1 has IDs in A2:A1000, Sheet2 has IDs in A2:A1000, and the details you want returned are in Sheet2 B2:B1000, enter this in Sheet1 B2 and fill down: =XLOOKUP(A2,Sheet2!$A$2:$A$1000,Sheet2!$B$2:$B$1000,"Not found"). It returns the matching detail or “Not found” when there is no match.
Choose the right way to match the sheets
The best method depends on what you mean by “match.” You might want to check whether each value on one sheet appears on the other, bring related information across, compare corresponding rows, or produce a list of records missing from either sheet. A formula that answers one of these questions may not answer the others.
- Bring back related information: Use XLOOKUP to find a key on the second sheet and return a value from another column.
- Check whether a key exists: Use COUNTIF for a simple Found or Missing result.
- Compare corresponding cells: Compare the same row and column on each sheet, when row order is meaningful.
- Reconcile larger tables: Use Power Query to merge tables and identify unmatched records.
Before choosing, identify a reliable key. An invoice number, employee ID, or unique email is usually better than a person’s name, because names can be repeated or entered differently. Also decide whether the comparison should treat repeated keys as separate records or simply report whether a key appears at least once.
Use XLOOKUP to return a matching value
XLOOKUP is a practical choice when you want a value from one sheet beside its corresponding key on another. In the opening example, the formula searches for the value in Sheet1 A2 in Sheet2 A2:A1000, then returns the value from the same position in Sheet2 B2:B1000. The fourth argument supplies a readable result for a key that is not found. XLOOKUP uses exact matching by default, which suits most ID and record-matching tasks.
Enter the formula in the first result row, then use the fill handle to copy it down. Keep the lookup and return ranges the same size; otherwise, the formula can return an error. The dollar signs lock the Sheet2 ranges when you fill the formula, while A2 changes to A3, A4, and so on.
If the worksheet tab has spaces in its name, enclose the name in single quotation marks. For example, if the lookup sheet is called “April Data,” the reference is written as 'April Data'!$A$2:$A$1000. Use the actual tab name in the formula. If the two files are separate workbooks, both files generally need to be available for Excel to resolve external references reliably.
XLOOKUP returns the first matching record it encounters. If the lookup key occurs more than once, it does not automatically return every matching row or warn that the key is duplicated. Check for duplicates when uniqueness matters, or use Power Query when you need to reconcile all records.
Check whether a value exists on the other sheet
If you only need a yes-or-no answer, COUNTIF is simpler than returning a related field. With IDs in Sheet1 A2:A1000 and the list to check in Sheet2 A2:A1000, enter this in Sheet1 B2 and fill down:
=IF(COUNTIF(Sheet2!$A$2:$A$1000,A2)>0,"Found","Missing")
COUNTIF counts how many cells in the second range meet the criterion. The formula reports Found if the count is greater than zero and Missing if it is zero. This is useful for checking a roster against a registration list, or comparing an order list with a shipment list. It does not say how many times the key appears; to see that count instead, with the same ranges, use =COUNTIF(Sheet2!$A$2:$A$1000,A2).
COUNTIF criteria can treat the asterisk (*) and question mark (?) as wildcards. That can affect unusual keys containing those characters. For ordinary numeric IDs, dates, names, and email addresses, the basic formula is often sufficient; for special cases, use an exact lookup or validate the key format before relying on the result.
Compare matching rows cell by cell
Sometimes each row on the first sheet is meant to correspond to the same row on the second. In that case, a lookup is unnecessary: compare the cells directly. This method assumes the rows are already aligned and that the same fields are in the same columns. With values to compare in columns A through C on both sheets, enter this in Sheet1 D2 and fill down:
=IF(AND(A2=Sheet2!A2,B2=Sheet2!B2,C2=Sheet2!C2),"Match","Different")
The result is Match only if all three comparisons are true. If rows may be in a different order, this formula can produce misleading results because it compares row 2 with row 2 rather than searching for the same record elsewhere. Match or sort by a shared key first, or use a lookup-based approach.
For one cell only, with the values being compared in A2 on each sheet, use =IF(A2=Sheet2!A2,"Match","Different"). Be aware that Excel treats two empty cells as equal in this kind of comparison. If a blank should count as incomplete rather than as a match, add a separate blank check or apply a data-validation rule before comparing.
Find duplicates and understand repeated keys
A matching key is most useful when it identifies one record. If a key appears several times, XLOOKUP returns just one of those records, and a Found result from COUNTIF confirms presence but not uniqueness. Duplicate keys can be legitimate—for example, one customer may have several orders—so decide whether the key should identify a person, an order, or a particular combination of fields.
With the key in Sheet2 A2:A1000, enter this beside the first key on Sheet2, for example in B2, and fill down to count its occurrences:
=COUNTIF($A$2:$A$1000,A2)
A result above 1 means the key occurs more than once in the checked range. If an order is uniquely identified by both customer ID and order number, build a consistent combined key in helper columns on each sheet, or compare both fields as part of the matching logic. Do not assume that matching names alone means two rows are the same record.
Fix spaces, inconsistent text, and data types
A formula can report Missing even when two values look identical on screen. Common causes include leading or trailing spaces, nonbreaking spaces copied from websites, inconsistent punctuation, and one key stored as text while the other is stored as a number. For example, the text value “00125” is not the same key as the number 125 if leading zeros are significant.
Inspect a few unmatched values directly before changing a whole dataset. Check whether IDs should retain leading zeros, whether dates are real Excel dates rather than text, and whether names differ by punctuation or spelling. Avoid converting identifiers to numbers when their formatting carries meaning.
For text values, a helper column can remove many ordinary extra spaces and nonprinting characters. With the original text in A2, enter this in a helper cell and fill down:
=TRIM(SUBSTITUTE(CLEAN(A2),CHAR(160)," "))
CLEAN removes many nonprinting characters, SUBSTITUTE changes the nonbreaking space represented by CHAR(160) to a regular space, and TRIM removes leading and trailing regular spaces and reduces repeated internal spaces. Apply the same cleanup to the corresponding key columns on both sheets, then match the cleaned helper columns. This does not correct misspellings, different punctuation, or every possible invisible character, so review exceptions rather than treating cleanup as proof that records are identical.
For numbers stored as text, use a deliberate conversion only after confirming the values are numeric identifiers and that leading zeros do not matter. Text-to-Columns or Excel’s conversion warning can help with some imported data, but changing the source values may be inappropriate for codes or account numbers.
Highlight results and review exceptions
A result column makes mismatches easy to filter and audit. After adding a Found/Missing or Match/Different formula, turn on filters with Home > Sort & Filter > Filter, then filter the result column to Missing or Different. This lets you focus on exceptions without deleting or hiding the original records permanently.
You can also color the result cells. Select the formula-result range, then choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains and enter Missing or Different as the text to format. The rule colors the helper results; it does not independently compare records across sheets. Keep the underlying formula visible so that a color is not the only evidence for a match.
For one-time visual checks, Excel’s View Side by Side feature can display two workbook windows together, but it does not determine whether records match. Similarly, sorting both sheets by a key may make a manual comparison easier, but a formula or query is more dependable for large lists and helps preserve a reproducible audit trail.
Use Power Query for larger reconciliations
Power Query is useful when the sheets contain many rows, need repeatable cleanup, or should produce a full list of missing records. On Excel for Microsoft 365 for Windows, format each source range as a table, select a cell in each table, and choose Data > From Table/Range to load it as a query. In the Power Query Editor, use Home > Merge Queries, select the two queries, and click the corresponding key column in each preview.
Choose a join type according to the result you need. A Left Anti join returns rows from the first table that have no matching key in the second; a Right Anti join does the reverse. A Full Outer join keeps rows from both sides, including unmatched records. An Inner join keeps records with matching keys in both tables. Expand the merged column when you want fields from the other table, then use Home > Close & Load to return the result to Excel.
Power Query does not update its output every time a source cell changes; refresh the query to incorporate changes. It also requires deliberate choices about duplicate keys and data types. If you merge on a non-unique key, a record can appear more than once in the output. Excel for the web and Mac may not offer the same complete editing experience or identical interface as Excel for Windows, so check the Power Query options available in the version you use.
Frequently Asked Questions
Can Excel compare two sheets automatically?
Yes. Use a formula such as XLOOKUP or COUNTIF to check keys, or Power Query to merge tables. Formulas recalculate as their referenced cells change; a Power Query result needs to be refreshed.
How do I find names on one sheet that are missing from another?
Put a COUNTIF check beside each name, with the other sheet’s name range as the counted range. Filter the results to Missing. Names are not always unique, so use an ID or another reliable key when possible.
Can XLOOKUP compare two sheets with different row orders?
Yes. XLOOKUP searches the specified key range rather than comparing the same row number, so the rows can be in a different order. The key still needs to identify the intended record.
Why does Excel say a value is missing when it looks the same?
Check for hidden spaces, nonbreaking spaces, text-versus-number differences, leading zeros, and spelling or punctuation differences. Compare the underlying values and clean corresponding key columns consistently.
Does XLOOKUP return every duplicate match?
No. It returns one matching result, normally the first match in the search range. Use a duplicate count to investigate repeated keys, or Power Query when you need to preserve and review multiple matching records.
Related
- CSV File to Excel Converter: Convert Without Data Loss
- Data Cleaning Tools in Excel: What to Use and When
- Data Cleaning in Excel: A Practical Example
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel XLOOKUP Examples for Common Spreadsheet TasksMicrosoft Excel’s XLOOKUP finds a value in one range and returns the corresponding value from another. For example,…
- Conditional Formatting Formula If Statement in ExcelIn Excel, a conditional-formatting formula must evaluate to TRUE or FALSE for the cell or row you want…
- Average in Excel Between Two Values: Formula & TipsTo average numbers between two values in Excel, use AVERAGEIFS with a lower-bound and an upper-bound condition. For…
- Excel Wildcard Text Search: Formulas and Examples GuideTo check whether text in A2 contains the search term in E2, use =ISNUMBER(SEARCH(E2,A2)). It returns TRUE when…
- Excel Table of Contents: Create a Clickable Sheet IndexExcel does not have a dedicated command that automatically creates a table of contents for every worksheet. The…
- Excel Formula for Percentage Increase: Examples and TipsTo calculate percentage increase in Excel, subtract the old value from the new value, then divide by the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.