How to Compare Two Excel Sheets and Return Only Missing Names in WPS Office
Question details
The user needs to identify names that are present in Sheet B but do not exist anywhere in Sheet A by using a lookup formula that searches a range instead of comparing matching cells or rows.

- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user maintains two lists or sheets and wants to quickly isolate names that exist only in Sheet B.
- Observed behavior
- A formula in Sheet B should return the missing name when no match exists in Sheet A and return a blank when a match is found.
Open the workbook in WPS Office and confirm the exact worksheet names and data range—for example, SheetA!$A$2:$A$6 and the first Sheet B cell A2—before entering the formula.
Use MATCH and IF to Return Only Missing Names from Sheet B
Use this formula when you need to compare every value in one Sheet B column against a full range in Sheet A and display only the missing names.
The MATCH function searches the Sheet A range for the value in Sheet B. When no match is found, MATCH returns an error; the ISERROR and IF functions convert that error into the original name, while matched values produce a blank cell.
Click the cell where you want the missing-name check to appear, for example B2 next to the first name in Sheet B.
Type =IF(ISERROR(MATCH(A2,SheetA!$A$2:$A$6,0)),A2,"") and press Enter. Replace A2 with the first Sheet B name cell and SheetA!$A$2:$A$6 with your actual Sheet A range.
Drag the fill handle from the formula cell down through all rows that contain names in Sheet B to apply the comparison to the entire list.
Cells that show a name are missing from Sheet A; blank cells mean a match was found.

Use COUNTIF as an Alternative to MATCH and ISERROR
Use COUNTIF if you prefer a formula that counts matching values and avoids the MATCH error-handling combination.
Compare Excel Sheets and Flag Missing Names with WPS Office
Use WPS Spreadsheets to open your Excel workbook, apply the MATCH/IF or COUNTIF formula, and automatically flag names that exist in Sheet B but not in Sheet A. WPS Office preserves your Excel formulas and formatting while remaining free and lightweight.
- 1. Download and install WPS Office: Download WPS Office Free from the official WPS website, install it, and open WPS Spreadsheets.
- 2. Open your Excel workbook: Click Menu > Open, select the workbook that contains Sheet A and Sheet B, and confirm your worksheet names.
- 3. Enter the comparison formula: In Sheet B, click the first result cell and type =IF(ISERROR(MATCH(A2,SheetA!$A$2:$A$6,0)),A2,""). Press Enter.
- 4. Fill down and review results: Drag the fill handle through the list, review the names that appear because they are missing from Sheet A, and save the file as an .xlsx workbook.

Frequently Asked Questions
Can I compare two separate Excel files rather than two sheets in the same workbook?
Yes. Put the formula in the destination workbook and reference the other workbook's full path, file name, sheet name, and range. For example: =IF(ISERROR(MATCH(A2,'[Book1.xlsx]SheetA'!$A$2:$A$6,0)),A2,""). The other file must be open or the full path must be included.
How do I return missing names from Sheet A instead of Sheet B?
Place the formula in Sheet A and point the lookup range to Sheet B. For example, use =IF(ISERROR(MATCH(A2,SheetB!$A$2:$A$6,0)),A2,"") in a Sheet A result column.
Why does my formula return blanks for every cell?
Check that the first cell reference matches your data. If your names start in a cell other than A2, replace A2 with that cell. Also verify the Sheet A range is correct and the worksheet name is spelled exactly, including spaces or extra characters.
Can this formula compare numbers or IDs instead of names?
Yes, the MATCH/IF and COUNTIF/IF formulas work with text, numbers, and ID values. Simply point the formula at the cell and range that contain the values you want to compare.
What if my sheet name contains spaces?
Wrap the sheet name in single quotes. For example: =IF(ISERROR(MATCH(A2,'Sheet A'!$A$2:$A$6,0)),A2,"").




