Excel Compare Two Sheets for Matching Names: Formulas

To compare names in two worksheets, enter a formula beside the names on the first sheet and check whether each one appears on the second. If the names are in Sheet1!A2:A100 and Sheet 2!A2:A100, put this in Sheet1!B2 and fill it down: =IF(COUNTIF('Sheet 2'!$A$2:$A$100,A2)>0,"Match","Not found"). It returns “Match” when the name in A2 appears anywhere in the second list, and “Not found” otherwise.

Set up the two name lists

Before comparing, decide which list is the one you want to check. For example, Sheet1 might contain this month’s customer list, while Sheet 2 contains last month’s. Putting the result beside Sheet1’s names makes it easy to see which current customers were already on the previous list. You can reverse the direction by putting the formula beside the names on Sheet 2 instead.

Keep each list in one column, with a heading in the first row and the first name in row 2. The example formula uses rows 2 through 100; adjust both range endpoints to cover your actual data. The dollar signs in the range reference keep the lookup area fixed when you copy the formula down, while A2 changes to A3, A4, and so on.

Use a clear worksheet name, such as Current and Previous, rather than relying on which tab happens to be first.
Check that the cells contain names, not a mixture of names and notes or blank rows that interrupt the list.
If the lists may grow, consider converting each range to an Excel table with Insert > Table. Tables expand when you add rows, although formulas written with ordinary cell ranges must still be adjusted if their ranges do not include the new rows.

In Microsoft 365 for Windows, enter the formula in the formula bar or directly in the cell. Select B2 and press Ctrl+D after selecting the range B2:B100 to fill the formula down. On a Mac, the equivalent fill-down shortcut is Command+D. You can also drag the fill handle at the lower-right corner of the cell.

Use COUNTIF for a straightforward match check

COUNTIF counts cells in a range that satisfy a condition. In the opening example, the condition is the name in A2, and the range is the name column on the other sheet. The IF function turns the count into a label that is easier to scan. A name occurring once or several times on Sheet 2 still produces “Match,” because the question is whether it appears at least once.

To see the actual number of occurrences instead of a label, use a count-only version. With Sheet1 names in A2:A100, place this in Sheet1!B2 and copy down: =COUNTIF('Sheet 2'!$A$2:$A$100,A2). A result of 0 means Excel found no match; 1 means it found one occurrence; and a larger number signals duplicates on the second sheet. COUNTIF is available in older Excel versions as well as Microsoft 365.

COUNTIF comparisons are not case-sensitive: “Jordan Lee” and “jordan lee” count as the same text for this purpose. It compares the cell contents, not a person’s identity. Two different people with identical names will match, and a person whose name changed will not match unless you account for that change separately.

Choose between a match label and a lookup

COUNTIF is a good choice when the only question is whether a name appears in the other list. If you want a position number that you can use in another calculation, XMATCH is another option. With the same sheet and range layout, enter this in Sheet1!B2 and fill down: =IF(ISNUMBER(XMATCH(A2,'Sheet 2'!$A$2:$A$100)),"Match","Not found").

XMATCH returns a position when it finds an item and an error when it does not; ISNUMBER distinguishes the position from the error, and IF displays a readable status. XMATCH is available in Microsoft 365 and Excel 2021 or later. In older Excel versions, COUNTIF is the simpler broadly compatible choice. XLOOKUP can also retrieve related information, such as an ID or department, but use it only when the second sheet has a value you actually need to return. XLOOKUP and XMATCH require Microsoft 365 or Excel 2021 or later.

A name-only match can be misleading if names are not unique. If both sheets have a customer ID, employee number, or other stable identifier, compare that field instead. Alternatively, compare a combination of fields such as name and date of birth only when you have a legitimate reason and permission to use that information. A unique identifier is usually more dependable than trying to infer identity from spelling alone.

Clean names before comparing

Names that look identical on screen may contain extra spaces or hidden characters. A trailing space in “Mina Patel ”, for instance, can stop a direct text comparison from behaving as expected. Data copied from websites or other systems may also contain nonbreaking spaces or nonprinting characters. Cleaning both lists consistently can reduce these false “Not found” results.

With a name in A2, enter this in a helper column, such as C2, and copy it down: =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))). SUBSTITUTE changes the common nonbreaking space (character 160) to an ordinary space; CLEAN removes certain nonprinting characters; TRIM removes leading and trailing ordinary spaces and reduces repeated internal spaces to one. Apply the same cleanup to the names on both sheets, then point the comparison formula at the cleaned columns instead of the original ones.

This cleanup does not solve every text problem. Accented letters, punctuation, initials, different name orders, and abbreviations remain meaningful differences to Excel. “Anne-Marie Chen” and “Anne Marie Chen” are not automatically equivalent. Do not remove punctuation or alter spelling without considering whether that could combine genuinely different people. Keep the original columns so you can review any transformed values.

Find duplicates as well as matches

A match check tells you whether a name exists on the other sheet, but it does not tell you whether either list contains duplicate entries. To count how often each name appears within Sheet1, with names in A2:A100, put this in B2 and fill down: =COUNTIF($A$2:$A$100,A2). A value greater than 1 means that name appears more than once in the checked range. Repeat the check on Sheet 2 if you need to identify duplicates there too.

Duplicates matter when you are reconciling records. If one sheet has “Riley Morgan” once and the other has it three times, the basic match formula still says “Match.” That is correct for an existence test, but it does not confirm that the lists have the same number of records. Compare occurrence counts or use a unique identifier if record-by-record reconciliation is required. Also decide how blank cells should be treated; do not interpret empty rows as people or records.

Make matching names easier to see

A status column is often clearer than coloring names directly because it gives each row an explicit result. You can also apply conditional formatting to the name cells based on that status. First, with the match label in B2:B100 and the corresponding names in A2:A100 on Sheet1, select A2:A100. Choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =$B2="Match", choose a format, and confirm the rule. The row number is relative so each name uses its own status, while the dollar sign fixes the status column.

Using a helper result avoids a common snag: conditional-formatting formulas generally cannot directly refer to a range on another worksheet in the same way as an ordinary cell formula. Calculate the cross-sheet comparison in a cell first, then format based on that cell. To color missing names instead, create a separate rule using the status text “Not found,” or use the built-in filters on the status column to display only missing records.

Common reasons a result looks wrong

If a name you can see is marked “Not found,” inspect the underlying text before changing the formula. Click each cell and look for leading or trailing spaces, inconsistent punctuation, or a different spelling. Compare the formula’s lookup range with the actual data: a fixed range ending at row 100 will not include a name entered in row 101. Also confirm that the sheet tab name in the formula is exactly right. A sheet name containing spaces needs single quotation marks around it, as in 'Sheet 2'!$A$2:$A$100.

If Excel displays the formula as text instead of calculating it, check whether the cell is formatted as Text. Change the format to General, then re-enter the formula. If a formula shows an error after you rename a sheet, Excel normally updates references, but manually typed or copied text can still contain a stale sheet name. Finally, be aware that COUNTIF treats the asterisk and question mark as wildcard characters in criteria. Ordinary names rarely contain them, but if your data does, a wildcard can affect the count and you may need a different exact-comparison approach.

When an exact, case-sensitive check is needed

Most name comparisons should ignore capitalization, which is why COUNTIF works well for typical lists. If capitalization itself must distinguish values, use EXACT with SUMPRODUCT. With the name to check in Sheet1!A2 and the comparison range in Sheet 2!A2:A100, enter this in Sheet1!B2 and fill down: =IF(SUMPRODUCT(--EXACT(A2,'Sheet 2'!$A$2:$A$100))>0,"Match","Not found"). EXACT distinguishes uppercase and lowercase letters, and SUMPRODUCT totals the TRUE results. This is useful for case-sensitive codes, though it is rarely appropriate for ordinary personal names.

For large workbooks, keep the comparison ranges close to the actual data size rather than using entire columns unnecessarily, especially if the workbook contains many formulas. If the same reconciliation is repeated every month, Excel’s Power Query tools can offer a repeatable import-and-merge workflow. For a one-time check between two modest lists, a helper column and COUNTIF are usually easier to inspect and maintain.

Frequently Asked Questions

Can Excel highlight names that appear on both sheets?

Yes. Put a COUNTIF match result in a helper column, then use conditional formatting on the names based on that result. This is more reliable than trying to have a conditional-formatting rule directly inspect a range on another worksheet.

Does COUNTIF distinguish uppercase and lowercase names?

No. COUNTIF is case-insensitive, so capitalization differences do not prevent a match. Use an EXACT-based formula when case must matter, as shown above.

How can I find names that are on one sheet but not the other?

Run the match formula beside the list you want to audit, then filter its result column to “Not found.” To find names missing in the opposite direction, repeat the check with the two sheets reversed.

Why does Excel say a name is missing when it looks the same?

Hidden spaces, nonbreaking spaces, punctuation, or a small spelling difference can make two entries different. Compare the cell contents and, when appropriate, clean both lists with the same helper formula before matching.

Can I compare names if there are duplicates?

Yes, but a basic match formula only confirms that at least one occurrence exists. It does not confirm that the duplicate counts agree or that each row represents the same person. Check counts or compare a unique ID for a more complete reconciliation.

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.