XLOOKUP to Compare Two Sheets in Excel Step by Step

If Sheet1 has IDs in A2:A100 and Sheet2 has IDs in A2:A100, enter this in Sheet1 cell B2 to check whether each ID appears on Sheet2: =IF(ISNA(XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$A$2:$A$100,NA())),"Missing","Found"). Fill it down to label each row Found or Missing; XLOOKUP searches the second sheet for the value in A2 and returns its match when it exists.

What XLOOKUP Can Compare Between Sheets

XLOOKUP can look for a value on one worksheet and return a related value from another. That makes it useful for comparing lists of customer numbers, product codes, invoice IDs, dates, or names. The sheets do not need to be in the same workbook, but formulas are simplest to maintain when both sheets are in one file.

A common misconception is that XLOOKUP compares two entire worksheets automatically. It does not. You choose a lookup value and a lookup range, then decide what result you want: a match status, a value from the other sheet, or a difference between corresponding fields. For a row-by-row list check, put a formula beside the first list and fill it down.

The example assumes Sheet1 and Sheet2 each contain one record per row, with the unique identifier in column A. In practice, use a stable key such as a product ID rather than a person’s name: names can repeat or be spelled differently, while a well-maintained ID is intended to identify one record.

Set Up the Sheets and Enter a Match Check

Keep the column containing the values to match consistent across both sheets. For example, put employee IDs in column A on each sheet, with a heading in row 1 and records starting in row 2. On Sheet1, place the status formula in an empty column, such as B2, then copy it down using the fill handle or copy and paste.

The dollar signs in the lookup ranges make those references absolute, so they stay fixed as the formula is filled down. A2 has no dollar signs, so it changes to A3, A4, and so on. Adjust the row limits to cover your data, or use full-column ranges if that suits your workbook. Bounded ranges can be easier to audit and may calculate more efficiently in very large workbooks.

XLOOKUP uses exact matching by default. For IDs and other discrete values, that is usually what you want: 104 should match 104, not a nearby number. If your worksheet has spaces, inconsistent text-versus-number types, or differently formatted IDs, correct those issues before relying on the results.

Compare Values for IDs That Match

Finding that an ID exists is different from checking whether the associated information agrees. Suppose Sheet1 has IDs in A2:A100 and amounts in B2:B100, and Sheet2 has IDs in A2:A100 and amounts in B2:B100. Enter this formula in Sheet1 cell C2 to find the corresponding amount on Sheet2 and compare it with Sheet1’s amount:

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

For example, if A2 is ID 104 and B2 is 125, and the row for ID 104 on Sheet2 also has 125 in column B, the result is Same. If Sheet2 has 130 for ID 104, the result is Different. If ID 104 is absent, the result is Missing ID. Fill the formula down to check the remaining rows.

COUNTIF distinguishes a missing ID from a matched record whose returned amount is blank. XLOOKUP then returns the amount associated with the first matching ID. This formula compares values using Excel’s normal equality rules; it does not apply a tolerance to small differences in decimal amounts. If a difference of a few cents should count as the same, decide on an appropriate tolerance for your data rather than treating every unequal value as a substantive error.

Check Both Directions, Not Just One

A one-way check answers “Which IDs on Sheet1 are absent from Sheet2?” It does not reveal IDs that appear only on Sheet2. To find omissions in both directions, repeat the missing-ID check with the sheets reversed: place a status column on Sheet2 and look up each Sheet2 ID in Sheet1. This gives you two lists of missing records.

Also consider whether you need to compare every field or only selected fields. A match in the ID column confirms that a record can be found; it does not confirm that its name, address, price, or status is identical. Add separate comparisons for important fields, or return a combined review status only after deciding how blank cells and legitimate differences should be handled.

When the worksheets have different row orders, XLOOKUP is particularly useful because it searches by key rather than assuming that row 12 on one sheet corresponds to row 12 on the other. That is safer than comparing cells in the same row when either list may have been sorted, filtered, or updated independently.

Duplicates, Blanks, and Data-Quality Checks

XLOOKUP returns one result when it finds a value. By default, it searches from the first item to the last and returns the first matching record. If an ID appears twice on Sheet2 with different amounts, the comparison formula will use the amount from the first occurrence; it will not warn you that the ID is duplicated. Check that the chosen key is unique, or investigate duplicates before treating the comparison as a complete reconciliation.

Blank keys need deliberate treatment. If A2 is blank, a lookup can find a blank cell in the lookup range, which may make an empty row look like a match. If your list includes trailing blank rows, exclude them from the comparison or add a separate blank-row check. Do not assume that every blank means a missing record: a blank in a descriptive field may be valid, while a blank identifier often means the row needs review.

Text and numbers that look alike can still be different values. For example, an ID stored as the number 104 may not match text stored as “104” in another sheet. Leading or trailing spaces can also prevent an exact match. Inspect the source data and standardize the key columns before comparing. Avoid changing IDs into numbers if leading zeros are meaningful, as that can turn an ID such as 00104 into 104.

For case-sensitive comparisons, remember that ordinary XLOOKUP matching is not case-sensitive: codes such as ab12 and AB12 are treated as equivalent. If letter case is part of the identifier, use a case-sensitive approach based on the EXACT function and a carefully designed formula, rather than assuming the standard XLOOKUP check distinguishes case.

Make Results Easier to Review

Keep the result labels clear and consistent, such as Found, Missing ID, Same, and Different. Then use the filter arrows on the header row to show only records needing attention. Filtering the status column is often more practical than scanning a long list of formulas, and it leaves the source data intact.

You can also apply conditional formatting to the status column. Select the result cells, then use Home > Conditional Formatting > Highlight Cells Rules > Text that Contains to format labels such as Missing ID or Different. Choose colors that remain understandable when printed or viewed by people who cannot distinguish certain colors; retain the text labels rather than relying on color alone.

Before sharing a comparison, check that the formula reaches the last data row, that both sheets use the intended key column, and that the two-way check is complete if additions and omissions matter. A formula result is only as reliable as its ranges and source data. If rows are added frequently, Excel tables can make a comparison easier to extend as the data grows, though structured references require adapting the formula to the table’s column names.

Compatibility and Alternatives

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. It is not available in Excel 2019 or Excel 2016, so a workbook using it may show a function-name error in those older versions. The formula syntax is essentially the same in Excel for Windows, Mac, and the web; the examples here use Microsoft 365 for Windows.

For older Excel versions, a common alternative is INDEX with MATCH, or VLOOKUP when the lookup column is positioned to the left of the return column. Those functions require different formulas and have different constraints. For a one-off visual check, sorting both lists and using Excel’s duplicate or conditional-formatting tools may help, but sorting alone does not reliably reconcile records when the lists contain different items or row counts.

For a repeatable comparison involving many columns, inconsistent source files, or large data-cleaning steps, Power Query can be a better fit than a long collection of worksheet formulas. It can merge tables using selected columns and show records found in one source, the other, or both. XLOOKUP remains convenient when you want a transparent, cell-level result that updates as the workbook data changes.

Frequently Asked Questions

Can XLOOKUP compare two sheets in different workbooks?
Yes. Both workbooks need to be available to Excel for the external reference to work reliably. You can create the formula by selecting the lookup and return ranges in the other workbook, or use a properly formed external workbook reference. If the source file is moved or renamed, the link may need updating.

Does XLOOKUP return every matching row?
No. A standard XLOOKUP returns one result: the first match in its search order by default. If a key has multiple records and you need to inspect all of them, use a method designed to return multiple results, such as FILTER in supported Excel versions, or remove or resolve duplicates first.

Why does XLOOKUP show Missing when the values look identical?
Check whether one value is stored as text and the other as a number, whether either value contains extra spaces, and whether the formula points to the correct sheet and range. Also check that the IDs have not lost meaningful leading zeros. Similar appearance on screen does not guarantee identical underlying values.

Can I compare two columns without matching IDs?
You can compare the cells in corresponding rows directly, but that only makes sense if the row order is guaranteed to align. When records may be sorted differently, match by a unique ID first; otherwise, a row-by-row comparison can report false differences or miss mismatched records.

Is XLOOKUP case-sensitive?
No. Standard XLOOKUP matching treats uppercase and lowercase letters as equivalent. If case differentiates codes in your system, use a case-sensitive comparison method and verify it against representative records before relying on the results.

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.