Find Matching Names in Two Excel Columns With Formulas

Formulas & functions · Updated November 20, 2026

To find names in column A that also appear in column B, enter =IF(COUNTIF($B$2:$B$100,A2)>0,"Match","Not found") beside the first name and fill the formula down. It marks each name in A according to whether Excel finds it anywhere in B; adjust the ranges to fit your data.

Choose the matching method

The best method depends on what you want Excel to show. A formula beside each name is useful for checking a list row by row. A filtered list is better when you want to see only the names that occur in both columns. Conditional formatting can quickly highlight matches without adding a results column.

  • Check each name: Use COUNTIF to return a label such as “Match” or “Not found.”
  • Return a match list: Use FILTER with XMATCH in Microsoft 365 or Excel 2021 and later.
  • Highlight matches: Create a conditional-formatting rule based on COUNTIF.

These methods compare the text in the cells, not the real-world identities of the people. If two lists contain names such as “Alex Kim,” a match means the text appears in both lists; it does not establish that both entries refer to the same person.

Check each name with COUNTIF

Suppose the first list is in A2:A100 and the second list is in B2:B100. Put the following formula in C2, then copy it down through C100. The dollar signs keep the lookup range fixed as the formula moves down, while A2 changes to A3, A4, and so on.

With names in A2:A100 and B2:B100, enter this in C2:

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

COUNTIF counts how many cells in column B meet the criterion supplied by A2. If the count is greater than zero, IF displays “Match”; otherwise it displays “Not found.” For example, if A2 contains “Jordan Lee” and that text appears once in B2:B100, C2 returns “Match.” If it is absent, C2 returns “Not found.”

To check in the opposite direction, place the same pattern beside column B and reverse the roles of the ranges. With names in A2:A100 and B2:B100, enter this in D2 and fill down:

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

Checking both directions matters when the lists are different lengths or one list contains extra names. The first check answers “Which names from A occur in B?” The reverse check answers “Which names from B occur in A?” Those are related questions, but they are not interchangeable.

Return only the matching names

If you do not need a status for every row, a dynamic-array formula can produce a separate list of names from A that also occur in B. FILTER and XMATCH are available in Microsoft 365 and Excel 2021 or later. Enter the formula in an empty cell with enough blank cells beneath it for the results to spill.

With names in A2:A100 and B2:B100, enter this formula in an empty cell:

=FILTER(A2:A100,ISNUMBER(XMATCH(A2:A100,B2:B100)),"No matches")

XMATCH looks for each name from A2:A100 in the B range. ISNUMBER turns successful position results into TRUE values, and FILTER returns the corresponding names from A. If no names match, the formula displays “No matches.” The output updates when the source values change, and Excel displays a spill-range error if occupied cells block the results.

This formula preserves repeated entries from column A. If “Jordan Lee” occurs three times in A and appears at least once in B, the result can include “Jordan Lee” three times. That is appropriate when each row represents a separate record, but it may not be what you want for a list of distinct names.

Highlight matching names without a helper column

Conditional formatting is a quick visual check. Select A2:A100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Type the formula below, choose a format such as a fill color, and confirm the rule. Excel highlights each selected cell whose value is found in B2:B100.

With names in A2:A100 and B2:B100, and A2:A100 selected, use this rule formula:

=COUNTIF($B$2:$B$100,A2)>0

The reference to A2 is deliberately relative, so the rule checks each selected row in turn. The B range is absolute so it remains fixed. To highlight names in B that occur in A, apply a second rule to B2:B100 and use the corresponding reversed ranges. Conditional formatting changes appearance, not cell contents, so it is less convenient than a helper column if you need to sort, filter, or export a match status.

Understand what Excel considers a match

COUNTIF is not case-sensitive: “Morgan Chen” and “morgan chen” count as a match. It otherwise compares the cell text as written, so a different spelling, an extra middle initial, or an added suffix can prevent a match. Leading or trailing spaces can also make two entries look alike on screen while their underlying text differs.

COUNTIF treats the asterisk (*) and question mark (?) as wildcard characters in criteria. That means a name containing one of these characters could be interpreted as a pattern rather than as literal text. This is unusual for ordinary names, but it can matter when matching codes or imported data. If literal wildcard characters occur in your values, clean or escape those criteria before relying on COUNTIF.

If capitalization must matter, use EXACT with SUMPRODUCT instead. EXACT compares text case-sensitively, and SUMPRODUCT counts the TRUE results across the comparison range. With the name to check in A2 and the comparison names in B2:B100, use this in C2 and fill down:

=IF(SUMPRODUCT(--EXACT(A2,$B$2:$B$100))>0,"Match","Not found")

For example, “Morgan Chen” and “morgan chen” will not match with this case-sensitive test. Most name-list checks should remain case-insensitive because capitalization varies between sources, but the stricter formula can be useful when case carries meaning in the data.

Clean spaces and inconsistent entries

When names come from websites, exports, or copied documents, extra spaces are a common cause of apparent mismatches. TRIM removes leading and trailing ordinary spaces and reduces repeated internal ordinary spaces to one. It does not remove every special whitespace character, including the nonbreaking space that can arrive in copied text.

With an original name in A2, use this in a helper cell such as C2, then fill down. Repeat for column B in another helper column, changing A2 to the appropriate B-cell reference:

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

Compare the cleaned helper columns with the COUNTIF method rather than overwriting the original source data. Keeping the original values makes it possible to review what changed and return to the unmodified records. Cleaning spaces will not fix differences in spelling, punctuation, order, accents, or name formats; those require a deliberate rule rather than an automatic assumption.

Duplicates, blanks, and list meaning

A match test asks whether a value appears at least once, not how many times it appears. If A contains a name once and B contains it five times, the basic formula still returns “Match.” To investigate repeated records, use a count rather than a yes-or-no label. With the same ranges, the count of occurrences of A2 in B is:

=COUNTIF($B$2:$B$100,A2)

Blank cells deserve attention as well. A blank checked against a range containing blanks may be treated as a match by a straightforward COUNTIF test. If blank rows should never count, add an explicit nonblank check. With names in A2:A100 and B2:B100, use this in C2 and fill down:

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

This leaves the result blank when A2 is blank and checks nonblank names normally. Decide whether duplicates represent legitimate separate records before removing them; two identical names can belong to different people, while repeated rows can also be accidental duplicates.

Common problems and how to diagnose them

  • Every result says “Not found.” Check that the formula points to the correct columns and that the lookup range covers all rows. Compare a suspected pair in the formula bar, not just by appearance.
  • Some names look identical but do not match. Check for extra spaces, nonbreaking spaces, punctuation, middle initials, or different spellings. Try helper columns to normalize whitespace.
  • The output shows a formula instead of a result. Confirm that the cell is not formatted as Text, then re-enter the formula. Also check that Show Formulas is not enabled.
  • A filtered list shows an error. FILTER needs room to spill its results. Clear cells below and beside the formula, and check for merged cells in the destination area.
  • The result changes when copied down. Make sure the comparison range uses dollar signs, as in $B$2:$B$100. Without them, the range shifts along with the formula.

If the lists are long, converting each data range into an Excel Table can make ranges easier to manage as rows are added. Structured references can be used in formulas, but the basic cell-range approach is often simpler for a one-time comparison. Whatever method you use, check that the source columns contain the intended data and that the ranges exclude headings unless those headings are also meant to be compared.

Frequently Asked Questions

Can Excel find names that appear in both columns?
Yes. COUNTIF can check each name against the other column, while FILTER with XMATCH can return a list of matches in Microsoft 365 or Excel 2021 and later.

How do I compare two columns and show only the names in common?
Use the FILTER and XMATCH formula above in an empty cell. It returns names from the first list when they are present in the second, and the results spill into cells below.

Does COUNTIF distinguish uppercase and lowercase letters?
No. COUNTIF is case-insensitive. Use the EXACT and SUMPRODUCT formula above if the capitalization must match exactly.

Can I match names when one column has extra spaces?
Clean both columns first with TRIM and, for nonbreaking spaces, SUBSTITUTE with CHAR(160). Then compare the cleaned helper columns, keeping the original data intact.

Can Excel identify two different people with the same name?
No formula can determine identity from a name alone. If names are not unique, compare an additional identifier such as an employee number or email address, subject to your data-handling rules.

Will the formulas work in Excel for Mac?
COUNTIF and the helper-column method work in current Excel for Mac as well as Excel for Microsoft 365 on Windows. FILTER and XMATCH require Microsoft 365 or Excel 2021 or later; availability depends on the Excel version installed.

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.