How to Compare a List of Names in Excel: 10 Methods

How-to basics · Updated November 20, 2026

For a quick comparison, put one name list in A2:A100 and the other in D2:D100, then enter =IF(COUNTIF($D$2:$D$100,A2)>0,"Found","Missing") beside the first name and fill down. This guide covers ten ways to compare names in Microsoft Excel for Microsoft 365 on Windows, from simple formulas and highlighting to Power Query and fuzzy matching, so you can choose a method that fits your list size and how exact the matches need to be.

1. Use COUNTIF to mark matches and missing names

With names in A2:A100 and D2:D100, enter this formula in B2 and fill it down: =IF(COUNTIF($D$2:$D$100,A2)>0,"Found","Missing"). If A2 contains Maya Chen and that name appears once in the D list, the result is Found; if it does not appear, the result is Missing. COUNTIF is a good starting point because it is available in older Excel versions as well as Microsoft 365, and it gives each row an easy-to-filter status. It treats uppercase and lowercase letters as equivalent and can interpret wildcard characters such as an asterisk in a search value, so check the data if names contain those characters or if letter case must matter.

2. Use XLOOKUP to return the matching name

With the same layout—names in A2:A100 and D2:D100—enter =XLOOKUP(A2,$D$2:$D$100,$D$2:$D$100,"Not found",0) beside the first list and copy it down. If A2 is Maya Chen and the second list contains that exact text, the result is Maya Chen; otherwise it is Not found. XLOOKUP is useful when you want the matched value itself, or when you plan to return a related field by changing the return range, such as a department or customer ID in column E. The final 0 requests an exact match, and XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in older perpetual releases such as Excel 2019.

3. Use MATCH when you need a position or a simple status

With names in A2:A100 and D2:D100, put =IF(ISNUMBER(MATCH(A2,$D$2:$D$100,0)),"Found","Missing") in B2 and fill down. The final 0 tells MATCH to look for an exact value; if Maya Chen is the third item in D2:D100, MATCH finds its position and the formula returns Found. This approach is handy in workbooks that already use MATCH with INDEX, because you can reuse the same lookup logic to retrieve information from another column. Unlike XLOOKUP, MATCH returns a position rather than the name or a related value, so wrap it in ISNUMBER when all you need is a clear found-or-missing label.

4. Highlight matches with Conditional Formatting

With the first list in A2:A100 and the list to check against in D2:D100, select A2:A100 and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =COUNTIF($D$2:$D$100,A2)>0, choose a fill color, and apply the rule; matching names in column A will be highlighted because they occur in column D. This is worth using when a person needs to scan the original list without adding a status column or changing its order. The formula uses a fixed reference for the comparison list but leaves A2 relative, which lets Excel check each selected row; to highlight names missing from the other list instead, use =COUNTIF($D$2:$D$100,A2)=0.

5. Check case-sensitive matches with EXACT

With a name in A2 and the comparison list in D2:D100, use =SUMPRODUCT(--EXACT(A2,$D$2:$D$100))>0 to return TRUE only if a case-sensitive match exists. For example, if A2 contains Alex Kim but the comparison list contains only alex kim, the result is FALSE, unlike COUNTIF or XLOOKUP, which normally treat those spellings as equivalent. EXACT compares the text character by character, and SUMPRODUCT checks the result against the whole comparison range without requiring a special array-entry shortcut. Choose this when capitalization carries meaning in your data, but not merely because names happen to have inconsistent capitalization; case-sensitive rules can create apparent mismatches that are not useful for ordinary contact lists.

6. Normalize spaces and hidden characters before comparing

With the first list in A2:A100 and the second in D2:D100, put =LOWER(TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))) in E2 and copy it down; use the same formula with D2 in place of A2 in F2. If A2 has extra spaces around Maya Chen or contains a nonbreaking space copied from a web page, the normalized result is maya chen, making it easier to compare the cleaned columns with COUNTIF or XLOOKUP. LOWER removes capitalization differences, TRIM removes repeated ordinary spaces and spaces at the ends, CLEAN removes many nonprinting characters, and SUBSTITUTE converts the common nonbreaking space (character 160) to a regular space first. Keep the original columns, because this process does not resolve swapped first and last names, punctuation differences, accents, nicknames, or genuinely different spellings.

7. Sort both lists and compare row by row

With two lists of the same length in A2:A100 and D2:D100, sort each list A to Z, then enter =IF(A2=D2,"Same","Different") in E2 and fill down. If the first sorted values in both columns are Avery Brown, E2 returns Same; the first row where the entries differ identifies where the sorted lists stop agreeing. This is a practical visual audit for small, tidy lists, especially when someone needs to inspect the actual differences rather than produce a separate report. It is not a reliable general-purpose comparison when the lists have different lengths or contain duplicates in different quantities, because one missing name can shift many later rows and make them all look different.

8. Spill a list of names that are missing from the second list

With the source names in A2:A100 and the comparison names in D2:D100, enter =FILTER(UNIQUE(A2:A100),(UNIQUE(A2:A100)<>"")*(COUNTIF(D2:D100,UNIQUE(A2:A100))=0),"None") in an empty cell. If Maya Chen and Jordan Lee are in the first list, only Maya Chen is in the second, and Jordan Lee is absent, the formula spills Jordan Lee as the result while ignoring blanks and repeated entries in the first list. This is useful for a compact exception report that updates when the source data changes, rather than a status result repeated beside every row. FILTER, UNIQUE, and dynamic array spilling require Microsoft 365 or Excel 2021 or later; leave enough empty cells below the formula so the results can spill without a #SPILL! error.

9. Merge the lists in Power Query for a repeatable report

For recurring comparisons, convert each name range to an Excel table with Ctrl+T, give the tables clear names, and load them through Data > From Table/Range. In the Power Query Editor, choose Home > Merge Queries, select the name column in each table, and use a Left Anti join to return rows present in the first table but absent from the second. Run the merge in the opposite direction to find names found only in the second table; a Left Outer join is useful when you want to retain every first-table row and inspect whether a corresponding second-table row exists. After choosing Close & Load, refresh the query when the source tables change, making this a strong choice for monthly or weekly files; exact matching still depends on the text, so trim and clean the columns in Power Query when needed.

10. Try a fuzzy Power Query merge for near-matches

With both name lists loaded as Power Query tables, open Home > Merge Queries, select the name columns, and turn on Use fuzzy matching to perform the merge in the dialog. Fuzzy matching can suggest that Jon Smyth and John Smith are similar even though their text is not identical, which helps surface possible typos or small spelling variations for review. The merge dialog lets you adjust its similarity threshold and configure options such as ignoring case or combining text parts, but a high similarity is not proof that two records belong to the same person. Use it to create a review queue, then confirm matches against reliable identifiers such as an email address or customer number; Power Query features vary by platform, and fuzzy merge support is not available in every Excel for Mac build, so use Windows desktop Excel if the option is absent.

How to Choose

Start with the question the comparison must answer. If you need a yes-or-no result beside every name, COUNTIF is simple and widely compatible; use XLOOKUP when returning a matched value or another column matters, and EXACT when capitalization must be respected. For a quick visual scan, conditional formatting avoids a results column, while a FILTER and UNIQUE formula creates a live list of distinct exceptions in newer Excel versions. Before relying on any method, decide whether duplicate names count as separate records: COUNTIF can confirm that a name appears at least once, but it does not tell you whether the two lists contain the same number of copies.

Data quality and repeatability are equally important. Clean spaces and nonprinting characters when names were pasted from emails, websites, or exported systems, but retain the source values so you can audit what changed. Use Power Query when the same large comparison must be repeated, when you need both one-sided differences, or when the result should be refreshed as new rows arrive. Fuzzy matching is best reserved for suggesting candidates that a person will verify, not for automatically merging records based on names alone. If two people can share a name, use a stable identifier such as an employee ID or email address whenever possible.

Frequently Asked Questions

Does Excel treat uppercase and lowercase names as different?

Most standard lookup and counting methods, including COUNTIF, MATCH, and XLOOKUP, are not case-sensitive for ordinary text matching. Use EXACT with SUMPRODUCT when case must be part of the comparison, and be aware that this can flag capitalization inconsistencies as differences.

How can I compare two lists when names have extra spaces?

Normalize both lists before comparing them. TRIM removes extra regular spaces, while CLEAN and SUBSTITUTE with CHAR(160) can address common hidden characters from copied text; then compare the helper columns rather than the untouched originals.

Can I find names that appear in one list but not the other?

Yes. Use a COUNTIF status formula and filter for Missing, or create a dynamic exception list with FILTER, UNIQUE, and COUNTIF in Microsoft 365 or Excel 2021 and later. For a complete two-way difference, repeat the check with the lists reversed.

What is the difference between duplicate checking and comparing lists?

A duplicate check identifies repeated values within a list, while a list comparison checks whether a value from one range appears in another. A simple COUNTIF match only proves that a name occurs at least once; it does not verify that duplicate counts are equal.

Can Excel tell whether two differently spelled names are the same person?

No formula can establish identity from similar names alone. Power Query fuzzy matching can suggest likely spelling variations, but review those suggestions and confirm them with an identifier such as an email address, employee number, or customer ID.

Final Thoughts

For most straightforward comparisons, begin with COUNTIF and check whether spaces or hidden characters are causing false misses. Move to dynamic arrays for a changing exception list, Power Query for repeatable workflows, or fuzzy matching when you need human-reviewed suggestions. The best method is the one that matches your accuracy needs while preserving enough context to verify each result.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.