To compare two name lists in Excel, check whether each name in the first list appears anywhere in the second. With names in A2:A100 and B2:B100, enter this in C2, then fill down: =IF(A2="","",IF(COUNTIF($B$2:$B$100,A2)>0,"Match","Not found")). The result shows which names from column A are also in column B, regardless of their position.
This approach is useful for checking attendee lists, customer records, staff rosters, or any two sets of names. It finds whether a name is present; it does not confirm that the lists have identical order or identical numbers of repeated names. The steps below use Microsoft Excel for Microsoft 365 on Windows.
What You’ll Need
- An Excel workbook with the two lists in separate columns. The examples use column A and column B, with headers in row 1 and names starting in row 2.
- A spare column for results, such as column C. If your lists are longer or start on different rows, adjust the example ranges to fit.
- Optionally, a copy of the workbook if you plan to clean or rearrange the source data. A formula comparison does not change the names in the original lists.
Step-by-Step
-
Arrange the lists in columns. Put the first list in column A and the second in column B. Use one name per row, and put a short heading such as List 1 and List 2 in row 1. The lists do not need to have the same length, and names do not need to be sorted. Avoid putting extra notes or blank rows inside a list, because they make it harder to check the intended range.
-
Choose what you want to compare. The main formula checks each name in List 1 against all of List 2, so it answers “Which names in A also appear in B?” It does not compare A2 only with B2. This matters when the same person appears on different rows in the two lists. To check both lists, you will repeat the process in the other direction.
-
Add a match formula beside the first list. With names in A2:A100 and B2:B100, put the formula below in C2. The empty-cell check leaves the result blank if A2 is blank; COUNTIF searches the full second-list range and returns a positive count when it finds the name.
=IF(A2="","",IF(COUNTIF($B$2:$B$100,A2)>0,"Match","Not found"))For example, if A2 contains “Jordan Lee” and that name occurs in B2:B100, C2 displays Match. If it is not in that range, C2 displays Not found. The dollar signs keep the B range fixed as you copy the formula, while A2 changes to A3, A4, and so on.
-
Fill the formula down the list. Select C2 and point to the small square at the lower-right corner of the cell, called the fill handle. Drag it down to the last row of List 1. You can also copy C2, select the remaining result cells, and paste. Check the last formula to make sure its reference points to the corresponding name in column A and still searches the complete range in column B.
-
Read or filter the results. Match identifies names found in both lists; Not found identifies names in List 1 that do not occur in List 2. To see only missing names, select the data and choose Data > Filter, then use the filter arrow in column C to show Not found. Filtering hides other rows; it does not delete them. Clear the filter when you want to see the full list again.
-
Check the second list in the opposite direction. A name might exist in List 2 but not List 1, which the first formula cannot reveal. With names in A2:A100 and B2:B100, place this formula in D2 and fill down beside List 2:
=IF(B2="","",IF(COUNTIF($A$2:$A$100,B2)>0,"Match","Not found"))Column D now shows whether each name in B appears in A. Review Not found in both result columns to see the names unique to each list. If the ranges have different last rows, set each formula’s search range to the actual size of the other list.
-
Highlight missing names instead of adding a result column (optional). To highlight names in List 1 that are absent from List 2, select A2:A100. Choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With names in A2:A100 and B2:B100 and A2:A100 selected, enter
=COUNTIF($B$2:$B$100,A2)=0, choose a format, and select OK. The A2 reference adjusts for each selected row, while the B range stays fixed. Repeat for B2:B100 using=COUNTIF($A$2:$A$100,B2)=0to highlight names found only in List 2. -
Save or preserve the comparison. Keep the result columns if you may need to review or share the findings. If you only need a static report, copy the results and use Paste Special > Values in a separate area or workbook. That replaces formulas with their current text results, so remember to retain a formula copy if the lists may change and need checking again.
Common Mistakes
Comparing row by row when the lists are unordered. A formula such as A2=B2 only checks the two cells on the same row. It will miss a name that appears elsewhere in the second list. Use a lookup or count across the entire other-list range instead.
Checking only one direction. A one-way formula finds names in A that are absent from B, but it cannot identify names present only in B. Run the reverse check as well when you need a complete difference report.
Including headers or the wrong range. If your range starts at row 1, Excel may try to match the heading as if it were a name. Conversely, a range ending too early can mark valid names as missing. Confirm the first and last data rows before copying formulas.
Assuming a match means the lists are identical. The formula reports presence, not list order or duplicate counts. If “Sam Patel” appears three times in one list and once in the other, each occurrence can still show Match. For duplicate-sensitive reconciliation, compare the number of occurrences for each name in both lists, not just whether the count is above zero.
Overlooking spelling and formatting differences. “Ana Smith” and “Ana Smith” may not compare as expected because one has an extra space. A middle initial, suffix, punctuation mark, or different spelling also makes a name different. Standard Excel text comparisons are not case-sensitive, so “LEE” and “Lee” are normally treated as the same name.
Tips
Keep the original lists unchanged and put any cleaned versions in helper columns. That way you can inspect the source if a result looks surprising. Use clear headings such as “List 1 result” and “List 2 result,” and format the result column as text or General so the words Match and Not found display normally.
For inconsistent leading, trailing, or repeated ordinary spaces, make a cleaned helper column. With original names in A2:A100, enter =TRIM(A2) in C2 and fill down; with original names in B2:B100, enter =TRIM(B2) in D2 and fill down. Then compare the cleaned columns using the same method. TRIM removes extra regular spaces but does not remove every unusual character, such as a nonbreaking space copied from a website.
COUNTIF treats the characters * and ? as wildcards in its criteria. If a name literally contains one of those characters, the result could be broader than intended. This is unusual for ordinary names, but can matter when a list contains codes or unusual entries. For standard names, COUNTIF is generally a straightforward option. If your lists are large, use a bounded range that covers all entries rather than entire columns, which can make calculation more efficient.
If the list is a recurring export, consider creating an Excel Table: select the data and choose Insert > Table. Tables expand when new rows are added, helping formulas and filters include updated data. Verify the formula range after adding records, especially if the lists are not in tables.
Troubleshooting
Every result says Not found. First verify that the formula searches the correct column and that the selected range includes the names. Click a result cell and inspect the formula bar: a common error is searching column A for a name in A rather than searching column B. Also check whether names contain leading or trailing spaces, hidden characters, or different punctuation.
Some obvious matches are missing. Compare the two cells directly in the formula bar and look for middle initials, extra spaces, suffixes, or spelling variations. Try the TRIM helper-column method for ordinary spaces. If the names were pasted from a webpage, a nonbreaking space may remain; TRIM alone does not remove it. You may need to find and replace the unusual character or normalize the source before comparing.
Blank rows show Match or Not found unexpectedly. Use the blank-cell guard in the example formula, and make sure the formula refers to the cell on its own row. If the source cell contains a formula that returns an empty string, the guard usually leaves the result blank too. Remove accidental spaces from cells that look empty but are not actually empty.
Conditional formatting highlights the wrong cells. Confirm the correct list range was selected before creating the rule. In the rule formula, the first-cell reference should match the top-left cell of the selection—for example, A2 when A2:A100 is selected. Keep dollar signs around the comparison range so it does not shift as Excel evaluates each row. Open Home > Conditional Formatting > Manage Rules to inspect or edit an existing rule.
The comparison stops updating after adding names. The example uses fixed endpoints, so a new entry below row 100 is not included until you extend the formula ranges. Edit the ranges in the formulas or use Excel Tables to make growing lists easier to manage. If formulas appear as text instead of calculating, change the cell format to General and re-enter the formula.
Frequently Asked Questions
Can I compare two lists when the names are in a different order?
Yes. The COUNTIF formula searches the entire comparison range, not just the cell on the same row. Sorting is optional and does not affect the result.
Does this method distinguish uppercase and lowercase letters?
No. COUNTIF comparisons are not case-sensitive, so names with the same letters but different capitalization are treated as a match. For most name lists, this is useful because capitalization often varies between sources.
How do I find names that appear in one list but not the other?
Run the comparison in both directions. Check A against B to find names only in A, then check B against A to find names only in B. Filter each result column to Not found.
Will the formula catch duplicate names?
It checks whether a name appears at least once, so it does not flag a different number of occurrences. If duplicate counts matter, count each name in both lists and compare those counts separately.
Can I compare first and last names stored in separate columns?
Yes, but first create a consistent full-name value in a helper column in each list, then compare those helper columns. Use the same order and spacing for both—for example, first name, one space, and last name—so equivalent people produce equivalent text.
Why does Excel say a name is missing when it looks identical?
The cells may contain extra spaces, a hidden character, punctuation, or a spelling difference. Compare the underlying cell contents, clean ordinary spaces with TRIM, and check that the search range includes the suspected match.
Related
- Change Table Color in Excel: Styles and Custom Formatting
- Excel Subscript: How to Change Text to Subscript
- How to Change Strikethrough Color in Excel Cells Easily
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Make a Fillable Form in Word: A Step-by-Step GuideTo make a fillable form in Microsoft Word, turn on the Developer tab, add content controls such as…
- How to Add Double Spacing in WordTo double-space text in Microsoft Word, select the text—or press Ctrl+A on Windows or Command+A on Mac to…
- Excel Lock Slicer in Place? Position and Protect ItYes. In Excel for Microsoft 365 on Windows, select the slicer, open Size and Properties, and under Properties…
- How to Make Columns in Word 365To make columns in Word for Microsoft 365 on Windows, select the text you want to format, then…
- How to Divide Every Cell in Excel by a Number: StepsTo divide a range in place, enter the divisor in an empty cell, copy it, select the cells…
- Excel Data Validation Guidelines for Reliable SpreadsheetsIn Excel for Microsoft 365 on Windows, select the cells to control, then choose Data > Data Validation…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.