To use a named range with VLOOKUP in Excel for Microsoft 365 on Windows, give the lookup table a name, then use that name as VLOOKUP’s second argument. With product IDs in A2:A5, product names in B2:B5, prices in C2:C5, the lookup ID in E2, and ProductTable defined as A2:C5, enter =VLOOKUP(E2,ProductTable,2,FALSE) to return the matching product name. For example, if E2 contains 102 and row 3 contains ID 102 and Pen, the result is Pen.
What a named range does
A named range is a label assigned to a cell or range, so you can refer to it by name instead of typing its address. In this example, ProductTable stands for the three-column range A2:C5. The name does not change how VLOOKUP searches; it simply makes the formula easier to read and maintain than a reference such as A2:C5.
This can be especially useful in a workbook with several lookup formulas, or when the lookup table is on another worksheet. Instead of needing to remember where a range begins and ends, you can use a clear name such as ProductTable, EmployeeList, or RateTable. A name can also make it easier to spot the purpose of a formula when someone else reviews the workbook.
A named range is not automatically a database or a special lookup feature. It is a reference to cells. VLOOKUP still requires the value being searched for to be in the leftmost column of the referenced range, and it still returns a value from a column to the right.
Create and name the lookup range
First, arrange the lookup data in columns, with the value to search for at the left. For the example, column A contains product IDs, column B contains names, and column C contains prices. Select the data rows A2:C5, leaving the headers out of the range. Including the headers is not always fatal, but excluding them keeps the table reference focused on the records VLOOKUP should search.
- Select A2:C5.
- On the Windows ribbon, choose Formulas > Define Name.
- Enter ProductTable in the Name box. Keep the scope at Workbook if you want to use the name from any worksheet.
- Check that Refers to points to the intended cells, such as =$A$2:$C$5, and confirm the name.
You can also select the range and type ProductTable into the Name Box at the left of the formula bar, then press Enter. The Name Box is useful for creating or jumping to a named range. On Excel for Mac, the Name Box works too; the Define Name command is available from the Formulas tab, though ribbon placement can vary slightly by release. Excel for the web can use named ranges in formulas, but the available name-management controls may differ from the desktop app.
Names cannot contain spaces, and they cannot look like cell references such as A1. Use an underscore or a word boundary instead, for example Product_Table or ProductTable. Names are not case-sensitive. If you are unsure what names already exist, open Formulas > Name Manager in desktop Excel to review, edit, or delete them.
How VLOOKUP uses the named range
VLOOKUP takes four arguments: the value to find, the table to search, the number of the return column within that table, and whether to require an exact match. The named range replaces the ordinary cell-range reference in the second argument. The column number is counted from the left edge of the named range, not from the worksheet’s column letters.
With IDs, names, and prices in columns A, B, and C of ProductTable, column 1 is the ID, column 2 is the name, and column 3 is the price. Thus, the opening formula uses 2 to return the name. If E2 contains 102 and B3 contains Pen, it returns Pen. For a price lookup using the same layout, change the return-column number to 3; the result for ID 102 is 1.25 if C3 contains 1.25.
The final argument FALSE requests an exact match. This is generally the right choice for identifiers such as product codes, employee IDs, or postal codes, where a near match would be misleading. It also makes the formula’s intention explicit. If the ID is not present, VLOOKUP returns #N/A rather than silently returning a different row.
Exact match, approximate match, and missing values
Do not omit the fourth argument when you intend an exact lookup. If it is omitted, VLOOKUP uses approximate-match behavior. Approximate matching is intended for ordered data such as score bands or tax brackets, and it expects the first column to be sorted in ascending order. With unsorted IDs, an approximate lookup can return an unexpected result instead of an error.
For an exact-match lookup that should show a message rather than #N/A when an ID is absent, wrap VLOOKUP in IFNA. With IDs in A2:A5, names in B2:B5, prices in C2:C5, ProductTable defined as A2:C5, and the ID to find in E2, enter =IFNA(VLOOKUP(E2,ProductTable,2,FALSE),"Not found"). If E2 contains 999 and no row has that ID, the result is Not found; if E2 is 102, it is Pen. IFNA handles the not-found error while allowing other kinds of formula errors to remain visible.
Use IFERROR only if you deliberately want to replace other errors as well. Broadly hiding errors can make a broken range or an invalid column number look like an ordinary missing record. During setup, keeping the original error visible can help diagnose mistakes.
Make the range easier to maintain
A named range created from A2:C5 refers to those cells; it does not necessarily expand just because you type a new record beneath row 5. If you add products regularly, check the Refers to field in Name Manager after extending the data. A formula that uses a name can still return #N/A for new records if the name excludes their rows.
One durable alternative is to convert the source data into an Excel Table. Select the data, including its headers, and choose Insert > Table, confirming that the table has headers. Tables expand as you add rows, and they offer structured references such as a table column name. You can still create a named range for a table’s data if that suits the workbook, but consider whether a table name or structured reference would communicate the layout more clearly.
Named ranges are also convenient when the table is on a different worksheet: the formula can use ProductTable without embedding a sheet name and cell address each time. For a range that should remain fixed, this is often cleaner. If the data moves, however, review the name’s definition in Name Manager; Excel may adjust references during edits, but you should not assume a manually maintained name always covers the intended records.
Common problems and how to fix them
- #NAME? Check spelling and scope. ProductTable and Product_Table are different names. A name defined with worksheet scope may not be available as an unqualified name from another sheet.
- #N/A for a value that appears to exist: Confirm that the name includes the row, that the lookup value is in its leftmost column, and that the values have compatible types. A number stored as text may not match a numeric value even when both display the same digits.
- Wrong column returned: Count columns within the named range. If ProductTable begins at column A, the name is column 2 and price is column 3; these are not the worksheet’s column letters.
- Unexpected approximate result: Make the fourth argument FALSE for an exact match. Do not rely on a sorted first column unless approximate matching is intentional.
- New records are missed: Expand the name or use a source that grows with the data, such as an Excel Table. Check Formulas > Name Manager to see exactly which cells the name refers to.
- Duplicate lookup values: VLOOKUP returns the first matching row from the top. If duplicate IDs are possible, decide whether the data should be corrected or whether a different method is needed to retrieve multiple matches.
When comparing text values, also check for leading or trailing spaces, inconsistent punctuation, and accidental differences in spelling. Excel’s exact match is not a general data-cleaning tool. If a copied ID includes an extra space, the visible text may look identical while the lookup fails.
Named range versus an ordinary range or table
An ordinary reference such as A2:C5 is direct and easy to inspect in a small formula. A named range gives that address a meaningful label, which is helpful when the same source is used in many formulas or when the formula is shared across sheets. The tradeoff is that a reader may need to inspect Name Manager to see what cells the label represents.
An Excel Table is often preferable for lists that grow and have clear column headers. It expands with new rows and supports structured references. A named range can be a good fit for a stable lookup block, a range reused in multiple places, or a workbook where a short, descriptive formula matters. These choices are not mutually exclusive: a workbook can use a Table as its data source and names for particular ranges or values.
VLOOKUP itself has a limitation that naming the range does not remove: it cannot return a value from a column to the left of the lookup column. In Microsoft 365, XLOOKUP is an alternative that can look in one range and return a result from another, including to the left. For a basic rightward lookup, however, VLOOKUP with a clearly defined named range remains a useful, widely understood pattern.
Frequently Asked Questions
Can VLOOKUP use a named range from another worksheet?
Yes. If the name has workbook scope, you can refer to it by name from another worksheet. If it has worksheet scope, it is limited to the sheet where it was defined, so check the scope in Name Manager if Excel does not recognize it.
Should I include the column headings in the named range?
Usually, define the name over the data rows only. VLOOKUP searches the leftmost column of the range, so including a header adds an unnecessary row and can complicate approximate-match behavior. Keep the headers in the worksheet for people, but exclude them from a simple fixed lookup range.
Why does VLOOKUP return #N/A even though the value is there?
Common causes include the named range ending above the matching row, the lookup column not being the range’s first column, text-versus-number mismatches, or extra spaces. Confirm the name’s Refers to address and compare the underlying values, not only their displayed appearance.
Does a named range automatically grow when I add rows?
Not necessarily. A fixed name such as A2:C5 continues to cover those cells unless it is changed. Use Name Manager to inspect or update it, or consider an Excel Table for data that regularly gains records.
What does the number 2 mean in VLOOKUP?
It means return a value from the second column of the named range, counting from its left edge. It does not mean worksheet column B in every case. If the range begins in column D, its second column is worksheet column E.
Related
- Excel Split Multiline Cell Into Rows
- Excel Split Empty Cells Into Multiple Rows?
- Excel Split Cell Text Into Separate Cells: A Practical Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel INDEX and AVERAGE: How to Combine ThemExcel has no single function called INDEX AVERAGE; instead, combine INDEX with AVERAGE so Excel averages the value…
- Average Days Between Dates in Excel: Formulas & TipsIf your dates are listed in chronological order in A2:A10, enter =AVERAGE(A3:A10-A2:A9) to calculate the average number of…
- Capitalize Each Word in Excel: Shortcut and FormulaExcel does not have a single keyboard shortcut that changes selected text to Proper Case. To capitalize the…
- Excel Compare Two Sheets: Tools, Formulas, and TipsTo compare two Excel sheets, use Spreadsheet Compare if it is available in your Microsoft 365 or Office…
- Sequence Number Formula in Excel: Methods That WorkFor a numbered list starting in cell A2, enter =ROW()-1 and fill the formula down; the first result…
- Excel SUMIFS Not Equal: Criteria and FixesTo sum values that do not match a criterion, use the not-equal operator <> in SUMIFS. For example,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.