To find names from one worksheet in another, enter a check formula beside the list you want to search. If the names to check are in Sheet2 column A and the reference list is in Sheet1 column A, use =IF(ISNUMBER(MATCH(A2,'Sheet1'!$A$2:$A$100,0)),"Found","Not found") in Sheet2 row 2, then fill it down. The formula marks each name according to whether Excel finds an exact match in the other worksheet.
Choose a method for comparing the lists
The best approach depends on what “find names” means in your workbook. A formula is useful when you want a result that updates as names are added or changed. Conditional formatting is useful when you want to spot matching entries visually. A lookup formula can return another field, such as an email address or employee ID, rather than just report whether a name exists.
For a small, one-time check, you can also use Excel’s Find command, but it searches for text in the workbook rather than comparing every name on one sheet with a list on another. For a repeatable comparison, formulas are usually clearer and easier to audit.
- Use MATCH or COUNTIF to label names as found or not found.
- Use XLOOKUP when you want to return information attached to a matching name.
- Use a helper column and conditional formatting when a visual highlight is more convenient than a text result.
- Use Power Query for a recurring comparison involving larger or frequently refreshed lists.
The examples below assume Microsoft Excel for Microsoft 365 on Windows, with the two worksheets in the same workbook. If your sheet name contains spaces, put it in single quotation marks in a formula, as in 'Staff List'!$A$2:$A$100. If a sheet name has no spaces, quotation marks are optional.
Use MATCH to mark names found on the other sheet
MATCH searches a range for a value and returns its position when it finds one. Wrapping MATCH in ISNUMBER and IF turns that position into an easy-to-read status. The final argument, 0, requests an exact match; without it, MATCH can use approximate matching under certain conditions.
Assume Sheet1 contains the reference names in A2:A100, while Sheet2 contains names to check in A2:A100. In Sheet2 B2, enter =IF(ISNUMBER(MATCH(A2,'Sheet1'!$A$2:$A$100,0)),"Found","Not found"), then copy the formula down alongside the names. For example, if “Jordan Lee” occurs in Sheet1’s range, the result beside that entry on Sheet2 is “Found”; if “Morgan Chen” does not occur there, the result is “Not found.”
The dollar signs lock the reference range when you fill the formula down. A2 has no dollar signs, so it changes to A3, A4, and so on for each row. To fill down, select the formula cell and drag its fill handle, or select the range and press Ctrl+D on Windows. On Mac, use Command+D to fill down.
This formula checks each name on Sheet2 against Sheet1. To check the other direction, place an equivalent formula beside the names on Sheet1 and refer to the name list on Sheet2. Comparing both directions matters when you need to identify names that appear only in one list, because a one-way check will not reveal all unmatched entries.
Use COUNTIF when you also need duplicate counts
COUNTIF is a direct option when the question is whether a name appears at least once. It also tells you how many times it appears, which is helpful when the reference sheet might contain duplicates.
With names to check in Sheet2 A2:A100 and reference names in Sheet1 A2:A100, enter =COUNTIF('Sheet1'!$A$2:$A$100,A2) in Sheet2 B2 and fill down. If “Jordan Lee” appears twice in the reference range, the result is 2; if there is no match, the result is 0. To display words instead of a count, use =IF(COUNTIF('Sheet1'!$A$2:$A$100,A2)>0,"Found","Not found") in the same layout.
COUNTIF and MATCH are not case-sensitive in these common exact-match uses: “JORDAN LEE” and “Jordan Lee” are treated as the same text. COUNTIF also treats the asterisk and question mark as wildcard characters in criteria, so unusual names that literally contain those characters need extra care. For ordinary personal-name lists, the main practical concerns are usually spacing, spelling, and duplicate entries.
Highlight matches with a helper column
A helper column makes the comparison visible without changing the original name cells. First create a Found/Not found result with MATCH or COUNTIF. Then you can use conditional formatting to color the helper results. This is more dependable than trying to point a conditional-formatting rule directly at a range on a different worksheet, which Excel does not generally allow in a simple cross-sheet rule.
- On Sheet2, add the match-status formula in B2 and fill it down beside the names.
- Select the status cells, such as B2:B100, and go to Home > Conditional Formatting > Highlight Cells Rules.
- Choose Text that Contains, enter Found, select a format, and confirm. If you want different colors, add a separate rule for Not found.
When setting up a rule for “Found,” make sure the rule does not unintentionally color “Not found” as well. One safer design is to use a distinctive status such as “Match” and “No match,” or to apply a formula-based rule to the helper column. The helper values remain useful even if you later remove the formatting, and they can be filtered to show only the names requiring attention.
Return details for a matching name
Sometimes confirming a match is only the first step. If Sheet1 contains names in column A and email addresses in column B, XLOOKUP can return the email associated with each name being checked. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; older Excel versions may not include it.
Assume Sheet2 has names in A2:A100, while Sheet1 has names in A2:A100 and email addresses in B2:B100. Enter =XLOOKUP(A2,'Sheet1'!$A$2:$A$100,'Sheet1'!$B$2:$B$100,"Not found",0) in Sheet2 B2 and fill down. If the name is present, the formula returns the corresponding email address; otherwise it returns “Not found.” The final 0 requests exact matching.
Names are not always unique identifiers. Two people can share a name, and one person may appear with a changed surname or different spelling. If the workbook has a stable employee number, customer ID, or other unique key, comparing that field is usually safer than comparing names alone. If XLOOKUP encounters duplicate names, it returns the result for the first matching entry, so it does not by itself resolve ambiguous records.
Fix spaces and other mismatches
A name can look identical on screen but fail to match because its text contains an extra space or an invisible character. Leading spaces, trailing spaces, and nonbreaking spaces copied from websites or exported systems are common causes. Before changing formulas, compare the source values and look for inconsistencies in spelling, punctuation, middle initials, accents, and suffixes such as Jr.
For a quick cleanup, use a separate helper column rather than overwriting the original data. With the uncleaned name in A2, enter =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) in B2 and fill down. SUBSTITUTE changes nonbreaking spaces (character 160) into ordinary spaces, and TRIM removes leading and trailing ordinary spaces while reducing repeated internal spaces to single spaces. Apply a similar cleanup to both lists, then compare the cleaned columns. Keep the original columns if you need to preserve the imported data for reference.
TRIM does not correct misspellings, standardize different name formats, or remove every possible invisible character. Avoid automatically deleting punctuation or treating similar-looking names as identical unless the organization has defined a consistent rule. For example, “Anne-Marie” and “Anne Marie” may or may not refer to the same record; a formula cannot determine that safely without a business rule or another identifying field.
Understand common formula errors
If every result says Not found, first check that the formula points to the right worksheet and range. Confirm that the names are within the referenced rows, that the sheet name is spelled correctly, and that the formula is looking at the intended column. When a sheet name contains spaces or punctuation, use single quotation marks around it. Also confirm that the compared cells contain text in the same form and do not have hidden spaces.
If a formula shows an error rather than Found or Not found, check for a misspelled function, unbalanced parentheses, or a reference to a deleted sheet. In Excel settings that use a regional list separator, formulas may require semicolons instead of commas; the formulas here use commas as specified for standard English-language Excel syntax. If formula text appears in the cell instead of a result, the cell may be formatted as Text or the entry may have a leading apostrophe. Change the format to General and re-enter the formula.
Another common misconception is that Find searches one worksheet against another automatically. Ctrl+F opens Find in Excel on Windows, and Command+F does so on Mac, but it locates text occurrences; it does not produce a row-by-row comparison. It can help inspect a known name, but use a formula when you need a result for every entry in a list.
Pick the right approach for your workbook
For a short comparison that you may need to repeat, MATCH gives a clear yes-or-no result and COUNTIF adds a useful duplicate count. XLOOKUP is a good choice when a match should bring back a related value. A helper column with conditional formatting is useful for reviewing exceptions with a colleague, while Power Query can be more suitable when the lists are large or regularly imported.
Whichever method you choose, make the comparison direction explicit. If Sheet2 is the current list and Sheet1 is the master list, a formula beside Sheet2 answers, “Which current names are in the master list?” It does not answer, “Which master names are missing from the current list?” For that second question, check Sheet1 against Sheet2 as well. Filtering the status column to Not found then creates a practical review list.
For recurring work, convert each data range into an Excel table using Insert > Table. Tables expand when rows are added and can make formulas easier to read, although the sheet names and structured references need to be set up consistently. If you keep ordinary cell ranges, extend the fixed range when new names are added; a formula limited to row 100 will not search names entered in row 101.
Frequently Asked Questions
Can I compare names on two worksheets in the same workbook?
Yes. Use a formula on one worksheet that references the name range on the other, such as MATCH or COUNTIF. Include the worksheet name before the cell range, and put the sheet name in single quotation marks if it contains spaces.
How do I find names that are on one sheet but not the other?
Check each list against the other with a Found/Not found formula, then filter the status column to Not found. One direction identifies names missing from the reference list; checking the reverse direction identifies names missing from the first list.
Does Excel treat uppercase and lowercase names as different?
No. MATCH and COUNTIF in the examples are case-insensitive, so “Lee” and “LEE” match. If capitalization itself must distinguish records, ordinary MATCH or COUNTIF is not enough; use an explicitly case-sensitive comparison method or, preferably, a unique ID.
Why does Excel say a name is not found when it looks the same?
The cells may contain extra spaces, nonbreaking spaces, different punctuation, or a spelling variation. Clean both lists consistently and check the formula’s range. Visual appearance alone does not guarantee that the underlying text is identical.
Can I find a name in a different Excel file?
Yes. A formula can reference a range in another workbook, but the external reference must point to the correct file and worksheet. For a one-time check, copying the lists into one workbook may be simpler; for a recurring process, consider a controlled linked-workbook or Power Query workflow.
Related
- Excel File Too Large? How to Make It Smaller in Excel
- Excel File Too Large to Email? Practical Fixes
- Excel File Too Big? How to Reduce Workbook File Size Safely
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets VLOOKUP: Formula, Examples, and FixesIn Google Sheets, VLOOKUP searches for a value in the first column of a range and returns a…
- Excel vs CSV: Key Differences and When to Use EachExcel is spreadsheet software and a workbook format; CSV is a plain-text format for rows and columns. Choose…
- Excel Goal Seek Shortcut: Open It, Set a Target, SolveIn Excel for Microsoft 365 on Windows, open Goal Seek by pressing Alt, then A, W, and G…
- Creating a Flowchart in Word: Steps and Practical TipsTo create a flowchart in Microsoft Word for Microsoft 365 on Windows, choose Insert > Shapes, add shapes…
- Unable to Block a Sender in Outlook? Try These FixesIf you can’t block a sender in Outlook, add the address to your account’s blocked-senders list in Settings;…
- Multiply Hours by Money in Excel: A Practical GuideTo calculate pay from decimal hours, put the hours in B2 and the hourly rate in C2, then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.