Excel Named Range Lookup: Find and Use Named Ranges

How-to basics · Updated November 19, 2026

To find a named range in Excel, open Formulas > Name Manager to see its name, scope, and cell reference; you can also choose it from the Name Box or press Ctrl+G and select it in Go To. To use a named range in a lookup, refer to its name in a formula—for example, XLOOKUP can search one named range and return a value from another.

What a named range is

A named range is a label assigned to a cell, a group of cells, or a formula. Instead of writing a cell address such as B2:B20 in a formula, you can refer to a descriptive name such as UnitPrices. Names can make formulas easier to read and help users understand which data a formula uses.

A name does not perform a lookup by itself. It identifies a reference. For example, if ProductNames refers to A2:A20 and UnitPrices refers to B2:B20, a lookup formula can search ProductNames and return the corresponding value from UnitPrices. This is different from searching the workbook for every occurrence of a word or from finding a cell by its displayed text.

How to find a named range

In Excel for Microsoft 365 on Windows, the most complete way to inspect names is Formulas > Name Manager. The dialog lists defined names and, among other details, their values, references, and scope. Select a name to inspect it; use Edit if you need to change its definition. Be careful when editing a name used by existing formulas, because changing its reference can change those formulas’ results.

For a quick jump to a range, use the Name Box, the small box to the left of the formula bar. Open its drop-down list, choose a name, and Excel selects the cells it refers to. You can also type a known name into the Name Box and press Enter. Alternatively, press Ctrl+G to open Go To, select a listed name, and choose OK. On Mac, use Formulas > Name Manager or the Name Box; keyboard shortcuts can differ by keyboard and Excel setup.

If the name does not appear in a list, check that it is defined in this workbook and that you are looking at the right sheet. A sheet-scoped name may be shown with a sheet prefix in some selection lists. The Name Manager is the best place to check whether a name exists and what it refers to.

Create or define a name

To create a simple name, select the cells, click the Name Box, type a valid name, and press Enter. For more control, select the cells and choose Formulas > Define Name. In the New Name dialog, enter the name, choose its scope, and check the Refers to field before confirming. You can also open Name Manager and choose New.

For example, selecting B2:B5 and naming that selection UnitPrices makes UnitPrices refer to those four cells. Excel names cannot contain spaces and cannot be written like a cell address, such as C5. Use an underscore or period where a separator is useful, as in Unit_Prices. Names are not case-sensitive, so UnitPrices and unitprices refer to the same name.

Names can refer to a fixed range or to a formula. A fixed range is straightforward, but it does not automatically grow when you add rows outside its reference. Excel tables are often a better fit for expanding lists because their structured references adjust as table data changes. If you do use a fixed named range, review it when adding data so the lookup includes the new rows.

Use a named range in a lookup formula

Suppose A2:A5 contains product names—Notebook, Pen, Folder, and Ruler—and B2:B5 contains their prices—3.50, 1.25, 2.00, and 0.75. Assume you have named A2:A5 ProductNames and B2:B5 UnitPrices, and D2 contains Folder. With the lookup value in D2, enter this in E2:

With product names in A2:A5, prices in B2:B5, ProductNames referring to A2:A5, UnitPrices referring to B2:B5, and the search term Folder in D2, enter =XLOOKUP(D2,ProductNames,UnitPrices,"Not found") in E2. It returns 2.00.

XLOOKUP searches ProductNames for the value in D2 and returns the value at the matching position in UnitPrices. The two ranges need corresponding rows and should be the same size. The fourth argument supplies a message for a missing match; without it, XLOOKUP returns an error when it cannot find the lookup value. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in some older perpetual editions.

For an older Excel version that does not include XLOOKUP, INDEX and MATCH can do the same exact-match lookup. With ProductNames, UnitPrices, and D2 set up as above, enter =INDEX(UnitPrices,MATCH(D2,ProductNames,0)) in E2. It returns 2.00. The zero tells MATCH to search for an exact match. If there is no match, this formula returns an error; you can handle that separately if your workbook needs a custom message.

Use a name stored as text

Sometimes a cell contains the name of a range rather than a value to search for. INDIRECT can turn that text into a reference, but it is not a general name-search function. For example, if D2 contains the text ItemNames and that defined name refers to four cells, a formula can count those cells as follows:

With ItemNames referring to A2:A5 and the text ItemNames in D2, enter =COUNTA(INDIRECT(D2)) in E2. If all four cells contain entries, the result is 4.

INDIRECT is useful only when the text exactly matches a valid reference or defined name that Excel can resolve in context. It can make formulas harder to audit, and it is a volatile function, meaning Excel recalculates it more often than many ordinary formulas. If the goal is simply to find a product listed in a range, use XLOOKUP or INDEX and MATCH instead.

Workbook scope and sheet scope

A defined name has a scope: either the whole workbook or a particular worksheet. A workbook-scoped name can generally be used from any sheet in that workbook. A sheet-scoped name belongs to one sheet and can be useful when different sheets need separate names with the same wording. The scope is set when the name is created and is visible in Name Manager.

Scope matters when names are duplicated. A local name can take precedence over a workbook-level name when a formula is entered on the sheet where the local name applies. If a formula returns an unexpected range, check the Scope column in Name Manager as well as the Refers to field. Avoid relying on duplicate names unless you have a clear reason and have checked how Excel resolves them.

Names are stored in the workbook, not in a formula’s visible wording alone. Copying a formula to another workbook may leave it referring to a name that is missing there. If Excel asks you to update links or shows a #NAME? error after a copy, inspect the destination workbook’s names and the formula’s references.

Common named-range lookup problems

  • The name is not recognized: Check its spelling in Name Manager. A typo, deleted name, or a name that exists only in another workbook can cause #NAME?.
  • The lookup returns #N/A: The search value may not exactly match an entry. Look for extra spaces, different text, or a number stored as text. XLOOKUP uses exact match by default; INDEX and MATCH needs the final 0 argument for an exact match.
  • The answer comes from the wrong row: Confirm that the lookup and return ranges line up. If the names cover A2:A20 but the prices cover B3:B21, the positions do not correspond.
  • New records are not included: The defined name may refer only to the original cells. Check its Refers to field and extend the range, or consider converting the data to an Excel table.
  • A name works on one sheet but not another: Check whether it is worksheet-scoped rather than workbook-scoped, and look for another name with the same label.
  • The result changes after editing a name: Formulas using that name follow its current reference. Review dependent formulas and the range definition before making changes.

Also distinguish a blank cell from a missing lookup value. A successful match can return a blank from the corresponding result cell, which may look like no match even though Excel found one. Check the source row and the formula’s lookup range before treating every blank result as an error.

Named ranges versus tables and cell references

Named ranges are handy for a stable input area, a frequently reused list, or a formula that reads more clearly with labels than with coordinates. Ordinary cell references are often simpler for a one-off calculation. Tables are usually more convenient for changing datasets because their columns expand with added rows and formulas can use column names such as Table1[Price].

These options are not mutually exclusive. You can use a named range for a fixed lookup list and a table for records that grow over time. Choose based on how the data changes, how many formulas use it, and whether someone maintaining the workbook can easily understand the reference. A descriptive name is helpful only if it points to the intended cells and is kept up to date.

Frequently Asked Questions

How do I see all named ranges in Excel?
Choose Formulas > Name Manager. It lists the workbook’s defined names and lets you inspect each name’s scope and reference.

How do I jump to a named range?
Choose it from the Name Box drop-down, or press Ctrl+G on Windows, select the name in Go To, and choose OK. The selected range is highlighted on the sheet.

Can XLOOKUP use a named range?
Yes. Use the named range as the lookup array or return array, provided the ranges align. XLOOKUP is available in Microsoft 365 and Excel 2021 or later.

Why does my named-range formula show #NAME?
Excel may not recognize the name. Check spelling, confirm that the name exists in the current workbook, and review whether its scope is limited to a different worksheet.

Do named ranges expand automatically?
Not necessarily. A name referring to a fixed cell range usually stays fixed when you add data beyond that range. Check its reference or use an Excel table when the list is expected to grow.

Related

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.