How to Find Names in One Excel Sheet but Not Another
Question details
The user needs to compare names across two Excel sheets and extract only the names listed on Sheet 2 that do not appear in specific grouped ranges on Sheet 1.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing lists of names across different sheets to automatically identify missing or unique entries.
- Observed behavior
- The user wants a formula to automatically extract and return names present in one sheet but absent in another, bypassing manual cross-referencing.
Ensure both sheets are within the same workbook and identify the exact cell ranges containing the names you want to compare on each sheet.
Use the FILTER and COUNTIF Functions (Microsoft 365 & Excel 2021)
The most efficient way to extract unique names not found on another sheet using modern dynamic arrays.
If you are using a newer version of Excel that supports dynamic array formulas, combining the FILTER function with COUNTIF is the cleanest approach. It automatically spills the results into adjacent cells without the need to drag the formula.
Click on the cell in Sheet 2 where you want the list of unique names to begin displaying.
Type =FILTER(A1:A330,COUNTIF('Sheet 1'!B2:S50,A1:A330)=0) into the formula bar. Replace the ranges with your actual data ranges.
Press Enter. The formula will automatically spill the names that are on Sheet 2 but missing from the specified ranges on Sheet 1.
Use an Array Formula (Older Excel Versions)
A reliable method for legacy Excel versions that do not support dynamic array functions.
Easily Compare Excel Sheets with WPS Office
WPS Spreadsheet fully supports advanced dynamic array formulas like FILTER, making it incredibly simple to compare lists across multiple sheets without relying on complex workarounds.
- 1. Open your workbook in WPS: Launch WPS Spreadsheet and open your Excel file containing the two sheets you want to compare.
- 2. Enter the FILTER formula: Select an empty cell in your destination sheet and type the formula =FILTER(A1:A330,COUNTIF('Sheet 1'!B2:S50,A1:A330)=0).
- 3. Press Enter to display results: Press the Enter key. WPS Spreadsheet will instantly calculate and display all the names unique to the second sheet.
Frequently Asked Questions
Why does my FILTER formula return a #CALC! error?
The #CALC! error occurs when the FILTER function finds no names that meet your criteria (meaning all names on Sheet 2 are already on Sheet 1). You can handle this gracefully by adding a third argument: =FILTER(A1:A330,COUNTIF('Sheet 1'!B2:S50,A1:A330)=0, "No missing names").
Why is the array formula not working in my older Excel version?
In legacy Excel versions, array formulas must be confirmed with Ctrl+Shift+Enter instead of just the Enter key. If done correctly, curly braces {} will appear around the formula in the formula bar. Also, ensure your absolute references (the dollar signs $) match your exact data range.
Will these formulas work if the names have different capitalization?
Yes, the COUNTIF function is case-insensitive by default. It will treat 'John Doe' and 'john doe' as identical when comparing your two lists.
