Compare Two Columns of Names in Excel: Find Matches

To check whether names in one list appear anywhere in another, put the first list in A2:A6 and the second in B2:B6, then enter =IF(COUNTIF($B$2:$B$6,A2)>0,"Match","Not found") in C2 and fill it down. Excel labels each name in column A as a match or not found. This guide also covers comparing lists in both directions, checking names on the same row, highlighting matches, and fixing common data issues.

What You’ll Need

A workbook open in Microsoft Excel for Microsoft 365 on Windows. The main formulas below also work in many earlier Excel desktop versions.
Two columns containing the names you want to compare, with a header row and names starting below it. The examples use columns A and B.
An empty column for results, such as column C. If you want to compare both lists, leave another empty column available too.

You do not need to sort the lists or make them the same length. Keep the original data unchanged while you work, particularly if you are cleaning a list for someone else. If your names are in other columns, replace the example references in the formulas with the cells in your workbook.

Step-by-Step

Set up the lists. Put a clear heading above each column, such as “Original list” in A1 and “New list” in B1. Start names in row 2, and check that each cell contains one name rather than a name combined with other text. The lists can have different numbers of entries. For the example, enter Maria, James, Priya, Daniel, and Olivia in A2:A6; enter James, Olivia, Maria, Noah, and Priya in B2:B6. Leaving a header out of the comparison range prevents Excel from trying to match the heading as if it were a name.
Add a result heading. Type “Status” in C1. With names in A2:A6 and B2:B6, click C2 and enter =IF(COUNTIF($B$2:$B$6,A2)>0,"Match","Not found"). Press Enter. The formula checks whether the name in A2 appears anywhere in the second list and displays one of the two labels. In the sample data, Maria returns “Match.” The dollar signs lock the search range so it stays fixed when the formula is copied, while A2 is allowed to change on each row.
Copy the formula down. Select C2, then move the pointer to the small square at the lower-right corner of the selected cell, called the fill handle. Drag it down to C6. You can also copy C2, select C3:C6, and press Ctrl+V on Windows; on Mac, use Command+V. For a continuous adjacent list, double-clicking the fill handle often fills down to the last neighboring row, but dragging or copying to a chosen endpoint is safer when columns have gaps or different lengths.
Read the results. The example returns Match for Maria, James, Priya, and Olivia, and Not found for Daniel. That means four of the five names in A2:A6 occur in B2:B6; it does not mean the lists are identical. COUNTIF checks each name against the entire specified range, not just the name on the same row. A match can therefore appear in a different position in the other list.
Check the second list as well. To find names in B that are missing from A, add “Status in original” as a heading in D1. With names in A2:A6 and B2:B6, enter =IF(COUNTIF($A$2:$A$6,B2)>0,"Match","Not found") in D2 and fill down through D6. In the sample, Noah is Not found in the original list, while the other four names match. Checking both directions matters: the first formula can find names from A that are present in B, but it cannot reveal extra names that appear only in B.
Choose the right comparison for your question. The list formula is for asking whether a name occurs anywhere in the other column. If instead each row is meant to pair with the same row, use a direct comparison. With the names to compare in A2:A6 and B2:B6, enter =IF(A2=B2,"Same","Different") in C2 and fill down. This checks A2 against B2, A3 against B3, and so on. It does not look elsewhere in column B. For example, Maria in A2 and James in B2 return “Different,” even though Maria appears further down in B.
Optionally highlight matches instead of adding labels. Select A2:A6. Choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With names in A2:A6 and B2:B6, enter =COUNTIF($B$2:$B$6,A2)>0 in the rule’s formula box, choose Format, select a fill or font style, and confirm the dialogs. The relative A2 reference adjusts for each selected row; the dollar signs keep the lookup range fixed. The matching names in column A are highlighted, while names absent from B are left unformatted. To highlight names in B that are also in A, select B2:B6 and use =COUNTIF($A$2:$A$6,B2)>0 as the rule instead.
Adjust the ranges for your real data. If your list extends to row 500, change the relevant range endpoints from 6 to 500 in the formula. For example, with names in A2:A500 and B2:B500, the formula in C2 is =IF(COUNTIF($B$2:$B$500,A2)>0,"Match","Not found"). Keep the lookup range fixed with dollar signs and fill the result formula down only as far as there are names in column A. Do not include a total, notes, or unrelated cells in the lookup range.

The formulas above are case-insensitive: “maria” and “Maria” count as a match. They also match the cell’s contents as Excel evaluates them; leading or trailing spaces can cause a name to be missed. For ordinary names, COUNTIF is convenient and easy to fill down. If a name contains an asterisk (*) or question mark (?), note that COUNTIF treats those characters as wildcards in its criteria. For unusual data that includes those characters, use a different matching approach or clean the characters before relying on the result.

Common Mistakes

Comparing rows when you mean to compare lists. A2=B2 only checks two cells at the same position. To see whether a name appears anywhere in the other column, use the COUNTIF list formula instead.
Forgetting to lock the lookup range. Without dollar signs around the range, copying the formula down can shift the range and produce misleading results. Keep references such as $B$2:$B$6 fixed.
Including the column heading. Start formulas at row 2 when row 1 contains headings. Including a heading can create a false match if both columns use the same heading.
Assuming a match means the lists are identical. A name can occur multiple times, and there can still be extra names in the other list. Compare both directions and consider checking duplicates separately if counts matter.
Ignoring extra spaces or different name formats. “Lee” and “Lee ” may look identical on screen but behave differently. “Anne-Marie” and “Anne Marie” are also different text. Decide whether punctuation, middle initials, or spacing should count as meaningful before changing the data.

Tips

Use descriptive result labels such as “In both” and “Missing from new list” if other people will read the workbook. Clear labels make it less likely that someone will mistake a match for a same-row comparison.
Keep a copy of the source lists before cleaning spaces or standardizing names. Cleaning changes the text, so retaining an untouched version makes it easier to verify or reverse your decisions.
For longer lists, convert the data to an Excel table using Insert > Table. Tables make headings and ranges easier to manage as records are added. Check that a formula or conditional-formatting rule includes new rows when your table grows.
Filter the Status column to show only “Not found” when your goal is to review missing names. Select the header row and use Data > Filter if filter buttons are not already visible. You can then inspect the exceptions without deleting or rearranging the source data.
If you need case-sensitive matching, the usual COUNTIF formula is not sufficient. With names in A2:A6 and B2:B6, enter =IF(SUMPRODUCT(--EXACT(A2,$B$2:$B$6))>0,"Match","Not found") in C2 and fill down. EXACT distinguishes uppercase and lowercase, so “maria” does not match “Maria.”

Troubleshooting

Every result says Not found. Confirm that the lookup range points to the other name column, that it includes all relevant rows, and that the source cells contain names rather than numbers or formulas returning unexpected text. Check for leading or trailing spaces by clicking a cell and inspecting its contents in the formula bar. One quick cleanup option is to copy the names to a spare column and use the TRIM function there. With a name in A2, enter =TRIM(A2) in an empty cell and fill down, then compare the cleaned column. TRIM removes ordinary extra spaces at the beginning and end and reduces repeated internal spaces to single spaces; it does not fix every kind of imported whitespace.

Some names match and others do not, despite looking the same. The entries may contain nonbreaking spaces, hidden characters, punctuation differences, or extra middle initials. If the data came from a website or another system, try pasting a few cells into a plain-text editor to inspect what was imported, and compare the formula-bar contents. Excel’s CLEAN function can remove many nonprinting characters, but it does not remove every Unicode character. For stubborn data, standardize both lists using the same process and verify a small sample before replacing the original values.

The formula appears as text or does not calculate. Make sure the cell is not formatted as Text. Change its format to General, edit the cell by pressing F2, then press Enter to make Excel interpret the formula. Also check that the formula begins with an equals sign and that calculation is enabled under Formulas > Calculation Options > Automatic. If Excel shows a formula error after entry, confirm that quotation marks and parentheses are balanced and that your Excel uses commas as argument separators. Regional settings can require semicolons instead of commas.

Conditional formatting highlights the wrong cells. Recheck which range you selected before creating the rule. If you selected A2:A100, the formula should begin with A2, not A1 or A3, because the formula’s relative reference is evaluated from the first selected row. Confirm that the other list range is locked with dollar signs, and look for an older conditional-formatting rule that may also be applying a fill. Review rules through Home > Conditional Formatting > Manage Rules.

Frequently Asked Questions

Does Excel compare names with uppercase and lowercase letters?
COUNTIF and the direct equals formula used here are not case-sensitive. “JORDAN” and “Jordan” count as a match. Use the EXACT and SUMPRODUCT option in Tips when letter case must be treated as different.

Can I compare columns that have different numbers of names?
Yes. Set the lookup range to cover the full comparison list, and fill the result formula down only for the list you are checking. To find unmatched names in both lists, run a formula in each direction.

How do I find duplicate names within one column?
That is a different check from comparing two lists. Select the cells in the column and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Review the highlighted entries, since repeated names may be legitimate records rather than errors.

Will the formulas update when I add more names?
They update when new names are entered inside the referenced range, but a formula limited to row 6 will not include a new name entered in row 7. Extend the ranges or use an Excel table and confirm that the formulas and formatting cover added rows.

Can I remove names that do not match?
You can filter the result column to show Not found, then review those rows before taking action. Avoid deleting based only on a formula if names may have spelling variations or hidden spaces; validate the exceptions and keep a copy of the original data first.

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.