Find a Name in Excel with a Formula

Formulas & functions · Updated November 20, 2026

To check whether a name appears in an Excel list, use COUNTIF; to return information associated with a name, use XLOOKUP. For example, with names in A2:A100 and the name to check in D2, use =IF(COUNTIF(A2:A100,D2)>0,"Found","Not found"). This checks for an exact, case-insensitive match. If you mean finding a name inside longer text, use a partial-match formula instead.

What “find a name” can mean in Excel

The right method depends on what you want Excel to find. A name might be a value in a list, part of a longer cell entry, or a label associated with other data. You might also mean a defined name—an identifier such as TaxRate that appears inside a formula. These are different tasks, and using the wrong kind of search can produce misleading results.

  • Check whether a name is on a list: use COUNTIF for an exact match.
  • Look for a name within text: use a wildcard with COUNTIF or use SEARCH.
  • Return information from another column: use XLOOKUP.
  • Locate a cell manually: use Excel’s Find command.
  • Inspect a defined name: use Name Manager.

The examples below assume Microsoft Excel for Microsoft 365 on Windows. The core formulas also work in many earlier Excel releases, although newer functions such as XLOOKUP and FILTER require Microsoft 365 or Excel 2021 or later.

Check a list for an exact name

Use COUNTIF when the name should match a cell’s full contents. With names in A2:A100 and the name to check in D2, the formula is =IF(COUNTIF(A2:A100,D2)>0,"Found","Not found"). If D2 contains Carla Nguyen and that name occurs once in the list, COUNTIF returns 1 and the formula displays Found. If it is absent, the result is Not found.

COUNTIF is not case-sensitive, so Carla Nguyen and CARLA NGUYEN count as the same text for this purpose. It does not require the list to be sorted, and it can check a range on another worksheet if the formula uses a valid sheet reference. For example, a range on a sheet named Staff can be written as Staff!A2:A100.

If you only need a TRUE or FALSE result rather than a message, with names in A2:A100 and the search value in D2 use =COUNTIF(A2:A100,D2)>0. That can be useful as part of a larger formula or a conditional-formatting rule. If the list may contain duplicates and you need to know how many, use =COUNTIF(A2:A100,D2) with the same cell layout; it returns the number of matching cells, not just whether at least one exists.

Find a name inside longer text

An exact-match check will not find Carla in a cell containing Carla Nguyen. To check whether a name or fragment appears anywhere within a cell, with text in A2:A100 and the search term in D2, use =IF(COUNTIF(A2:A100,"*"&D2&"*")>0,"Found","Not found"). If D2 contains Carla and one list entry is Carla Nguyen, the result is Found. The asterisks are wildcards meaning any number of characters before or after the search term.

Wildcards are convenient, but partial matches can be too broad. Searching for Ann can match Ann Smith, but can also match a longer string such as Joanne. A partial match means the characters occur somewhere in the cell; it does not necessarily mean the search term is a separate word or a complete name. For a single cell, with its text in A2 and the search term in D2, =ISNUMBER(SEARCH(D2,A2)) returns TRUE when SEARCH finds the text and FALSE when it does not.

SEARCH ignores letter case. FIND is its case-sensitive counterpart: with the same layout, =ISNUMBER(FIND(D2,A2)) returns TRUE only when the capitalization in D2 matches the text in A2. Both functions return a position when they find a match; ISNUMBER turns that result into a simple TRUE/FALSE answer.

Be careful if D2 is blank. A blank search value can make a wildcard search match cells unexpectedly, because the surrounding asterisks can match text. If users might leave the search cell empty, guard against it. With names in A2:A100 and the search term in D2, use =IF(D2="","Enter a name",IF(COUNTIF(A2:A100,"*"&D2&"*")>0,"Found","Not found")).

Return information for the matching name

If the name is a key for other details, a lookup is usually more useful than a Found/Not found message. Suppose names are in A2:A6, departments are in B2:B6, and D2 contains the name to look up. Use =XLOOKUP(D2,A2:A6,B2:B6,"Not found",0). If Carla Nguyen is in A4 and Design is in B4, the formula returns Design. The final 0 asks for an exact match; XLOOKUP’s default is also exact, but specifying it makes the intention clear.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. In Excel 2019 and earlier, a common alternative is VLOOKUP. With the same layout, provided the lookup names are in the first column of the range, use =IFERROR(VLOOKUP(D2,A2:B6,2,FALSE),"Not found"). FALSE requests an exact match, and IFERROR replaces the lookup error with a readable message. VLOOKUP searches only from left to right within its selected range, whereas XLOOKUP can return a value from a column on either side of the lookup range.

Show every partial match

A lookup normally returns one result. If you want a list of every entry containing a search term, FILTER can return multiple matching rows. With names in A2:A100 and the search term in D2, use =FILTER(A2:A100,ISNUMBER(SEARCH(D2,A2:A100)),"No match"). If D2 contains Lee, the results spill into cells below the formula and include entries such as Jordan Lee and Lee Chen. SEARCH makes this a case-insensitive partial match.

FILTER is available in Microsoft 365 and Excel 2021 or later. Leave enough empty cells below the formula for its results to spill; otherwise Excel may show a #SPILL! error. If the search term might be blank, add a check so an empty input does not return the entire list. With the same layout, use =IF(D2="","Enter a name",FILTER(A2:A100,ISNUMBER(SEARCH(D2,A2:A100)),"No match")).

Use Find when you want to locate a cell

If you want to jump to a cell rather than create a formula result, use Excel’s Find command. On Windows, press Ctrl+F or select Home > Find & Select > Find. On a Mac, press Command+F. Enter the name and choose Find Next to move through matches, or Find All to list matching cells in the current search scope.

Find is useful for a quick, one-time search, while a formula updates as worksheet values change. Check the Find dialog’s options if a search misses something: it can search within formulas, values, or comments, and it can search the current sheet or the entire workbook. A search set to look within formulas may find text in a formula’s contents rather than a displayed result, which is different from searching cell values.

Find a defined name used in a formula

Excel also uses “name” to mean a defined name: a label assigned to a cell or range, such as TaxRate for cell B2. A formula like =A1*TaxRate refers to that label rather than spelling out the address B2. To review defined names in Microsoft 365 for Windows, select Formulas > Name Manager. The dialog shows names, their scope, and what each one refers to. On Mac, use Formulas > Name Manager as well.

To inspect which formula uses a defined name, select the relevant cells and look at the formula bar, or use Find with the name as the search text and set the search scope to formulas. A name can be scoped to the whole workbook or just one worksheet, so two names that look alike may refer to different cells depending on where they are used. This is distinct from searching a list of people’s names with COUNTIF.

Common problems and how to avoid them

  • Extra spaces: “Carla Nguyen” and “Carla Nguyen ” may not behave as expected in exact comparisons. Check for leading or trailing spaces, or clean the source data before relying on a lookup.
  • Duplicates: COUNTIF can confirm a match but does not identify which duplicate is the intended record. Decide whether duplicates are valid, and use an additional key such as an employee ID when names are not unique.
  • Partial matches: Wildcards and SEARCH can return a match inside a different word or longer name. Use exact matching when the full cell value matters.
  • Wrong range: Make sure the lookup range covers all current rows. A fixed range such as A2:A100 will not include a new name entered in A101.
  • Version differences: If Excel does not recognize XLOOKUP or FILTER, use a compatible alternative such as VLOOKUP for a single return value, or use Find for a manual search.

For a list that grows regularly, an Excel table can make ranges easier to maintain: select the data and choose Insert > Table. Structured references adjust as table rows are added, though the formulas above use ordinary cell ranges to make their assumptions explicit. Whichever method you choose, check a known matching example and a known non-matching example before depending on the result.

Frequently Asked Questions

How do I check whether a name exists in an Excel column? With names in A2:A100 and the value to check in D2, use =COUNTIF(A2:A100,D2)>0. TRUE means at least one exact match exists; FALSE means none was found.

How do I find part of a name in Excel? With a list in A2:A100 and a search term in D2, use =COUNTIF(A2:A100,"*"&D2&"*")>0. This is a case-insensitive partial match, so it may find the letters within a longer name or word.

Can Excel find a name and return a value from another column? Yes. Use XLOOKUP with the name column as the lookup range and the details column as the return range. For example, =XLOOKUP(D2,A2:A6,B2:B6,"Not found",0) returns the corresponding value from column B.

Why does Excel say a name was not found when it looks identical? Check for extra spaces, hidden characters, or a mismatch between exact and partial matching. Also confirm that the formula’s range includes the cell and that the search value is in the cell the formula references.

Is Excel Find the same as a formula? No. Find is an interactive command that locates cells in a worksheet or workbook. A formula calculates a result in a cell and can update when the underlying data changes.

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.