Find Matching Names in Two Excel Lists: Methods That Work

How-to basics · Updated November 20, 2026

To find names that appear in both Excel lists, add a helper column beside the first list and use COUNTIF to check each name against the second. With names in A2:A6 and B2:B6, enter =IF(COUNTIF($B$2:$B$6,A2)>0,"Match","No match") in C2 and fill it down; each row will show whether that name is in the other list.

How the basic match check works

COUNTIF counts how many cells in a range meet a condition. In this example, Excel checks whether the value in A2 occurs anywhere in B2:B6. The dollar signs keep the second-list range fixed as you copy the formula down, while A2 changes to A3, A4, and so on. The IF function turns the count into an easy-to-read label.

For example, suppose A2:A6 contains Ben, Cara, Diego, Erin, and Grace, while B2:B6 contains Cara, Erin, Farah, and Ben. The formula returns Match for Ben and Cara, No match for Diego, Match for Erin, and No match for Grace. The order of names in the two lists does not matter.

To set it up:

  1. Put the two lists in separate columns, with a header in row 1 if you have one.
  2. Enter the formula in the first result cell, such as C2.
  3. Fill the formula down to the last row of the first list.
  4. Filter the result column for Match if you want to see only the names found in both lists.

In Excel for Microsoft 365 on Windows, you can fill a formula down by dragging the small square at the bottom-right corner of its cell. You can also select the formula cell and the cells below it, then use Home > Fill > Down. The formula itself works the same in Excel for Mac; keyboard shortcuts and some interface details can differ.

Return the matching name instead of a label

If you want Excel to return the matching value from the second list, use XLOOKUP. It searches for the name in the second list and returns the corresponding value from that same range. This is useful when the second list contains the official spelling or an associated value in a neighboring column.

With names in A2:A6 and B2:B6, enter =XLOOKUP(A2,$B$2:$B$6,$B$2:$B$6,"Not found",0) in C2 and copy it down. If A2 contains Ben and Ben is in B2:B6, the result is Ben; if A2 contains Diego and Diego is absent, the result is Not found. The final 0 asks for an exact match. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; for older Excel versions, use the COUNTIF method or a lookup function available in that version.

In this example, the lookup range and return range are the same, so XLOOKUP returns the matched name. If the second list has related information in column C, such as an employee ID, use B2:B6 as the lookup range and C2:C6 as the return range instead. Keep those ranges the same size and aligned row by row.

Create a separate list of shared names

A match flag is helpful for reviewing the original list, but sometimes you want a compact output containing only the names found in both lists. In Microsoft 365 or Excel 2021 and later, FILTER can return those matches as a spilling list.

With the first list in A2:A6 and the second in B2:B6, enter =FILTER(A2:A6,COUNTIF(B2:B6,A2:A6)>0,"No matches") in an empty cell. For the example lists Ben, Cara, Diego, Erin, Grace and Cara, Erin, Farah, Ben, it returns Ben, Cara, and Erin in the order they appear in the first list. The results spill into the cells below, so leave that area clear.

This output preserves repeats from the first list. If Ben appears twice in A2:A6 and is present in the second list, Ben can appear twice in the filtered result. That may be correct when each row represents a separate record. If you need a distinct set of names, you can wrap the FILTER expression in UNIQUE: =UNIQUE(FILTER(A2:A6,COUNTIF(B2:B6,A2:A6)>0,"No matches")). UNIQUE is also available in Microsoft 365 and Excel 2021 or later.

Prepare the lists before comparing

Exact matching can miss names that look alike but contain different characters. A leading space, an extra space between words, or a nonbreaking space copied from a web page can make a value differ from the one you expect. Before comparing, check that both lists have a consistent format and that the cells contain names rather than a mixture of names, notes, or blank rows.

A useful check is to sort each list and scan for unexpected spaces, spelling differences, or inconsistent use of middle initials. Sorting does not need to put both lists in the same order; it simply makes unusual entries easier to spot. Also check whether one list uses full names while the other uses preferred names, or one includes a suffix such as Jr. Those are data differences that a standard exact-match formula cannot resolve by itself.

If unwanted spaces or nonprinting characters are likely, create cleaned helper columns rather than changing the source data immediately. With a raw name in A2, enter =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) in C2 and fill down. For example, a value with an ordinary space before Alice becomes Alice. SUBSTITUTE changes common nonbreaking spaces to ordinary spaces, CLEAN removes certain nonprinting characters, and TRIM removes leading and trailing spaces and reduces repeated ordinary spaces between words.

Apply the same cleanup to the second list in another helper column, then compare the cleaned columns with COUNTIF or XLOOKUP. TRIM and CLEAN do not fix spelling variations, initials, or every possible invisible Unicode character, so inspect any remaining surprising results. Keep the original columns until you have confirmed that the cleaned versions are appropriate for your records.

Understand duplicates and counts

A match check answers whether a name appears at least once in the other list. It does not show how many times it appears. COUNTIF can provide that detail, which matters when duplicates might represent repeat transactions, duplicate records, or a data-entry problem.

With names in A2:A6 and B2:B6, enter =COUNTIF($B$2:$B$6,A2) in C2 and fill down. If Ben appears once in B2:B6, the result beside Ben is 1; if Ben appears three times, it is 3; if Ben is absent, it is 0. Use the count to decide whether a simple yes/no match is enough or whether duplicate review is part of the task.

Do not assume that matching names prove two records refer to the same person. Different people can share a name, and the same person can appear under slightly different names. When the decision has consequences, compare an additional identifier, such as an employee number or customer ID, if you are authorized to use it. Names alone are often insufficient for confirming identity.

Highlight matches in the worksheet

If you prefer visual review to a separate result column, conditional formatting can highlight names from one list that appear in the other. Select A2:A6, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With the selected range starting at A2 and the comparison list in B2:B6, use =COUNTIF($B$2:$B$6,A2)>0 as the rule formula, choose a format, and confirm the rule.

The relative reference A2 changes for each selected row, while the dollar signs keep the lookup range fixed. The highlight updates when the names change, but it does not create a separate list or resolve spelling differences. Conditional formatting is most useful for a quick visual check; a helper column is generally easier to filter, copy, or audit. The Windows ribbon path above is for Excel for Microsoft 365. Excel for Mac has conditional-formatting controls too, though the dialog layout can differ.

Choose a method and interpret the result

Use the method that matches the result you need, rather than treating every kind of comparison as the same task:

  • COUNTIF with IF: best for a simple Match or No match label beside each entry.
  • XLOOKUP: useful when you want a returned value, such as the matching name or a related ID.
  • FILTER: useful for making a separate list of matching rows in newer Excel versions.
  • Conditional formatting: useful when you want to scan the original list visually.
  • COUNTIF alone: useful when the number of occurrences matters, not just whether a match exists.

These standard methods are generally not case-sensitive: Ben and BEN are treated as a match. They also look for exact cell values, not approximate spellings. For example, Ana and Anna are different values, and matching a first name alone does not establish that two full records are the same person. If case must distinguish names or the data contains near-matches, define that requirement before choosing a comparison method; a fuzzy or case-sensitive comparison needs a different approach and should be checked carefully.

One other practical issue is list size. For ordinary lists, the formulas above are straightforward. For very large workbooks, avoid comparing unnecessarily huge ranges such as entire columns in many repeated calculations if the workbook becomes slow. Use ranges that cover the data, or convert the data to Excel tables so ranges can expand as rows are added. If formulas display as text instead of calculating, check that the cells are not formatted as Text and that calculation is enabled.

Frequently Asked Questions

How do I find names that are in both Excel columns?
Put a COUNTIF check beside one list, such as =IF(COUNTIF($B$2:$B$100,A2)>0,"Match","No match"), and fill it down. Filter for Match to see the names present in both columns.

Can Excel show only the names that match?
Yes. In Microsoft 365 or Excel 2021 and later, use FILTER with COUNTIF to return matching entries in a new area. The result spills automatically, and you should leave enough empty cells below the formula.

Why does Excel say a name does not match when it looks identical?
Check for extra spaces, nonbreaking spaces, nonprinting characters, and differences such as middle initials or punctuation. Clean both lists consistently, then compare the cleaned values. A visual resemblance does not guarantee that the underlying cell contents are identical.

Does COUNTIF distinguish uppercase and lowercase names?
No. COUNTIF matching is not case-sensitive, so Ben and BEN count as a match. If capitalization is intended to distinguish values, use a case-sensitive method rather than relying on COUNTIF.

How can I find duplicate names in one list?
Use COUNTIF to count each name within its own list. With names in A2:A100, for example, =COUNTIF($A$2:$A$100,A2) returns the number of occurrences of the value in A2. Counts greater than 1 identify repeated entries, though you should decide whether those repeats are errors or valid records.

Will the formulas work if my Excel uses semicolons?
Some regional settings use semicolons instead of commas between formula arguments. If Excel rejects a formula with commas, use the argument separator required by your Excel settings; the formula logic stays the same.

Related

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.