To find names that appear in both sheets, put the names in column A of worksheets named List1 and List2, then enter this in List1!B2 and fill down; it marks each exact match. For names in List1!A2:A100 and List2!A2:A100, use =IF(ISNUMBER(XMATCH(A2,List2!$A$2:$A$100,0)),"Common",""). Filter the new column for Common to see the shared names. This is useful for comparing contact lists, sign-up sheets, staff rosters, or other records. The steps below are for Microsoft Excel for Microsoft 365 on Windows.
What You’ll Need
- An Excel workbook with both lists available. They can be on separate tabs in one workbook; the examples use tabs named List1 and List2.
- A column containing one name per cell on each tab, with a header in row 1 and names starting in row 2. Change the example ranges if your data is longer, shorter, or in another column.
- Excel for Microsoft 365, Excel 2021, or a version that includes XMATCH. If your Excel does not recognize XMATCH, use the COUNTIF alternative in the troubleshooting section.
- A moment to check that the lists use the same kind of values. For example, compare full names with full names rather than a full name on one tab and first names only on the other.
Step-by-Step
- Put both lists in the same workbook. If the lists are in different files, open both workbooks first. For a simpler formula, copy the second list into a new tab in the workbook that contains the first list. To add a tab, select the plus button beside the sheet tabs, then give the tabs clear names such as List1 and List2. The formula in this guide refers to a worksheet tab, not a separate workbook file, so keeping the lists together reduces reference errors.
- Check the layout and identify the name columns. Use one name per cell, with no merged cells in the list. In this example, both lists have a heading in A1 and names in A2 downward. The lists do not need to be the same length. If List1 has 300 names and List2 has 150, the formula can still compare the full first list with the full second list; just expand the example range to cover the actual last row. Avoid including entire columns until you have a reason to: a defined range is easier to inspect and can make a large workbook more responsive.
- Make sure the sheet names match the example. Double-click a tab name to rename it to List1 or List2, then press Enter. If you prefer to keep different tab names, edit the formula so its List2 reference uses the actual name. A worksheet name containing a space or punctuation needs single quotes around it in a formula. For example, the worksheet reference for a tab named Contact List is written as ‘Contact List’!$A$2:$A$100. Check spelling carefully; a wrong tab name can cause a reference error.
- Add a result column on the first tab. On List1, type a heading such as Match in B1. Select B2, the first row beside a name. With names in List1!A2:A100 and the comparison names in List2!A2:A100, enter
=IF(ISNUMBER(XMATCH(A2,List2!$A$2:$A$100,0)),"Common",""). Press Enter. XMATCH looks for the value in A2 in the List2 range. The 0 requests an exact match, ISNUMBER checks whether XMATCH found a position, and IF displays Common when it did or leaves the cell empty when it did not. The dollar signs keep the comparison range fixed when the formula is copied down. - Confirm the first result before filling the formula. Pick a name you know appears on both tabs and check whether B2, or the relevant result cell, says Common. Then check a name you know is only on List1; its result should be blank. For example, if List1 contains Ava, Noah, and Maya, while List2 contains Noah and Maya, the rows for Noah and Maya should say Common and Ava’s row should be blank. If the result looks wrong, do not copy the formula down yet; check the cell references and spelling first.
- Copy the formula to the rest of the rows. Select B2 and drag the small square at the lower-right corner of the selection down to the last name row. Alternatively, copy B2, select the remaining cells in column B, and paste. Excel adjusts A2 to A3, A4, and so on, while the dollar signs keep the List2 range unchanged. If you have formatted the data as an Excel table, entering the formula in the first row of the Match column usually fills the calculated column automatically. Inspect the bottom few rows to make sure the formula reached the last name.
- Filter to show only shared names. Select a cell in the data area and choose Data > Filter. Use the drop-down arrow in the Match heading, clear Select All if needed, select Common, and confirm. List1 now shows only rows with names found in List2. Filtering hides the other rows; it does not delete them. To restore the full list, open the same drop-down and select all values, or choose Data > Clear to clear filters. You can copy the visible names to a separate area if you need a standalone results list.
- Optionally create a compact results list with one formula. If you have a dynamic-array version of Excel and want the matches to spill into a separate area, enter this in an empty cell such as D2. With names in List1!A2:A100 and List2!A2:A100, use
=FILTER(List1!A2:A100,(List1!A2:A100<>"")*ISNUMBER(XMATCH(List1!A2:A100,List2!A2:A100,0)),"No matches"). Excel returns the names from List1 that also occur in List2, in their original order. For the example lists Ava, Noah, Maya and Noah, Maya, the result is Noah and Maya. The formula excludes blank cells in List1; it does not remove repeated names, so if Noah occurs twice in List1, Noah appears twice in the results. FILTER and XMATCH are available in Microsoft 365 and Excel 2021 or later. Keep the cells below and beside D2 empty so the results can spill; a blocked spill area produces a #SPILL! error. - Save a copy of the results if you need to share them. After filtering, copy the visible name cells and use Paste Special > Values in a new area or another workbook if you want a fixed list that will not change when the source data changes. Keep the original formula column if you may need to update the comparison later. If you are sending the workbook to someone, tell them whether the results are filtered or are a separate pasted list so they do not mistake hidden rows for deleted data.
Common Mistakes
Comparing the wrong fields: Exact matching compares the contents of cells, not a person’s identity. Jordan Lee will not match Jordan if one list has full names and the other has first names only. Decide whether to compare first names, last names, email addresses, or a unique ID, and make the same field available in both lists.
Assuming capitalization matters: XMATCH in this formula is not case-sensitive, so alex smith and Alex Smith count as a match. If capitalization is meaningful for your task, this formula alone will not distinguish the two. Also, exact matching is not partial matching: Ann does not match Annette.
Missing spaces or invisible characters: A trailing space makes a cell value different from the same name without that space. Nonbreaking spaces copied from websites can also be hard to see. Clean or normalize both lists consistently before comparing them.
Using the wrong range or copying a shifting range: If the comparison range starts at row 1, includes a heading, or ends before the last name, results can be misleading. The dollar signs in $A$2:$A$100 prevent the comparison range from moving as you fill the formula down. Update the end row if your list extends beyond 100.
Treating repeated names as unique people: If two different people share a name, the formula reports a match even if they are not the same person. Conversely, duplicates in a list may make the results appear more numerous than the number of distinct shared names. Use a unique identifier such as an employee number or email address when you need to confirm identity.
Tips
Turn each list into an Excel table with Ctrl+T on Windows (on Mac, use Command+T), and confirm that the table has headers. Tables expand as you add rows, making it less likely that new names will fall outside a fixed range. Give each table and column clear names if you are comfortable editing structured references; otherwise, the cell-reference method above is straightforward and easy to audit.
For names imported from other sources, use a spare column to standardize text before comparing. TRIM can remove ordinary extra spaces at the beginning and end, but it does not remove every invisible character, and cleaning must be applied consistently to both lists. Keep the original columns unchanged until you have checked the cleaned results. Where the data is important, compare unique identifiers rather than trying to make inconsistent names look equivalent.
Consider what you want the output to mean. The helper-column method answers, “Does this List1 entry occur at least once in List2?” It keeps List1’s original order and preserves duplicates. It does not count how many times a name occurs, list names unique to List2, or establish that two same-named entries are the same person. Those are different comparisons and may need a separate count, a second helper column, or a unique ID.
If you only need a visual check rather than a separate results list, use conditional formatting. Select the name cells in List1, choose Home > Conditional Formatting > New Rule, and create a rule that uses a formula to determine which cells to format. The helper-column formula is usually easier to inspect and filter, especially when you need to verify the matches or share the output. Save before making large cleanup changes, and keep an untouched copy of the source lists.
Troubleshooting
The formula shows #NAME?: Your Excel version may not include XMATCH, or the function name may have been mistyped. Use the COUNTIF option below, or update Excel if your organization allows it. Also check that the formula starts with an equals sign and that quotation marks are straight double quotes.
With names in List1!A2:A100 and comparison names in List2!A2:A100, this alternative returns Common for a name found in List2: =IF(COUNTIF(List2!$A$2:$A$100,A2)>0,"Common",""). COUNTIF is available in older Excel versions. It is not case-sensitive, and it can treat the asterisk and question mark as wildcard characters, so unusual names containing those characters may need a different approach.
Every result is blank: Confirm that the formula is on the same row as the first name and that it refers to the correct comparison column. Check the tab name, the start and end rows, and whether the names are actually identical apart from appearance. A space at the end of a cell, a middle initial, or a different spelling is enough to prevent an exact match. Try searching for a name manually on List2 with Ctrl+F on Windows or Command+F on Mac, then inspect the cell contents.
Names that look the same do not match: Check for extra spaces, line breaks, nonbreaking spaces, or inconsistent punctuation. Click into each cell and inspect the formula bar. If the names were imported, paste a few into a plain-text editor to reveal some differences, or use temporary cleanup columns and compare the cleaned values. Do not replace original data in bulk until you have reviewed a sample and saved a backup.
Too many matches appear: The formula checks whether a value occurs anywhere in List2, not whether it appears the same number of times. It also ignores differences in letter case. If your goal is to match people rather than text values, compare a more reliable field such as an email address or ID. If the result list contains repeated names, remember that each matching row in List1 is returned separately.
The compact list shows #SPILL!: Clear cells in the output area that might block the results, including cells that look blank but contain spaces or formulas. Also check for merged cells in the spill area. If you cannot clear the area, place the formula in a different empty column or use the helper-column and filter method instead.
The formula appears as text instead of calculating: Select the cell and set its number format to General from Home > Number Format, then press F2 and Enter to re-enter the formula. Check that there is no apostrophe before the equals sign. If Excel is showing formulas throughout the sheet, press Ctrl+` on Windows to toggle Show Formulas; on many Mac keyboards the equivalent is Control+`. You can also use Formulas > Show Formulas.
Frequently Asked Questions
Can I compare lists on two separate worksheet tabs?
Yes. The examples compare List1 with List2 in the same workbook. Change the sheet references to your actual tab names, and put single quotes around a tab name that contains spaces or punctuation. If the lists are in separate workbooks, copying one list into a tab in the other workbook is often the simplest setup.
Does this find partial names?
No. XMATCH with match mode 0 checks for an exact cell value, though it ignores capitalization. A first name will not match a full name, and a substring will not match a longer name. Choose a consistent field or prepare comparable values before running the check.
Can I find names that are in List2 but not List1?
Yes. Put the helper formula beside the names on List2 and point its comparison range to List1 instead. Then filter that helper column for Common to identify the shared entries from List2, or use a not-found label if you want to isolate names that occur only on List2.
Will the result update when I edit a name?
Yes, if calculation is set to Automatic, Excel recalculates the formula when source values change. Check Formulas > Calculation Options > Automatic if results do not refresh. The fixed range still needs to include any new rows you add; an Excel table can help that range expand with new entries.
Does the formula remove duplicate names?
No. The helper-column method marks every matching row in List1, and the FILTER formula returns matching rows in List1, including duplicates. If you need one copy of each shared name, create a distinct list as a separate step and verify the names are genuinely unique people before relying on it.
What is the safest field to compare?
A unique, consistently recorded identifier—such as an employee ID or email address—is usually more reliable than a person’s name. Names can be shared, changed, abbreviated, or entered with different punctuation. If you must compare names, standardize their format and review uncertain matches manually.
Related
- Extract Rows in Excel to a New Sheet: Step-by-Step
- GST Number to PAN in Excel: Extract the 10-Character PAN
- How to Extract a Filtered List in Excel
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Text Goes Into the Next Cell? How Overflow WorksExcel text does not actually go into the next cell: it remains in the cell where you entered…
- How to Get Out of Replace Mode in Excel and Restore TypingTo get out of Overtype (replace) mode in Excel for Microsoft 365 on Windows, click inside the cell…
- How to Change the View in Outlook to Preview EmailsTo preview emails in Outlook without opening each one, turn on the Reading Pane: in classic Outlook for…
- How to Insert an Image in Google Sheets: In a CellTo insert an image in Google Sheets, open the sheet in a web browser, select Insert > Image,…
- Google Sheets Dropdown From a Table: How It WorksYes. In Google Sheets, create a dropdown from table data by selecting the cells that need the menu,…
- Delete Data Validation in Excel: Steps and OptionsTo delete data validation in Excel for Microsoft 365 on Windows, select the cell or range, choose Data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.