Compare Excel Rows by Employee ID Across Two Worksheets
Question details
Compare all corresponding columns for matching employee position IDs in two worksheets and identify differences.

- 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.
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.
Compare matching employee IDs with formulas
Use a comparison worksheet to flag each differing value, even when matching IDs appear on different rows.
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.
In the new sheet, enter =Sheet1!A2 in cell A2 and fill it down through the rows containing IDs on Sheet1.
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")).
Select B2 and drag its fill handle across all data columns, then down alongside the IDs. Review cells marked Different or ID not found.
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 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. Open the workbook: Open WPS Spreadsheets and select Open to load the workbook containing Sheet1 and Sheet2.
- 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. 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. 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.

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.




