Find Common Names in Excel with Formulas and Formatting

Formulas & functions · Updated November 20, 2026

To find names that appear in two Excel lists, use COUNTIF to check whether each name in the first list occurs in the second, or use Conditional Formatting to highlight matches. If you have Microsoft 365 or Excel 2021 or later, a formula with FILTER and UNIQUE can also create a separate list of the common names. First put each list in its own column and decide whether you want to count, highlight, or extract the matches.

What You’ll Need

  • A workbook in Microsoft Excel for Microsoft 365 on Windows.
  • Two lists of names, preferably with a heading in row 1 and names starting in row 2. This guide assumes the first list is in A2:A100 and the second is in B2:B100; adjust those references to fit your data.
  • For the automatic extracted-list method, Microsoft 365 or Excel 2021 or later. The COUNTIF and Conditional Formatting methods work in older desktop versions too.

Step-by-Step

  1. Set up and check the two lists. Put one set of names in column A and the other set in column B. Keep a header such as “List 1” or “List 2” in row 1, and place the first name in row 2. Leave each list in a single column so formulas can compare the values cleanly. If the lists have different lengths, that is fine; the formulas can use ranges of different sizes, though making each range comfortably large enough is important. Avoid selecting the heading cells as part of a comparison, or the heading itself could be treated as a name.
  2. Choose what “common” means for your task. Comparing two lists finds names that occur at least once in both lists. It does not necessarily mean a name is repeated several times in either one. For example, if “Jordan Lee” appears once in A and three times in B, it is still a name common to both lists. If instead you need to find duplicate entries within just one list, use a duplicate check for that column rather than comparing the two lists.
  3. Count matches for every name in the first list. With names in A2:A100 and B2:B100, enter this formula in C2 to count how many times the A2 name appears in the second list: =IF(A2="","",COUNTIF($B$2:$B$100,A2)). Press Enter. The result is 0 when the name is not in column B and a positive number when it is. The IF part keeps blank rows blank instead of showing a zero for each empty cell. In C2, for example, if A2 contains “Jordan Lee” and that name appears twice in B2:B100, the result is 2.
  4. Fill the formula down the list. Select C2, then drag the small square at the lower-right corner of the cell down to the last row you need, or copy C2 and paste it into the remaining cells in column C. The dollar signs in $B$2:$B$100 keep the lookup range fixed as the formula is copied. The A2 reference is deliberately relative, so it changes to A3, A4, and so on. Filter column C for values greater than 0 to see only the names from the first list that occur in the second. On the Home tab, select Sort & Filter > Filter, then use the dropdown in C1 to exclude 0 and blank values.
  5. Highlight the common names instead of adding a count column. Select A2:A100. On the ribbon, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With A2:A100 selected and the active cell at A2, enter: =AND(A2<>"",COUNTIF($B$2:$B$100,A2)>0). Choose Format, set a fill or font style, and select OK to create the rule. The conditional test ignores blank cells and highlights a name in column A whenever that name appears at least once in column B. To highlight matches in column B as well, repeat the process with B2:B100 selected and change the formula references so that it checks column A. Conditional Formatting changes the appearance of matching cells; it does not make a separate results list.
  6. Create a separate list of common names. If you have Microsoft 365 or Excel 2021 or later, choose an empty cell outside the source data, such as D2. With names in A2:A100 and B2:B100, enter: =UNIQUE(FILTER(A2:A100,(A2:A100<>"")*(COUNTIF(B2:B100,A2:A100)>0),"No matches")). Press Enter. Excel spills the results into cells below D2: each nonblank name from column A that occurs in column B appears once. If there are no matches, the formula returns “No matches.” Make sure the cells underneath D2 are empty so the spilled results have room. This formula checks names from A against B; it does not automatically include names that appear only in B.
  7. Check the output against the source data. Pick a few reported names and confirm they really occur in both columns. Check a name you expect not to match, too. The count formula should return 0 for it, and the highlighting rule should leave it unhighlighted. If the results look right, you can keep the count column, copy the extracted results, or use the highlights to review the original lists. If you copy dynamic formula results to another workbook or share the file with someone using an older Excel version, consider copying the results and using Paste Special > Values so the names remain available without the formula.

Common Mistakes

Including headers in the range: If you compare A1:A100 with B1:B100, Excel can treat “List 1” and “List 2” as data. Start at row 2 when row 1 contains headings, or update the start row if your names begin elsewhere.

Using inconsistent text: COUNTIF treats uppercase and lowercase letters as equivalent, but extra spaces or different punctuation can prevent a match. “Jordan Lee” and “Jordan Lee ” may look the same on screen while being different text values.

Expecting a unique result from a count: The COUNTIF method marks each row in the first list. If a name appears more than once in A, it can appear more than once among the marked rows. Use the UNIQUE and FILTER method if you want each common name listed just once.

Forgetting the source direction: The examples test names in A against names in B. That is sufficient to identify the overlap, but it does not report names exclusive to B. To check the other direction, switch the ranges and test each B name against A.

Tips

  • Convert each list to an Excel table with Insert > Table if the data will grow often. Tables make it easier to filter and maintain structured data, although the formulas in this guide use ordinary cell references for clarity.
  • Use a clear heading such as “Count in List 2” for column C. A count of 1 or more means a match, while 0 means no match; the exact count can also reveal repeated entries in the second list.
  • Choose the method based on the result you need: COUNTIF gives a reusable count, Conditional Formatting makes matches easy to scan, and FILTER with UNIQUE creates a compact list for reporting.
  • If you are checking a large list, use realistic range limits instead of entire-column calculations in complex formulas. After adding more names, extend the ranges so new rows are included.
  • Keep an unchanged copy of the source lists before cleaning or editing names. That makes it easier to compare results and undo unwanted data changes.

Troubleshooting

The formula returns 0 for a name that appears in both lists. Check for leading or trailing spaces, different punctuation, initials, or spelling differences. Click each source cell and inspect the text in the formula bar. You can create cleaned helper columns using TRIM and CLEAN. With the original name in A2, enter =TRIM(CLEAN(A2)) in a helper cell, then fill down; do the same for the other list and compare the cleaned columns. TRIM removes ordinary extra spaces and CLEAN removes many nonprinting characters. Some copied data contains nonbreaking spaces, which TRIM may not remove, so those entries may need a separate cleanup step.

The formula shows an error or does not update. Confirm that the formula starts with an equals sign and that the ranges contain the intended cells. If Excel shows the formula as text, change the cell format to General and re-enter the formula. If calculation is set to Manual, use Formulas > Calculate Now to recalculate. In some regional settings, Excel expects semicolons instead of commas as argument separators; use the separator shown by your Excel installation if a correctly copied formula is rejected.

The dynamic formula returns #SPILL!. Clear any contents, including spaces or formulas returning an empty string, from the cells where the results should spill. Also check that the formula is not inside an Excel table, where dynamic array results cannot spill as a normal range. Move it to a clear area outside the table.

Some matches are highlighted but the extracted results differ. Verify that both methods cover the same rows and that the conditional formatting rule applies to the intended range. Select a highlighted cell and open Home > Conditional Formatting > Manage Rules to inspect the rule and its Applies to range. Also confirm that the dynamic formula starts from the same source list you intend to report.

Frequently Asked Questions

Does Excel treat uppercase and lowercase names as different?
COUNTIF is not case-sensitive, so “SAM TAYLOR” and “Sam Taylor” count as a match. The formulas here therefore compare names without distinguishing capitalization.

Can I find common names in more than two lists?
Yes. Check a name from the first list against each additional list and require a match in every one. For a small number of lists, separate COUNTIF checks are straightforward; carefully expand the logic and ranges if building a single formula.

How can I find names that appear only in one list?
Use the same count approach but look for a count of 0. For example, the count in column C from the steps is 0 when an A-list name is absent from B. To find names only in B, count each B name in A instead.

Can I use these steps in Excel for the web or on a Mac?
The basic COUNTIF and Conditional Formatting approaches are available in current Excel versions, though ribbon placement and labels can vary slightly on Mac and in the web app. The FILTER and UNIQUE formula requires Microsoft 365 or Excel 2021 or later. If a command appears in a different place, use Excel’s ribbon search to locate it.

Why do repeated names appear more than once in my results?
The COUNTIF method checks every row in the first list, so repeated source entries remain repeated. The FILTER and UNIQUE formula removes repeated names from its output. If you need to preserve counts or occurrences, use the COUNTIF results rather than the unique extracted list.

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.