Check for Missing Names in Another Excel Column
Question details
Compare two name ranges and return TRUE or FALSE depending on whether every nonblank name in the first range appears in the second.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Compare name lists without helper columns or VBA, including lists with blanks and duplicate values.
- Observed behavior
- A single formula should report whether any nonblank name in the first range is missing from the second range.
Identify the range containing the names to check and the range to search. The example uses A2:A9 as the first list and B2:B5 as the comparison list; adjust both ranges to fit your worksheet.
Compare the name ranges with SUMPRODUCT and MATCH
Use one formula to check whether every nonblank name in column A appears in column B.
The formula ignores blank cells and empty-string results in column A. Duplicate names in column A each count as found if the name appears at least once in column B; extra names in column B do not affect the result.
In Excel, click an empty cell outside the two name ranges.
Type =SUMPRODUCT((A2:A9<>"")*--ISNUMBER(MATCH(A2:A9,B2:B5,0)))=SUMPRODUCT(--(A2:A9<>"")) and press Enter. Replace A2:A9 and B2:B5 with your own ranges.
Read the result cell: TRUE means every nonblank name in the first range was found; FALSE means at least one was missing.

Check for Missing Names in WPS Spreadsheets
Use the same SUMPRODUCT and MATCH formula in WPS Spreadsheets to compare two name lists without VBA or helper columns.
- 1. Open the workbook: Open WPS Spreadsheets and select Open to load your Excel workbook.
- 2. Choose a result cell: Click an empty cell outside the two name ranges.
- 3. Enter the comparison formula: Type =SUMPRODUCT((A2:A9<>"")*--ISNUMBER(MATCH(A2:A9,B2:B5,0)))=SUMPRODUCT(--(A2:A9<>"")) and press Enter. Adjust the ranges to match your lists.
- 4. Check the result: Read the result cell: TRUE means all nonblank names in the first range were found, while FALSE means at least one is missing.

Frequently Asked Questions
How can I return TRUE when a name is missing instead?
Wrap the comparison in NOT: =NOT(SUMPRODUCT((A2:A9<>"")*--ISNUMBER(MATCH(A2:A9,B2:B5,0)))=SUMPRODUCT(--(A2:A9<>""))). TRUE then means at least one nonblank name is missing.
Do duplicates in the first list affect the result?
Each occurrence is checked, but one matching name in the second range is enough for all identical entries. The formula checks presence, not matching counts.
What happens if the first range contains no names?
The formula returns TRUE because there are no nonblank names missing from the second range.
Does the comparison distinguish uppercase and lowercase letters?
No. MATCH with an exact-match setting of 0 is not case-sensitive, so names that differ only in letter case are treated as matches.
Why might names that look the same be reported as missing?
Extra spaces or other hidden characters can make names differ. Check the cells in both ranges and clean the values before running the comparison.




