Find Matching Names in Excel Columns: Easy Methods

How-to basics · Updated November 20, 2026

To find names from one Excel column that also appear in another, enter a COUNTIF formula beside the first list and fill it down. With names in A2:A100 and B2:B100, put =IF(A2="","",IF(COUNTIF($B$2:$B$100,A2)>0,A2,"")) in C2. It returns the name from A when that name appears in B, and a blank otherwise. You can also highlight matches with Conditional Formatting or compare cells on the same row.

The steps below are for Microsoft Excel for Microsoft 365 on Windows. They help compare two lists even when the names are in a different order, identify which entries overlap, and spot missing or duplicate names. Before starting, decide whether you want a separate list of matches, visual highlighting, or a simple yes/no result.

What You’ll Need

  • An Excel workbook containing the two columns of names you want to compare.
  • A clear idea of the first and last data rows in each list. The examples use A2:A100 and B2:B100, with headings in row 1.
  • An empty column for formula results, if you want to create a match list. Save a copy of the workbook before making substantial changes.

Step-by-Step

  1. Check how the lists are arranged. This guide assumes the first list is in column A and the list to check against is in column B. The names do not need to be in corresponding rows. For example, “Jordan Lee” in A2 can match “Jordan Lee” in B47. If your data is in other columns, replace A and B in the examples with your actual columns. Keep the header row out of the ranges.
  2. Choose an output column. Click an unused cell in C2, beside the first name in A2. Add a heading such as “Match in column B” in C1 so the result column is easy to understand. Check that cells below C2 are empty enough for the results you will fill down.
  3. Enter the matching formula. With names in A2:A100 and B2:B100 and the result starting in C2, enter =IF(A2="","",IF(COUNTIF($B$2:$B$100,A2)>0,A2,"")). Press Enter. This checks whether the value in A2 occurs anywhere in B2:B100. If it does, C2 displays the name; if not, C2 stays blank. The first condition prevents an empty cell in column A from being reported as a match just because column B also has empty cells.
  4. Confirm the first result before filling down. If A2 contains “Jordan Lee” and that exact name is present in B2:B100, C2 should show “Jordan Lee.” If it is absent, C2 should be blank. COUNTIF is not case-sensitive, so “Jordan Lee” and “JORDAN LEE” count as a match. It checks a whole cell value for ordinary names, rather than requiring the matching entry to be on the same row.
  5. Copy the formula through the list. Select C2, point to the small square at its lower-right corner, and double-click the fill handle. Excel usually fills the formula down as far as the neighboring data in column A. Alternatively, drag the fill handle to your last row, or copy C2, select C3:C100, and paste. The dollar signs in $B$2:$B$100 keep the comparison range fixed while the reference to A2 changes to A3, A4, and so on.
  6. Filter to see only the matches. Select a cell in the data and choose Data > Filter. Open the filter arrow in column C and clear the selection for blanks, leaving the names selected. You can now review or copy the matching names. To show the names that do not appear in column B instead, filter column C for blanks and check the corresponding entries in column A.
  7. Highlight matching names without making a result column. Select A2:A100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With names in A2:A100 and the comparison list in B2:B100, enter =AND(A2<>"",COUNTIF($B$2:$B$100,A2)>0) in the rule box. Choose Format, select a fill or font style, and select OK. Make sure A2 is the active cell in the selection when you create the rule: it is the first cell in the selected range, and Excel adjusts the row reference for the other cells.
  8. Compare two columns row by row if that is what you mean by matching. A list comparison searches the entire second range; a row-by-row comparison checks only cells side by side. With the values to compare in A2 and B2, enter =IF(A2=B2,"Match","Different") in C2, then fill down. This returns “Match” only when the two cells on that row have the same value. It is a different test from COUNTIF and should be used only when row positions are meaningful.
  9. Adjust the range for longer or changing lists. If your data ends on row 450, update the formula’s comparison range to $B$2:$B$450 and fill the results to row 450. Do not include headings. If you expect the lists to grow often, convert each list into an Excel Table by selecting it and choosing Insert > Table. Tables expand as you add records; you can then use their column names in formulas, though the exact formula depends on the names you give the tables and columns.

Common Mistakes

  • Comparing corresponding rows by accident. The formula =A2=B2 tests only row 2. It will miss a name that appears elsewhere in column B. Use COUNTIF when the order of the two lists may differ.
  • Leaving the comparison range relative. If you omit the dollar signs from B2:B100, the range shifts as you fill down. That can make later results check only part of column B. Lock the range as $B$2:$B$100.
  • Including headings or extra blank rows without thinking it through. A heading can be treated like a name, while blank values can create confusing results. Start at the first data row, and use the blank guard in the main formula.
  • Expecting case-sensitive matching. COUNTIF treats capitalization differences as equal. That is usually appropriate for names, but it is not suitable if uppercase and lowercase must be distinguished.
  • Assuming visually identical text is identical data. A trailing space, a nonbreaking space, or an invisible character can make two displayed names differ. “Ava Chen” and “Ava Chen ” may look alike but fail to match as expected.
  • Overlooking wildcard characters. In COUNTIF criteria, an asterisk (*) can stand for any number of characters, and a question mark (?) can stand for one character. If a name actually contains either character, the formula may interpret it as a pattern rather than a literal character.

Tips

  • Use the output to count overlaps. After filling the match formula down, you can use Excel’s status bar to see a count when you select the nonblank results, or filter the results and count the visible entries. Remember that a name repeated several times in column A appears several times in the result.
  • Decide how duplicates should be handled. The formula checks whether each name in A occurs at least once in B. It does not pair off occurrences or report how many times the name appears in either list. If “Sam Patel” appears three times in A and once in B, all three A entries display as matches.
  • Clean inconsistent spacing in a helper column. With source names in A2:A100 and an empty helper column C, enter =TRIM(CLEAN(A2)) in C2 and fill down. TRIM removes leading and trailing ordinary spaces and reduces repeated internal spaces; CLEAN removes many nonprinting characters. Make a cleaned helper column for each list, then compare those cleaned columns. CLEAN does not remove every unusual Unicode space, so inspect stubborn cases.
  • Keep original data intact. Put cleanup results and match results in new columns rather than replacing the source names. Once you have verified the results, you can copy and paste values into another sheet if you need a fixed list rather than live formulas.
  • Use a consistent name format when possible. “Lee, Jordan” and “Jordan Lee” are different text values to Excel. Decide how to handle middle initials, suffixes, punctuation, and preferred names before comparing lists. A formula cannot reliably determine that two differently written names refer to the same person.
  • Remember platform differences. The formula and Conditional Formatting approach work in Excel for Microsoft 365 on Windows. In Excel for Mac, the formula works the same way; menu wording and dialog layout can vary slightly. In Excel for the web, formulas also work, though some formatting controls may appear in a different place.

Troubleshooting

The formula returns blanks for names you can see in both columns. Check the exact source values by selecting each cell and looking in the formula bar. Remove stray spaces, check punctuation, and confirm that the comparison range includes the relevant rows. Try the cleanup-helper approach above in both lists. If one list contains full names and the other contains first names only, an exact whole-cell comparison will not match them.

Every result says Match, or blanks appear as matches. Verify that the formula refers to the intended comparison column and that its range is not accidentally a whole column containing many empty cells. Use the version with IF(A2="","",...) to leave empty source rows blank. Check that the row reference is A2 for the first result, not a fixed reference such as $A$2.

Only the first few rows work. Click a later result cell and inspect the formula bar. Confirm that the comparison range remains $B$2:$B$100 and that the formula was filled down far enough. If the list extends past row 100, extend both the comparison range and the output formula range.

The conditional formatting rule highlights the wrong cells. Select the intended range again and open Home > Conditional Formatting > Manage Rules. Check that “Applies to” covers the desired cells and that the formula begins with the first cell in that range. For A2:A100, the rule should use A2, not A1 or A3.

Excel displays the formula as text or reports a formula error. Confirm that the cell is not formatted as Text; change it to General, then re-enter the formula. Check that the formula begins with an equals sign, contains straight quotation marks, and uses commas between arguments. If your regional Excel settings use semicolons as separators, replace the commas with semicolons in the formula. If the cell shows the formula instead of a result, also check that Show Formulas is not enabled under the Formulas tab.

Frequently Asked Questions

Can I find names that appear in both columns when the lists are in a different order?
Yes. COUNTIF searches the full comparison range for each name, so the matching entry can be on any row in the other column. The rows do not need to line up.

How can I find names in column A that are missing from column B?
Use the same result formula and filter its output column for blanks. Those blank result rows correspond to nonblank names in A that COUNTIF did not find in B. Check for extra spaces or alternate spellings before treating them as genuinely missing.

Does the formula count duplicate names?
It reports whether a name occurs at least once in the comparison range. It does not match duplicate occurrences one for one. Repeated names in the first list will each receive a match result if that name appears anywhere in the second list.

Can Excel match names without caring about capital letters?
Yes. COUNTIF is case-insensitive, so capitalization alone does not prevent a match. If capitalization must be significant, use a case-sensitive approach instead of COUNTIF; for ordinary name lists, case-insensitive comparison is generally the expected behavior.

Can I match only part of a name?
COUNTIF can search with wildcards, but partial-name matches can produce false positives. For example, a shorter name may appear inside a longer entry. Decide whether you want a whole-cell match or a deliberate partial match before changing the criteria.

Will the formula update when I change a name?
Yes. The result recalculates when a referenced name changes, provided Excel calculation is set to Automatic. If you add names beyond the range used in the formula, expand the comparison range or use an Excel Table so new rows are included.

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.