To find names in one Excel list that are absent from another, put the first list in A2:A100 and the list to check against in B2:B100, then enter =IF(COUNTIF($B$2:$B$100,A2)=0,"Missing","Found") in C2 and fill it down. Rows marked Missing contain names from column A that Excel cannot find in column B.
Set up the two lists for comparison
This comparison is useful when reconciling an attendee list with registrations, checking an employee roster against an access list, or finding customers who have not appeared in a newer export. Decide which list is the reference and which list you want to check. The formula above asks, for every name in column A, whether the same value appears anywhere in column B. Reversing the lists answers a different question.
Keep a header row, such as “Original list” in A1 and “Current list” in B1, and put the actual names below it. The formula starts at row 2 so that it does not treat the headers as names. The example stops at row 100; change the range to cover your actual data. Use a larger fixed range if the lists may grow, or convert each list into an Excel table with Insert > Table so the data can expand more conveniently.
Before comparing, check that each column contains one name per cell. A full name stored as “Lee, Morgan” will not match “Morgan Lee,” even if both refer to the same person. Excel compares the cell values, not the people’s identities or the meaning of differently formatted names.
Use COUNTIF to label each name
COUNTIF counts cells in a range that meet a condition. In the example, COUNTIF($B$2:$B$100,A2) counts how many cells in the reference list match the name in A2. A count of zero means that name is missing; a count of one or more means it was found. IF converts that count into a readable status.
The dollar signs lock the reference range B2:B100 as you copy the formula down, while A2 remains relative and changes to A3, A4, and so on. Without the dollar signs, the lookup range would shift as the formula was filled down, potentially leaving early names out of the comparison. After entering the formula, drag the fill handle at the lower-right corner of C2 down the column, or copy C2 and paste it into the remaining rows.
To see only the missing entries, turn on filters with Data > Filter, open the filter on column C, and select Missing. This does not delete or rearrange the source names; it simply hides rows that do not meet the selected status. You can also sort the status column to group missing and found names together.
Return a separate list of missing names
If you have Microsoft 365 or Excel 2021, FILTER can produce a compact list without a status column. With names in A2:A100 and the comparison list in B2:B100, enter this formula in an empty cell outside those ranges:
=FILTER(A2:A100,(A2:A100<>"")*(COUNTIF(B2:B100,A2:A100)=0),"No missing names")
Excel spills the results into the cells below. The first condition leaves blank cells out, and the second selects names with no match in column B. If there are no missing names, the formula displays “No missing names.” Make sure the cells where results would spill are empty; otherwise Excel can show a #SPILL! error.
If the source list contains the same missing name more than once and you want each name only once, wrap the result in UNIQUE. With the same ranges, use =UNIQUE(FILTER(A2:A100,(A2:A100<>"")*(COUNTIF(B2:B100,A2:A100)=0),"No missing names")). FILTER and UNIQUE are available in Microsoft 365 and Excel 2021 or later. In older Excel versions, use the COUNTIF status column and filter it, or use Excel’s built-in Remove Duplicates command on a copy of the extracted results.
Highlight missing names without adding a status column
Conditional formatting can make missing names visible directly in the original list. Select A2:A100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With names in A2:A100 and B2:B100, enter =COUNTIF($B$2:$B$100,A2)=0 as the rule formula, choose a format such as a fill color, and confirm the rule.
The A2 reference is relative so Excel checks each selected row in turn; the dollar signs keep the lookup list fixed. This rule highlights blanks too if the selected range includes empty cells, so either select only populated rows or add a nonblank test: =AND(A2<>"",COUNTIF($B$2:$B$100,A2)=0). Conditional formatting changes how cells look, not their values, so it is useful for review but less convenient than a status column when you need to filter or export the results.
Clean names before comparing
A name can look identical on screen yet fail an exact comparison because one cell contains a leading space, a trailing space, or an invisible character. Imported data may also contain nonbreaking spaces, which are different from ordinary spaces. If the source files come from different systems, make cleaned helper columns before deciding that a person is truly missing.
With the original name in A2, enter =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) in C2 and fill down. For names originally in B2:B100, use the same formula in D2 based on B2, then compare the cleaned columns C and D instead. TRIM removes extra ordinary spaces around words, CLEAN removes many nonprinting characters, and SUBSTITUTE replaces the nonbreaking space represented by CHAR(160) with an ordinary space. This cleanup can change spacing, so preserve the original columns and review the results when exact spelling matters.
Cleaning cannot determine whether “Sam Lee” and “Samuel Lee” are the same person, or whether a changed surname belongs to someone in the other list. Those are identity decisions, not simple text matches. For that kind of reconciliation, add a stable identifier such as an employee number or customer ID if both lists contain one, and compare that field instead of names.
Choose the right comparison method
For most ordinary lists, COUNTIF is the clearest choice: it works in older Excel versions, labels every row, and is easy to audit. FILTER is better when you want a live, compact missing-only output that updates as the source data changes. Conditional formatting is convenient for a quick visual scan, while a helper column is usually better when others need to filter, sort, or review the result.
Excel’s standard text comparisons through COUNTIF are not case-sensitive, so “Jordan Kim” and “JORDAN KIM” count as a match. If capitalization must be treated as significant, with the name in A2 and the reference names in B2:B100, use =IF(SUMPRODUCT(--EXACT(A2,$B$2:$B$100))=0,"Missing","Found"). EXACT checks case-sensitive equality, and SUMPRODUCT counts the matches. Case-sensitive matching is uncommon for personal names, but may matter when comparing codes or identifiers.
COUNTIF also treats the asterisk (*) and question mark (?) as wildcard characters in criteria. That matters if a cell literally contains either symbol. For normal names, this is rarely an issue; for unusual text values where literal character matching is important, use an exact-match approach such as the SUMPRODUCT and EXACT formula above.
Common errors and how to fix them
- Every result says Missing: Check that the formula points to the right comparison column and that the names are stored as text in both lists. Check for extra spaces or different punctuation, and confirm the lookup range includes the full list.
- Some obvious matches are marked Missing: Compare the underlying text, not just how it appears. Look for middle initials, different name order, hyphens, apostrophes, nonbreaking spaces, or a trailing space. Helper columns can reveal or remove some spacing differences.
- Names that should not match are marked Found: COUNTIF is case-insensitive and interprets wildcard characters specially. Use EXACT for case-sensitive checking, or compare a reliable ID rather than a name.
- The formula changes when copied: Confirm that the reference list is locked with dollar signs, as in $B$2:$B$100. The name being checked should remain relative, as in A2.
- The FILTER formula returns an error: Check that the output area is clear, the ranges are the same height, and the Excel version supports FILTER. A #SPILL! message usually means another value blocks the output range.
Duplicates are another source of confusion. COUNTIF reports a name as Found even if it appears several times in the second list; it answers whether at least one match exists, not whether the lists have the same number of occurrences. If duplicates matter, compare counts for each name or remove duplicates from a copy before reconciling. A missing-name check alone does not verify that two lists have identical contents.
Check both directions and keep the result current
A one-way comparison finds names in the first list that are absent from the second. It does not find names that appear only in the second list. To check the reverse, use the same method with the lists switched: for example, put a COUNTIF formula beside the names in B and count matches in A. Reviewing both directions helps identify additions as well as omissions.
When new rows are added, update fixed ranges such as B2:B100 or use Excel tables so formulas can refer to expanding columns. Recheck the comparison after importing refreshed files, and keep an unchanged copy of the original data if the reconciliation needs to be reviewed later. A formula result is only as reliable as the range and source data it checks.
Frequently Asked Questions
How do I find names in one Excel column that are not in another? Put the lists in columns A and B, enter =IF(COUNTIF($B$2:$B$100,A2)=0,"Missing","Found") beside the first name in A, and fill down. Filter the result for Missing.
Can Excel show only the missing names automatically? Yes. In Microsoft 365 or Excel 2021 and later, use FILTER with a COUNTIF test to return a spilling list. The output recalculates when the source values change, provided the formula ranges cover the updated data.
Does COUNTIF distinguish uppercase and lowercase names? No. COUNTIF is case-insensitive for text. Use a formula built with EXACT and SUMPRODUCT if capitalization must count as a difference.
Why does Excel say a name is missing when it looks the same? The cells may contain extra spaces, invisible characters, different punctuation, or a different spelling. Use helper columns to clean ordinary spacing, then inspect the original values if the mismatch remains.
How can I find names that are in the second list but not the first? Reverse the comparison: check each name in column B against the range in column A. The original formula only checks names in A against names in B.
Related
- Excel Data Validation List: Create a Yes/No Dropdown
- Excel Data Validation Simple List: Create and Manage It
- Excel Data Validation List: Can It Show Two Columns?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Why Is AVERAGE Not Working in Excel? Common FixesIf AVERAGE is not working, Excel may not recognize the selected cells as numbers, the formula may point…
- Excel Invalid Name Error: Causes and FixesIn Excel, an invalid name error usually means a formula contains a name Excel cannot recognize, such as…
- Excel Won’t Let You Delete a Row? Try These FixesTo delete a worksheet row in Excel for Microsoft 365 on Windows, select its numbered heading, then choose…
- Excel SUMIF Spill Error: Causes and How to Fix It QuicklyTo fix an Excel SUMIF #SPILL! error, either clear the cells blocking the formula’s output or change the…
- Disk Is Full Excel Save Error: How to Fix ItIf Excel says the disk is full when you try to save, first preserve your open workbook: use…
- Circular Reference Error in Excel: How to Find It FastTo find a circular reference in Excel for Microsoft 365 on Windows, select Formulas > Error Checking >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.