Function Problems

Compare Two Excel Workbooks by Unique ID with Lookup Formulas

John WilsonJohn Wilson Nov 12, 2026 870 views

Question details

Compare records in two differently arranged Excel workbooks by using a shared unique ID to retrieve and compare related fields.

How to Compare Two Excel Workbooks Using a Unique ID
Product
Microsoft Excel workbooks
Device & OS
not provided
Scenario
The user needs to match records such as Username, Name, Age, and Position across two source workbooks with different column layouts.
Observed behavior
Fields must be returned for the same unique ID from each workbook and labeled as matching or different.
Before you start

Confirm that both workbooks use the same unique ID for each record and that the ID column does not contain duplicate values. Format the ID columns consistently as Text or Number before creating lookup formulas.

Solution 1Recommended

Use XLOOKUP to retrieve fields from both Excel workbooks

Use this method when XLOOKUP is available and the ID column may not be the first column in a source worksheet.

Create a comparison worksheet with one ID per row. Retrieve each field from Workbook 1 and Workbook 2 into separate columns, then use an IF formula to compare the returned values.

1
Create the comparison table

In a new comparison workbook or worksheet, enter the unique IDs in column A. Add headers such as Username - File 1, Username - File 2, Name - File 1, Name - File 2, and Result.

2
Retrieve a field from the first workbook

In the first result column, enter an XLOOKUP formula such as =XLOOKUP($A2,'[Excel1.xlsx]Sheet1'!$A$2:$A$100,'[Excel1.xlsx]Sheet1'!$B$2:$B$100,"Not found"). Replace the first range with the ID column and the second range with the field you want to return.

3
Retrieve the same field from the second workbook

In the adjacent column, use the equivalent formula for the other source file, for example =XLOOKUP($A2,'[Excel2.xlsx]Sheet1'!$A$2:$A$100,'[Excel2.xlsx]Sheet1'!$B$2:$B$100,"Not found").

4
Compare the returned values

In the Result column, enter =IF(B2=C2,"Match","Different") to compare the two returned values. Copy the lookup and comparison formulas down for all IDs.

5
Repeat for additional fields

Change the XLOOKUP return range to retrieve Name, Age, Position, or other fields, then add an IF comparison formula for each pair of columns.

Use XLOOKUP to retrieve fields from both Excel workbooks
Keep the source files accessible: External workbook references work most reliably while both source workbooks are open. If a source file is moved or renamed, update the formula references.
Compare workbook records with WPS Spreadsheets

Compare ID-Based Records Across Excel Workbooks in WPS Spreadsheets

WPS Spreadsheets can open Excel workbooks and use lookup and comparison formulas to retrieve records by unique ID. Build a comparison sheet, return each field from both files, and identify matching or different values in one place.

  1. 1. Open the source workbooks: Open both Excel files in WPS Spreadsheets and confirm the worksheet name and ID column used in each file.
  2. 2. Create a comparison worksheet: Click the plus button beside the sheet tabs to add a worksheet, then enter the list of unique IDs in column A.
  3. 3. Add lookup formulas: Select a result cell and enter an XLOOKUP or VLOOKUP formula that uses the ID in column A and returns a field from the first workbook. Repeat for the second workbook.
  4. 4. Flag differences: In a result column, enter an IF formula such as =IF(B2=C2,"Match","Different"), then drag the fill handle down to compare all records.
Use XLOOKUP or VLOOKUP to match records by a unique ID.Compare names, ages, positions, and other returned fields with IF formulas.Open and edit Microsoft Excel .xlsx and .xls workbooks with format compatibility.Use a familiar spreadsheet interface for a smooth transition from Excel.
microsoft office alternative - wps office

Frequently Asked Questions

Can I compare workbooks when the columns are in a different order?

Yes. Use XLOOKUP with the unique ID as the lookup value and select the correct return range for each field. The ID does not need to be in the first column when using XLOOKUP.

Why does my lookup return Not found for an ID that exists?

Check whether one workbook stores the ID as text and the other stores it as a number. Also remove leading or trailing spaces and verify that the lookup range includes the ID.

How do I compare more than one field for each ID?

Create two lookup columns for every field, one from each workbook, then add a comparison formula such as =IF(B2=C2,"Match","Different") for each field pair.

When should I use VLOOKUP instead of XLOOKUP?

Use VLOOKUP when XLOOKUP is not available and the unique ID is the first column of the selected lookup range. Use XLOOKUP for more flexible return ranges.