Function Problems

How to Compare Two Excel Sheets and Return Only Missing Names in WPS Office

Camila MilosovichCamila Milosovich Nov 13, 2026 870 views

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.

How to Compare Two Excel Sheets and Return Only Missing Names in WPS Office
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the first result cell in Sheet B

Click the cell where you want the missing-name check to appear, for example B2 next to the first name in Sheet B.

2
Enter the MATCH and IF formula

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.

3
Fill the formula down the column

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.

4
Review the results

Cells that show a name are missing from Sheet A; blank cells mean a match was found.

Use MATCH and IF to Return Only Missing Names from Sheet B
Formula result: The formula returns the name when it is missing from Sheet A and a blank when a match is found.
Direct solution

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. 1. Download and install WPS Office: Download WPS Office Free from the official WPS website, install it, and open WPS Spreadsheets.
  2. 2. Open your Excel workbook: Click Menu > Open, select the workbook that contains Sheet A and Sheet B, and confirm your worksheet names.
  3. 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. 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.
Returns missing names automatically using familiar MATCH, IF, and COUNTIF formulasFull compatibility with Microsoft Excel .xlsx, .xls, and .csv filesLightweight and free spreadsheet tools for list comparison and reconciliationNo manual row-by-row checking or re-keying of missing names
microsoft office alternative - wps office

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,"").