Function Problems

Compare Excel Rows by Employee ID Across Two Worksheets

John WilsonJohn Wilson Nov 18, 2026 869 views

Question details

Compare all corresponding columns for matching employee position IDs in two worksheets and identify differences.

Compare Excel Rows by Employee ID Across Two Worksheets
Product
Microsoft Excel
Device & OS
not provided
Scenario
Two worksheets contain employee position records that need to be matched by ID rather than by row number.
Observed behavior
The desired result is a comparison showing which values differ for each matching ID and which IDs are missing from either worksheet.
Before you start

Confirm that each employee position ID is unique and stored consistently as text or a number in both worksheets. The example below assumes sheets named Sheet1 and Sheet2, IDs in column A, matching column order, and data in rows 2 through 1000.

Solution 1Recommended

Compare matching employee IDs with formulas

Use a comparison worksheet to flag each differing value, even when matching IDs appear on different rows.

1
Create a comparison sheet

Click the plus button beside the worksheet tabs to add a sheet. Copy the column headers from Sheet1 to the new sheet, with the ID header in column A.

2
List the IDs

In the new sheet, enter =Sheet1!A2 in cell A2 and fill it down through the rows containing IDs on Sheet1.

3
Compare the first data column

In cell B2 of the new sheet, enter =IF(COUNTIF(Sheet2!$A$2:$A$1000,$A2)=0,"ID not found",IF(Sheet1!B2=INDEX(Sheet2!B$2:B$1000,MATCH($A2,Sheet2!$A$2:$A$1000,0)),"Match","Different")).

4
Fill the comparison range

Select B2 and drag its fill handle across all data columns, then down alongside the IDs. Review cells marked Different or ID not found.

5
Find IDs only on Sheet2

In an unused column on Sheet2, enter =IF(COUNTIF(Sheet1!$A$2:$A$1000,A2)=0,"Only in Sheet2","") on row 2 and fill it down for the rows containing IDs.

Compare matching employee IDs with formulas
Check the comparison limits: Extend the row ranges if your data goes beyond row 1000. The formulas use the first matching ID, compare values rather than formatting, and do not distinguish letter case; blank cells and zero may compare as equal.
Compare worksheet records

Compare employee ID rows in WPS Spreadsheets

Open your workbook in WPS Spreadsheets and use INDEX, MATCH, and COUNTIF formulas to compare values by employee position ID.

  1. 1. Open the workbook: Open WPS Spreadsheets and select Open to load the workbook containing Sheet1 and Sheet2.
  2. 2. Add a comparison sheet: Click the plus button beside the sheet tabs, copy the headers from Sheet1, and enter =Sheet1!A2 in A2. Fill the formula down for the IDs you want to compare.
  3. 3. Enter the comparison formula: In B2 of the new sheet, enter =IF(COUNTIF(Sheet2!$A$2:$A$1000,$A2)=0,"ID not found",IF(Sheet1!B2=INDEX(Sheet2!B$2:B$1000,MATCH($A2,Sheet2!$A$2:$A$1000,0)),"Match","Different")).
  4. 4. Review the results: Drag the fill handle across the data columns and down the ID rows. Check the cells marked Different or ID not found.
Flag differences across matching employee ID rowsIdentify IDs that appear in only one worksheetOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and worksheet tabs
microsoft office alternative - wps office

Frequently Asked Questions

Will the formula work if the worksheets have different row orders?

Yes. MATCH locates each ID in Sheet2, so the matching record does not need to be on the same row as it is in Sheet1.

How do I find IDs that exist only in the second worksheet?

On Sheet2, enter =IF(COUNTIF(Sheet1!$A$2:$A$1000,A2)=0,"Only in Sheet2","") in an unused column and fill it down for the populated ID rows.

What happens if an employee position ID appears more than once?

MATCH returns the first matching ID, so later duplicates are not compared separately. Check each ID column for duplicates before using the formula.

Does the formula compare cell formatting or capitalization?

No. The comparison checks values, not formatting, and ordinary text comparisons are not case-sensitive. Use a separate formatting review or a case-sensitive formula if those differences matter.

Can I use a worksheet comparison tool instead?

Yes. A tool such as Professional_Compare can compare worksheets cell by cell or row by row and place results on a new worksheet. Open both workbooks before running the tool and verify that its comparison method suits your ID-based matching needs.