Named Ranges in XLOOKUP: How to Use Them in Excel

To use a named range in XLOOKUP, replace a cell reference such as A2:A6 with the range’s name in the formula. For example, if ProductNames refers to the lookup list and UnitPrices refers to the matching prices, enter =XLOOKUP(E2,ProductNames,UnitPrices,"Not found"). This makes the formula easier to read and maintain. The guide below uses Excel for Microsoft 365 on Windows; XLOOKUP is also available in Excel 2021 and later.

What You’ll Need

An Excel workbook containing a lookup list and a corresponding return list. Both lists should have the same number of rows.
Excel for Microsoft 365 or Excel 2021 or later. XLOOKUP is not available in older perpetual versions such as Excel 2019 or Excel 2016.
A name for each range that follows Excel’s naming rules: start with a letter, underscore, or backslash; do not include spaces; and do not use a name that could be mistaken for a cell reference. For example, ProductNames is valid, but Product Names is not.

You do not need to create a named range before writing XLOOKUP, but you do need to define the name before the formula can use it. The example uses product names in column A, prices in column B, and a product to find in cell E2.

Step-by-Step

Check that your lists line up. Put each lookup value in one column and its corresponding return value on the same row in another column. For this example, enter Tea, Coffee, Cocoa, Green tea, and Lemonade in A2:A6. Enter 2.40, 3.10, 2.75, 2.20, and 1.85 in B2:B6, respectively. Put Tea in E2 as the value to look up. Keep headers outside these ranges so they are not treated as data.
Select the lookup cells. Select A2:A6, the five product names, without selecting the heading in A1. The cells do not have to be adjacent to the return list, but the lookup and return lists must be the same height for this example.
Create a name for the lookup list. In Excel for Windows, click the Name Box to the left of the formula bar, type ProductNames, and press Enter. The Name Box normally shows the active cell address, such as A2. Press Enter after typing the name; otherwise, Excel may not apply it to the selected cells. As an alternative, use Formulas > Define Name, enter ProductNames in the Name field, check that the Refers to field shows the intended cells, and click OK.
Name the return list. Select B2:B6, then use the Name Box to enter UnitPrices and press Enter. You can also create this name with Formulas > Define Name. Check that the name refers to B2:B6, not to the heading or an unrelated column. The two range names should cover corresponding rows in the same order.
Enter XLOOKUP. With product names in A2:A6, prices in B2:B6, names ProductNames and UnitPrices referring to those ranges, and the requested product in E2, enter this formula in F2: =XLOOKUP(E2,ProductNames,UnitPrices,"Not found"). The arguments mean: search for the value in E2 within ProductNames, return the value on the matching position in UnitPrices, and show Not found if there is no match. XLOOKUP uses exact matching by default, so you do not need to add an exact-match setting.
Check the result. Since E2 contains Tea and Tea is the first item in the lookup range, F2 should return 2.40, the value in the corresponding first position of UnitPrices. If you change E2 to Cocoa, F2 should return 2.75. If you type a product that is not in the list, F2 should display Not found rather than an error.
Copy the formula if needed. If you want to look up more products, enter each one in E3, E4, and so on, then copy F2 down. The reference to E2 adjusts to E3, E4, and the following rows, while the named ranges continue to refer to the lists. If your names refer to fixed ranges, adding products below row 6 does not automatically include the new rows; update the named ranges or use a suitably sized source range.
Verify what each name refers to. Open Formulas > Name Manager and inspect ProductNames and UnitPrices. Confirm their scope and the Refers to addresses. You can also use the Name Box’s drop-down list to select a defined name and see which cells it highlights. Save the workbook after confirming that the lookup works as expected.

The formula works because XLOOKUP uses the position of a match in its lookup array to choose a value from the return array. If Tea is the first item in ProductNames, it returns the first item in UnitPrices. A named range does not change how XLOOKUP searches; it gives a readable label to the cells the function already needs.

Common Mistakes

Using a name that has not been defined. Typing ProductNames into a formula does not create the name. Define it first, and check the spelling in Name Manager. Excel ignores capitalization differences in names, but a spelling difference still matters.
Including spaces or invalid characters. Names cannot contain spaces. Use Product_Names or ProductNames instead of Product Names. Avoid punctuation and names that look like cell addresses, such as B2.
Pointing the ranges at different rows. If ProductNames refers to A2:A6 but UnitPrices refers to B2:B5, their sizes do not correspond. XLOOKUP may return an error because its lookup and return arrays have incompatible dimensions. Select matching ranges and verify both definitions in Name Manager.
Including a heading or blank rows unintentionally. If the name includes a header such as Product, XLOOKUP can search that header too. Extra blank cells can also make the defined range larger than expected. Define names over the actual data unless you have a specific reason to include more cells.
Expecting a fixed range to grow automatically. A name referring to A2:A6 continues to refer to those cells if you type a new product in A7. The formula will not find that new entry until you expand the name or use a source that adjusts as data is added.
Using a name with the wrong scope. A name can be workbook-scoped or limited to a particular worksheet. A sheet-scoped name may not be available in the way you expect from a formula on another sheet, especially if a workbook-level name with the same label also exists.
Expecting older Excel to recognize XLOOKUP. A workbook containing XLOOKUP may show #NAME? in versions that do not support the function. The named ranges themselves do not add XLOOKUP support.

Tips

Choose names that describe their role. Names such as CustomerIDs, InvoiceDates, or UnitPrices make the formula’s purpose clearer than names such as Range1. Use a consistent naming style, particularly in workbooks with many lookups.
Keep lookup and return ranges in the same order. XLOOKUP matches a position, not a label in the return list. If you sort one list independently from the other, the formula can return a value belonging to a different item. Sort the full table together or use columns from the same table.
Use the optional not-found message deliberately. The fourth argument in the example gives a helpful result when no match exists. You can use a message such as "Product not found", or omit that argument if you want Excel’s default #N/A result to flag missing data.
Consider a table for lists that change often. Excel tables expand when you add rows directly beneath them, and structured references can make formulas easier to maintain. If you keep fixed named ranges, remember to edit their Refers to entries when the source data grows.
Use the name manager when reviewing a workbook. In Windows Excel, Formulas > Name Manager displays defined names, their scope, and their references. This is useful when a formula looks correct but returns an unexpected result, or when you inherit a workbook with names already in place.
Remember that names can be used on another worksheet. A workbook-scoped name can be referenced from formulas throughout the workbook. If you create a sheet-scoped name, make sure the formula is on the intended worksheet or explicitly use the appropriate sheet-qualified name.

Troubleshooting

The formula displays #NAME? Check that both names exist and are spelled correctly, and that your Excel version supports XLOOKUP. Open Formulas > Name Manager and look for the exact labels. If Excel does not recognize XLOOKUP because the workbook is opened in an older version, use a compatible version of Excel or replace the formula with a lookup method supported by that version.

The formula displays #VALUE! or another array-size error. Compare the Refers to entries for the two names. The lookup range and return range need compatible dimensions: five lookup cells should pair with five return cells. Also check that you have not accidentally defined one name as a horizontal row and the other as a vertical column.

The result says Not found even though the item seems to be present. Check the source and lookup value for leading or trailing spaces, different punctuation, or text-versus-number differences. For example, a number stored as text may not match a numeric value as expected. Compare the actual cell contents, not just their displayed formatting. If entries contain inconsistent spaces, clean the source data or use a helper column to make the values consistent before looking them up.

The result is correct for existing rows but misses newly added records. Inspect the name’s Refers to address. If it ends at row 6, adding information in row 7 will not extend that fixed name. Update the range in Name Manager, or plan the source around an Excel table or a larger range. When expanding a named range manually, expand both the lookup and return names to matching rows.

The formula uses a surprising name from another sheet. In Name Manager, check the Scope column. If there are names with the same spelling at workbook and worksheet scope, Excel may resolve the reference in a way that is not obvious from the formula. Rename one of the names to make it distinct, or correct the scope and references so the formula points to the intended cells.

The formula works on one computer but not another. Confirm that the other computer has Excel 2021 or Microsoft 365 and that the workbook has finished calculating. Some regional settings use semicolons rather than commas as formula separators. The formulas here use commas, as requested for English Excel syntax; if Excel rejects a formula because of local separators, use the separator required by that installation.

Frequently Asked Questions

Can I use a named range for both XLOOKUP arrays?
Yes. One name can refer to the values XLOOKUP searches, and a second name can refer to the values it returns. The two ranges should correspond row by row and have compatible dimensions.

Do I need quotation marks around a named range in XLOOKUP?
No. Enter the defined name directly, such as ProductNames. Quotation marks would make the text a string rather than a reference to the named cells.

Can I use a named range from another worksheet?
Yes, when the name is workbook-scoped. A worksheet-scoped name is limited to its sheet context, so check the Scope column in Name Manager if a cross-sheet formula does not use the range you expect.

Will a named range automatically include new rows?
Not if it refers to a fixed address such as A2:A6. Extend the name as the data grows, or base the lookup on an Excel table or another range design that includes added rows.

Can I use a named range for the lookup value too?
Yes. The first XLOOKUP argument can also be a cell reference or a name that refers to a single cell. For a formula copied down a list of different search values, a relative cell reference such as E2 is usually the practical choice.

Does this work in Excel for Mac or Excel for the web?
XLOOKUP and named ranges are available in supported versions of Excel for Mac and Excel for the web, though the interface may differ slightly. In desktop Excel for Mac, use the Formulas tab to define or manage names; in the web app, use the available name tools and verify the name’s reference before relying on it.

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.