How to Match Names Across Two Excel Spreadsheets Quickly

Formulas & functions · Updated November 20, 2026

To find names from one Excel spreadsheet in another, put the names to check in column A and use COUNTIF to test whether each appears in the other workbook. With names in Review.xlsx on Sheet1 in A2:A100 and the comparison list in Roster.xlsx on Sheet1 in A2:A500, enter this in Review.xlsx cell B2, then fill it down: =IF(A2="","",IF(COUNTIF('[Roster.xlsx]Sheet1'!$A$2:$A$500,A2)>0,"Match","No match")). Keep both workbooks open while creating the reference; Excel can then build the link to the other file.

What a name match means

A spreadsheet match is a comparison between values in two lists. The usual task is to take each name in one list and determine whether the other list contains the same text. A formula can label each row as a match or no match, after which you can filter, count, or review the results. This is useful for comparing customer lists, employee rosters, event registrations, mailing lists, and similar records.

A match does not automatically prove that two records refer to the same person. Two people can share a name, and a person’s name can appear differently in separate files. For example, “Jordan Lee” might occur twice in a roster, while “Jordan Lee” and “Lee, Jordan” may describe the same person without being an identical text value. Treat the result as a way to find candidates for review, not as proof of identity. If available, compare a stable identifier such as an employee number or customer ID as well.

Set up the workbooks and formula

Save the files with clear names and identify the worksheet and column containing each list. The example formula assumes the list being checked is in Review.xlsx, Sheet1, column A, and the reference list is in Roster.xlsx, Sheet1, column A. The dollar signs keep the reference list fixed when you copy the formula down, while A2 changes to A3, A4, and so on. The blank check at the start prevents empty rows from being labelled as matches.

  1. Open both workbooks in Excel for Microsoft 365 on Windows.
  2. In the workbook whose names you want to check, select the first result cell beside the first name, such as B2.
  3. Enter the formula from the opening paragraph. If your worksheet or range differs, change the reference accordingly.
  4. Press Enter, select B2 again, and drag or copy the fill handle down through the rows to check.
  5. Filter the result column to show only Match or No match, depending on what you need to review.

The example uses a linked reference in the form ‘[Roster.xlsx]Sheet1’!$A$2:$A$500. If the source file is closed, Excel may display a longer reference that includes its folder path. Do not move or rename the source workbook without checking the link afterward. If Excel cannot find the file, it may show an update or reference error; reopen the correct workbook or repair the link.

Choose a formula for the result you need

COUNTIF is a straightforward choice when the desired output is a label indicating whether a name occurs at least once. If you also need to know how many times it occurs, use COUNTIF by itself. With the names to check in Review.xlsx Sheet1 A2:A100 and the comparison list in Roster.xlsx Sheet1 A2:A500, enter this in Review.xlsx B2 and fill down:

=COUNTIF('[Roster.xlsx]Sheet1'!$A$2:$A$500,A2)

A result of 0 means no matching value was found; 1 means it was found once; a larger number indicates duplicates in the reference list. That distinction matters when a list is expected to contain one record per person. COUNTIF is not case-sensitive, so “Maya Chen” and “MAYA CHEN” count as a match. It also treats the asterisk and question mark as wildcard characters in its criteria. If names may contain those characters, or if exact character-by-character matching is important, test the data carefully rather than assuming COUNTIF is a strict identity check.

Microsoft 365 and Excel 2021 also include XMATCH, which can return a position for an exact-mode lookup. With the same layout, this formula in Review.xlsx B2 returns a number if A2 is found and an error if it is not:

=XMATCH(A2,'[Roster.xlsx]Sheet1'!$A$2:$A$500,0)

The final 0 specifies exact-match mode. XMATCH is useful when you need the matching item’s position for a follow-up calculation, but for a simple yes-or-no label, COUNTIF is usually easier to read. If you use a different Excel version, check function availability before building a workbook around XMATCH.

Clean names before comparing them

Many apparent misses come from inconsistent text rather than different people. A trailing space, a nonbreaking space copied from a web page, or an extra space between words can prevent values that look alike on screen from comparing as expected. Other common differences include middle initials, punctuation, accents, suffixes such as Jr., and different name order. Decide which differences are acceptable for your task before cleaning anything: removing punctuation or initials can make two different people look identical.

A helper column can standardize basic spacing and remove many nonprinting characters. With the original names in A2:A100 on each workbook’s Sheet1, enter this in B2 on both sheets and fill down:

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

This replaces the common nonbreaking space (character 160) with an ordinary space, removes many nonprinting characters, trims leading and trailing spaces, reduces repeated ordinary spaces, and converts text to lowercase. Then compare the helper columns instead of the original name columns. For example, if the cleaned names are in column B of both files, use this in Review.xlsx Sheet1 C2 and fill down:

=IF(B2="","",IF(COUNTIF('[Roster.xlsx]Sheet1'!$B$2:$B$500,B2)>0,"Match","No match"))

Cleaning is not the same as fuzzy matching. It will not decide that “Katherine” and “Catherine” are equivalent, or that “Sam Rivera” and “Samuel Rivera” are the same person. More aggressive edits can hide genuine differences, so retain the original columns and review uncertain cases against another identifier.

Review matches with filters and formatting

Once the formula has filled down, a result column makes the comparison easy to inspect. Use the filter on the header row to show only Match, or sort the results to group matches together. If the list has no header row yet, add descriptive headings such as Name and Match status before filtering. Avoid sorting just one column: select the full table or use its filter so each name stays with its result.

You can also make results stand out visually. Select the result cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains and enter Match. Because “No match” contains the word “Match,” use a rule that distinguishes the full label, or choose Home > Conditional Formatting > New Rule and set a formula-based rule for the exact result. A clear alternative is to use labels such as Found and Not found, then create a text rule for Found; inspect the rule so the longer label is not accidentally highlighted too. Formatting is only a display aid: the formula result remains the underlying test.

Compare lists with Power Query

For recurring comparisons or larger lists, Power Query can combine tables without copying a formula into every row. In Excel for Microsoft 365 on Windows, format each list as a table and give the tables useful names. Import the first table with Data > Get Data > From Other Sources > From Table/Range, then bring in the second table in the same way. In the Power Query Editor, choose Home > Merge Queries, select the name column in each query, and choose the join type that fits the question.

An Inner join keeps rows with a key present in both tables. A Left Anti join keeps rows from the first table that have no match in the second; a Right Anti join does the reverse. An Inner join can return multiple rows when either list contains duplicate names, so inspect duplicates before treating the output as a one-to-one comparison. Power Query is useful when the source lists are refreshed regularly, because you can refresh the query after replacing or updating the source data. Menu availability and connection options can differ on Mac and across Excel builds; if the described import commands are unavailable, a formula-based comparison may be simpler.

Power Query also offers fuzzy matching for some text comparisons in the merge settings. Fuzzy matching can identify similar spellings, but it is not a guarantee that two rows describe the same person. Similarity settings affect which candidates are returned, and common names can create false positives. Use fuzzy results as a review queue and verify them with extra fields such as email address or ID.

Common problems and how to diagnose them

  • Every row says No match: Confirm the workbook name, worksheet name, range, and column. Check that the reference list is actually inside the selected rows and that the source file has not moved.
  • Names look identical but do not match: Check for spaces, nonbreaking spaces, punctuation, or different name formats. Compare the cleaned helper values side by side.
  • There are too many matches: The lookup may include duplicate names, or two people may share a name. Use COUNTIF’s count result and verify records with a second field.
  • The formula changes the lookup range as it is copied: Make sure the range uses dollar signs, as in $A$2:$A$500, while the individual lookup cell remains relative, as in A2.
  • You see #REF! or a link warning: Check that the source workbook exists in the expected location and that its worksheet still has the referenced name. Re-selecting the range while both files are open can help rebuild the reference.
  • Only part of the list is checked: Expand the fixed range to cover all source rows, or convert the lists to Excel tables and use table references for ranges that grow over time.

Which matching method should you use?

For a one-time check with clean lists, COUNTIF is usually the quickest option. It is easy to copy down and gives a clear label or count. Use XMATCH when the position of a matching item matters and the Excel version supports it. Use helper columns when the differences are mostly harmless formatting inconsistencies, and preserve the original data so the cleanup can be reviewed.

Power Query is a better fit when the same comparison must be repeated after updates, when you want a joined output, or when you need unmatched rows from either list. Fuzzy matching can help identify likely spelling variants, but it should not replace verification. In every method, consider whether names are unique enough for the decision being made. For high-impact records, matching on a unique identifier is safer than relying on a name alone.

Frequently Asked Questions

Can Excel compare two separate workbooks?
Yes. A formula in one workbook can refer to a range in another workbook. Open both files while selecting the source range to create the reference, then check that the link remains valid if either file is moved or renamed.

Does COUNTIF distinguish uppercase and lowercase names?
No. COUNTIF is case-insensitive, so names that differ only in letter case are treated as matches. If case itself must distinguish records, use a case-sensitive comparison method and test it against your actual data.

How do I find names in one spreadsheet that are missing from the other?
Use the match-status formula for every name in the first list, then filter for No match. To check in the opposite direction, run the same comparison with the two lists reversed.

Can I match names with spelling mistakes?
A normal exact comparison will not reliably recognize misspellings or alternate forms. Power Query’s fuzzy merge can surface similar text in supported Excel builds, but review candidates manually and verify them with another field before combining records.

Why does Excel show a match for a name I cannot see in the list?
Check for duplicate entries, hidden or filtered rows, and wildcard characters in the criteria. Also inspect the precise cell contents in the referenced range; formatting can make values appear alike even when their underlying text differs.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.