Compare Names in Two Excel Sheets: Find Matches

To compare names on two Excel sheets, put the names in columns and use a formula such as COUNTIF to mark each name as a match or not found. For a list on Sheet1 in A2:A100 and a comparison list on Sheet 2 in A2:A100, enter the formula below in Sheet1!B2, then fill it down.

=IF(A2="","",IF(COUNTIF('Sheet 2'!$A$2:$A$100,A2)>0,"Match","Not found"))

It leaves blank rows blank and checks whether each name from the first list appears anywhere in the second. This is usually more useful than comparing rows side by side, because the same person may appear in a different position on each sheet.

Set up the two lists

Before comparing, identify the column that contains the names on each worksheet. The examples here assume the first list is in Sheet1 column A and the second is in Sheet 2 column A, with headers in row 1. If your names are in other columns or the sheets have different names, adjust the cell references in the formula.

In Excel for Microsoft 365 on Windows, click the cell where you want the result, type the formula, and press Enter. To apply it to the remaining names, select the formula cell and double-click its fill handle, the small square at the lower-right corner. Excel fills down alongside nearby data. You can also drag the handle to a specific row.

Keep a header such as Name in row 1, and start the data range at row 2.
Use a fixed comparison range, such as $A$2:$A$100, so it does not shift as you copy the formula down.
If a sheet name contains a space, put it in single quotation marks in the reference, as in ‘Sheet 2’!A:A.

On Excel for Mac, the formula and fill-handle method work the same way; the ribbon and keyboard shortcuts may differ. Excel for the web also supports the formula, though its interface and available options can vary slightly.

How the match formula works

COUNTIF checks the comparison range for a value equal to the name in A2. If it finds one or more occurrences, it returns a count above zero. IF turns that count into a readable status. The first test, A2=””, prevents an empty row from being reported as a match just because the other range also contains blanks.

To find names on the second sheet that are absent from the first, put the reciprocal check beside the names on Sheet 2. With the Sheet 2 name in A2 and Sheet1 names in A2:A100, enter this in Sheet 2!B2 and fill down:

=IF(A2="","",IF(COUNTIF(Sheet1!$A$2:$A$100,A2)>0,"Match","Not found"))

Checking both directions matters when the lists are not expected to contain the same people. A one-way check tells you which first-list names are missing from the second, but does not reveal extra names that appear only in the second list.

COUNTIF is not case-sensitive, so “MAYA CHEN” and “Maya Chen” count as a match. It also recognizes the wildcard characters * and ? in criteria. That usually does not affect ordinary names, but a name that literally contains one of those characters could produce an unexpected result. For literal exact matching, XMATCH is an alternative in Excel 2021 or Microsoft 365.

Clean spacing and inconsistent text

Names that look identical can fail to match because one cell has a leading or trailing space, repeated spaces, or a nonbreaking space copied from a website. A visible difference in punctuation, a middle initial, or a suffix can also prevent an exact match. Cleaning the data before comparing makes the result more dependable.

With the original name in A2, enter the following helper formula in B2 on each sheet, then fill it down. It replaces a common nonbreaking space with a regular space, removes nonprinting characters, and trims extra spaces at the beginning and end. TRIM also reduces repeated internal spaces to single spaces.

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

Then compare the cleaned helper columns rather than the originals. If the cleaned names are in column B on both sheets, place this formula in C2 on Sheet1 and fill down:

=IF(B2="","",IF(COUNTIF('Sheet 2'!$B$2:$B$100,B2)>0,"Match","Not found"))

This cleanup is helpful, but it does not decide whether two different spellings refer to the same person. “Jon Smith” and “John Smith,” for example, remain different text. Nor does this formula remove every unusual Unicode character or standardize punctuation and name order. Review questionable records rather than assuming a text-cleaning formula can resolve identity.

Account for duplicates and counts

A match result answers whether a name occurs at least once; it does not compare how many times it occurs. If “Alex Kim” appears three times in one list and once in the other, the basic formula marks all Alex Kim entries as matches. That may be enough for checking membership, but it cannot confirm that two lists contain the same number of each name.

To compare counts, count how often each name occurs in each list and then compare those totals. With the name in Sheet1!A2, Sheet1 names in A2:A100, and Sheet 2 names in A2:A100, enter this in Sheet1!B2:

=COUNTIF($A$2:$A$100,A2)-COUNTIF('Sheet 2'!$A$2:$A$100,A2)

A result of 0 means the name occurs equally often in both lists; a positive result means it appears more often on Sheet1, and a negative result means it appears more often on Sheet 2. For example, if Alex Kim occurs three times on Sheet1 and once on Sheet 2, the result is 2. Duplicate rows can be legitimate, so decide whether you are checking unique people or record counts before treating duplicates as errors.

Make missing names easy to see

A status column is often clearer than visually scanning two long lists. After filling the match formula, filter the status column to show only Not found. Select the header row and use Data > Filter, then use the dropdown in the status column. This isolates names that need review without changing the underlying lists.

You can also color the results. Select the status cells, such as B2:B100, then choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Enter Not found and choose a format. This highlights the formula results rather than attempting to apply a direct cross-sheet rule to the original name column.

For a short list, sorting each list alphabetically and comparing them manually can help reveal spelling variations. For larger lists, keep the original order and use a formula or filter: sorting changes the presentation, but does not itself establish whether a name is missing.

Choose the right comparison method

The best method depends on what “compare” means for your task. Use a membership check when you need to know whether each name from one sheet appears on the other, regardless of order. Use checks in both directions when you need to find additions and omissions in either list. Compare occurrence counts when duplicates matter.

Quick review: Use COUNTIF beside each list and filter for Not found.
Messy imported data: Create cleaned helper columns before checking.
Duplicate-sensitive audit: Compare counts, not only whether a name exists.
Small lists: Alphabetical sorting and manual review may be sufficient, especially for spelling differences.

For a one-time comparison, formulas are usually transparent and easy to inspect. If this is a repeated process, consider turning each range into an Excel table using Insert > Table, so new rows are easier to include in your workflow. You can also use Power Query to import and merge lists for a recurring data-preparation task, but that adds setup and is unnecessary for a simple check.

Common problems and checks

If every entry says Not found, first verify the sheet name and range. A misspelled tab name, a range that stops before the last row, or names stored in a different column can invalidate the comparison. If the sheet name includes an apostrophe, Excel references it by doubling that apostrophe inside the quoted sheet name.

If only some expected matches fail, inspect the source cells for spaces, nonbreaking spaces, punctuation, initials, and inconsistent name order. Compare a failing cell with the formula bar, not only the displayed text. Also check whether one list stores names as a single full name while the other separates first and last names into different columns; those layouts need to be combined or compared by components.

A formula showing as text instead of calculating may be in a cell formatted as Text. Change the format to General, then re-enter the formula. If formulas do not update after edits, check Formulas > Calculation Options and select Automatic. When working with very large lists, avoid unnecessarily huge ranges such as entire columns if calculation becomes slow; use realistic ranges or tables.

Finally, a text match is not proof that two records belong to the same person. Two people can share a name, and one person can appear under different names. For important records, use an appropriate unique identifier, such as an internal customer or employee ID, where permitted and available, and protect sensitive data according to your organization’s policies.

Frequently Asked Questions

Can Excel compare names when they are in a different order?
Yes. COUNTIF searches the entire comparison range, so it does not require the names to be on the same row. It checks whether the value appears anywhere in the selected range.

How do I find names that are on one sheet but not the other?
Place a match formula beside the first list, fill it down, and filter for Not found. Repeat the check in the opposite direction to find names that appear only on the other sheet.

Does the formula treat uppercase and lowercase as different?
No. COUNTIF is case-insensitive, so names with the same letters but different capitalization are treated as matches. It does not treat different spellings as equivalent.

Why does a name look the same but not match?
Extra spaces, nonbreaking spaces, punctuation, or different text such as a middle initial may be responsible. Try the cleanup helper formula and inspect the original values if the mismatch remains.

Can I compare duplicate names as well as check for a match?
Yes. Compare the COUNTIF totals for each name in the two lists. A zero difference means the occurrence counts are equal; the basic Match/Not found formula only confirms that at least one occurrence exists.

Will the formula include names I add later?
Only if the comparison range includes the new rows. Extend the range, or convert the lists to Excel tables and update the formula workflow so added records are included.

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.