Name Error in VLOOKUP: Causes and Fixes

In Excel, a VLOOKUP name error is usually #NAME?: Excel cannot recognize the function name or another name used in the formula. Check the spelling of VLOOKUP and any named ranges or text values in the formula; if the lookup value simply is not in the table, the usual result is #N/A, not #NAME?.

What a VLOOKUP name error means

#NAME? means Excel has encountered text in a formula that it cannot interpret as a valid function, defined name, or other recognized formula element. The error is not a message that the item you searched for is missing. It points instead to how Excel is reading the formula itself.

VLOOKUP is a long-standing Excel function, so a spelling mistake is a common explanation. For example, leaving out a letter or changing the order of letters means Excel may no longer identify the function. A formula can also contain a name that Excel does not know, such as an undefined named range, or a text value entered without quotation marks. Each of these can lead to #NAME?, even if the lookup table and intended result are otherwise sensible.

It helps to separate two questions: can Excel understand the formula, and can it find the requested value? #NAME? points to the first question. Once Excel recognizes the formula, an unsuccessful exact lookup generally produces #N/A. Fixing a name error therefore starts with inspecting the formula’s words and references, not changing the lookup value or sorting the table.

How VLOOKUP is structured

VLOOKUP searches for a value in the first column of a table and returns a value from another column in the same row. Its arguments tell Excel what to find, where to search, which column to return, and whether to use an exact or approximate match. A correct function name alone is not enough: the other arguments must also make sense for the layout.

For example, suppose product IDs are in A2:A4, their prices are in B2:B4, and D2 contains the ID P-104. With those cell locations and values, this exact-match formula returns 15.50:

With product IDs in A2:A4, prices in B2:B4, and P-104 in D2, use:

=VLOOKUP(D2,$A$2:$B$4,2,FALSE)

The lookup range begins in column A, where the product IDs appear. The number 2 tells Excel to return a value from the second column of that range, column B. FALSE requests an exact match. The dollar signs keep the table range fixed if the formula is copied to other rows. In Excel, the function name is not case-sensitive, so vlookup and VLOOKUP are equivalent; the letters themselves must still be correct.

Common causes of #NAME? in VLOOKUP

Misspelled function name: Check that the function is entered as VLOOKUP. A missing, extra, or transposed letter can make Excel treat the text as an unknown name.
Misspelled or missing named range: A formula may use a label such as ProductTable instead of a cell address. That label must exist as a defined name in the workbook, and it must be spelled consistently.
Text entered without quotation marks: If you type a text search value directly into a formula rather than referring to a cell, Excel needs to recognize it as text. Without quotation marks, Excel may interpret the text as a name and return #NAME?.
Unrecognized function in an older Excel version: This is less likely with VLOOKUP, which is supported in older Excel editions as well as Microsoft 365. It is more relevant when someone replaces it with a newer function that their Excel version does not support.
Broken or altered formula text: A copied formula may contain a changed function name or a reference to a defined name that was not included in the workbook. Inspect the formula in the formula bar rather than relying only on what the cell displays.

A misspelled sheet name normally has its own reference-related behavior when entered as a worksheet reference; do not assume that every mistake in a formula will produce #NAME?. Excel’s error depends on what part of the expression it cannot interpret. Read the formula from left to right and check the function, any names, and any directly entered text.

How to find and correct the problem

Start by selecting the cell with the error and reading the formula bar. Check the first function name, then look for words that are intended to identify a table, range, or text value. If the formula is long, use the Formula Bar to inspect it carefully, or click within the formula to see which ranges Excel highlights.

Confirm the function spelling. Replace an incorrect function name with VLOOKUP, keeping the opening parenthesis and the existing arguments.
Check each name in the formula. If a range is referred to by a custom label, make sure the label exists and is spelled exactly the same way. In Excel for Microsoft 365 on Windows, open Formulas > Name Manager to review workbook names and their references.
Check directly entered text. If the lookup value is typed into the formula as text, make sure Excel can distinguish that text from a name. A cell reference, such as D2 in the example, avoids that particular ambiguity.
Recheck the table and column number. Make sure the table starts with the lookup column and that the requested return column is included. These issues more often produce other errors or an unintended result than #NAME?, but they are worth checking after Excel recognizes the formula.
Recalculate and test. Press Enter after correcting the formula. Test it with a lookup value that you can see in the first column of the selected table, so you can distinguish a formula-recognition issue from a missing match.

When editing a formula copied from another file, also check whether its intended named ranges came with the workbook. In Name Manager, confirm that the name points to the expected cells and is available in the scope where the formula uses it. A name can be defined for a particular worksheet rather than the whole workbook, which can matter when a formula is moved between sheets.

Distinguishing #NAME? from other VLOOKUP errors

Different error values narrow down the diagnosis. #NAME? means Excel does not recognize an element of the formula. #N/A usually means an exact-match lookup did not find the requested value in the first column of the table. For #N/A, check for absent records, extra spaces, or a mismatch between text and numeric values; correcting the function spelling will not create a missing record.

#REF! commonly indicates an invalid cell reference, such as a return-column number that reaches beyond the selected table. #VALUE! can arise when an argument has an unsuitable value, including a column index that is less than one. A result that is a valid value but seems wrong can instead reflect an incorrect return-column number, the wrong lookup range, or approximate-match behavior. These are not the same as a name error.

For reliable exact matching, include FALSE as the last argument. If the final argument is omitted, VLOOKUP defaults to approximate matching. Approximate matching has specific requirements and can return an unexpected result when the first column is not sorted as required. That behavior is a formula-design issue, not a #NAME? problem. Being precise about the error type prevents time-consuming fixes to the wrong part of the workbook.

When to keep VLOOKUP or use another function

VLOOKUP remains useful when the value to search for is in the leftmost column of a table and the desired result is to its right. It is familiar, widely supported, and often adequate for simple product lists, employee directories, and reference tables. Correcting a misspelled function name is generally simpler than replacing a formula that otherwise fits the task.

One structural limitation is that VLOOKUP returns values from columns to the right of the lookup column. Its return-column argument is a number counted from the left edge of the table range, so inserting or rearranging columns can make a formula return the wrong field unless the formula is reviewed. XLOOKUP, available in Microsoft 365 and Excel 2021, can search in one range and return a value from a separate range, including a range to the left. It also offers a built-in option for specifying what to show when no match is found.

Using XLOOKUP is not a direct fix for #NAME? if the actual problem is an undefined range name or a misspelled reference. Diagnose the cause first. Also consider compatibility before sharing a workbook: people using older Excel versions may not have XLOOKUP, whereas VLOOKUP is supported much more broadly. If you keep VLOOKUP, use clear table references and exact match where the task calls for one.

Ways to prevent the error in future formulas

Use cell references for changing lookup values instead of typing different text into each formula. This keeps the formula consistent and makes it easier to audit. When you use a named range, choose a clear name, define it once, and check it in Name Manager if a formula stops working after a workbook is edited or copied.

Build the formula in stages: first confirm that Excel accepts the function and references, then verify the return-column number, and finally test the match behavior. For a batch of formulas, fill down only after checking the first result. Locking the table range with dollar signs helps prevent it from shifting when copied, but it does not correct a misspelled function or a missing name.

When you inherit a workbook, avoid replacing an error with a blank result before finding its cause. Hiding errors can make a report look tidy while concealing missing data or a broken reference. First decide whether the issue is formula recognition, an absent lookup value, or an incorrect table design. Then choose whether an error-handling formula is appropriate for the workbook’s purpose.

Frequently Asked Questions

Why does VLOOKUP show #NAME? instead of #N/A?

#NAME? means Excel cannot recognize something written in the formula, commonly a misspelled function or undefined name. #N/A generally means a valid exact-match lookup could not find the requested value. Check the formula’s spelling and names first; if it is recognized, then check whether the lookup item exists in the first table column.

Can lowercase vlookup cause a name error?

No. Excel function names are not case-sensitive, so vlookup and VLOOKUP mean the same function. A spelling error, missing function text, unsupported function in a different formula, or unknown name elsewhere in the expression can still cause #NAME?.

Does a misspelled named range cause #NAME?

It can. If a formula refers to a custom name, Excel needs that name to be defined and spelled correctly. In Excel for Microsoft 365 on Windows, check the workbook’s names under Formulas > Name Manager, and confirm that the name points to the intended cells.

What if VLOOKUP returns #N/A after I fix #NAME?

That means Excel now recognizes the formula, but an exact lookup has not found a matching value. Confirm that the value appears in the first column of the lookup range and check for differences such as leading or trailing spaces, inconsistent number-versus-text formatting, or a lookup range that excludes the record.

Should I replace VLOOKUP with XLOOKUP?

Not necessarily. VLOOKUP is suitable when the search column is at the left of the table and the return column is to its right. XLOOKUP can be more flexible and is available in Microsoft 365 and Excel 2021, but workbook compatibility matters. Neither function automatically fixes a misspelled or undefined name elsewhere in a formula.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.