Excel VLOOKUP Name Errors: Causes, Fixes, and Real Examples

#NAME? in an Excel VLOOKUP means Excel cannot recognize a name or text token in the formula; common causes include a misspelled function, unquoted text, or an invalid named range. Fix the unrecognized part of the formula; if the lookup value simply is not in the table, Excel normally returns #N/A instead.

What #NAME? Means in a VLOOKUP

Excel displays #NAME? when it encounters text in a formula that it interprets as a name but cannot identify. In a VLOOKUP formula, that can be the function name itself, a named range used for the table, or a text lookup value that was entered without quotation marks. Excel is not saying that the value is missing from the table; it is saying that part of the formula does not make sense as written.

VLOOKUP has four arguments: the value to find, the table to search, the number of the column to return, and whether to use an exact or approximate match. For example, with product IDs in A2:A100, product details in A2:C100, and the ID to find in E2, the formula below searches the first column of the table and returns its third column using an exact match.

With the lookup ID in E2 and the lookup table in A2:C100, enter:

=VLOOKUP(E2,$A$2:$C$100,3,FALSE)

The formula is valid in Excel for Microsoft 365 on Windows. If your regional settings use semicolons as formula separators, Excel may require semicolons instead of commas. That separator difference is a regional setting, not a different VLOOKUP function.

How to Tell It Apart from #N/A and Other Errors

These error values point to different problems, so identifying the exact one is the quickest way to choose a fix. #NAME? is about something Excel cannot recognize in the formula. #N/A usually means an exact-match lookup did not find the requested value. A formula can be perfectly valid and still return #N/A because the key is absent or differs in a way that matters, such as an extra space.

#NAME? Usually indicates a misspelled function, unrecognized name, or text that needs quotation marks.
#N/A Usually means the requested value was not found, or that the lookup value and table values do not match exactly.
#REF! Indicates an invalid cell or column reference. In VLOOKUP, a column number larger than the table’s width can cause this error.
#VALUE! Can point to an invalid argument, such as a column index that is not a usable number.

A frequent misconception is that every failed VLOOKUP should be fixed by changing the formula. If the formula returns #N/A, first check the lookup value and source data; rewriting a correct formula will not create a missing match. Conversely, replacing #NAME? with an error-handling function can hide a formula typo instead of correcting it.

Common Causes of a VLOOKUP #NAME? Error

Start by checking the parts of the formula that are words rather than cell references or numbers. The most common issues are straightforward to correct:

Misspelled function name: A name such as VLOKUP is not the VLOOKUP function. Excel may show #NAME? because it does not recognize the function call. Check the spelling, including the initial V and the final UP.
Text entered without quotation marks: If a lookup key is typed directly into a formula, Excel needs straight double quotation marks around it. Without them, Excel may interpret the text as a defined name. When the name does not exist, the result can be #NAME?.
A misspelled or missing named range: If the formula uses a name such as Products for its table, that name must exist in the workbook and be spelled correctly. A renamed or deleted range can leave a formula referring to a name Excel no longer recognizes.
Incorrectly written name: Defined names cannot contain spaces and have other naming rules. For example, a name containing a space must not be typed as though it were one unquoted identifier. Check the exact name in Formulas > Name Manager.
Smart quotes or copied characters: Text copied from a webpage or document may contain typographic quotation marks instead of straight formula quotes. If a formula with a text value is not accepted, retype the quotation marks directly in Excel.

VLOOKUP itself is available in older Excel releases as well as Microsoft 365, so a #NAME? result in a normal VLOOKUP formula is not usually an indication that the function is too new for your Excel version. A typo or an invalid name is a more likely explanation.

Steps to Find and Fix the Cause

Use the formula bar to inspect the actual formula, especially if it was copied from another cell or pasted from an outside source. Then work through these checks in order:

Read the error carefully. Confirm that the cell shows #NAME?, rather than #N/A or another error. The distinction narrows down what to investigate.
Check the function spelling. Confirm that the formula starts with VLOOKUP and that the function name has not been altered while editing.
Inspect direct text values. If the lookup key is typed into the formula rather than referenced from a cell, put it in straight double quotation marks. Do not add quotation marks around a cell reference such as E2.
Verify named ranges. If the table argument is a word rather than a cell range, open Formulas > Name Manager and check that the name exists, is spelled the same way, and refers to the intended cells. Also check whether its scope is limited to a particular worksheet.
Review each argument. The table must include the lookup column and the column to return. The column index must be a number counted from the left edge of the table, not the worksheet’s column letter.
Retest with a cell reference. If a formula contains a manually typed text key, put that key in a worksheet cell and refer to the cell. This removes ambiguity about whether the text is quoted correctly.

For an ordinary range-based lookup, with the lookup key in E2 and the table in A2:C100, this version uses a cell reference for the key and an exact match:

=VLOOKUP(E2,$A$2:$C$100,3,FALSE)

If the lookup key is typed directly into the formula instead, the following example searches for the text product ID P-104. The table is still A2:C4, with IDs in its first column and the value to return in its third:

=VLOOKUP("P-104",$A$2:$C$4,3,FALSE)

The quotation marks are needed because P-104 is text. In contrast, FALSE is a logical value and should not be placed in quotation marks. If the value is stored in a cell, referencing that cell is generally easier to maintain than typing the key into the formula.

A Worked Example with a Named Range

Suppose a small price list has product IDs in column A, descriptions in column B, and prices in column C. The rows contain P-101, Widget, 12.00; P-104, Cable, 18.50; and P-109, Adapter, 7.25. If E2 contains P-104, a VLOOKUP using the entire A2:C4 table and a return-column index of 3 should return 18.50.

With the IDs and product details in A2:C4 and the requested ID in E2, use:

=VLOOKUP(E2,$A$2:$C$4,3,FALSE)

To make the formula easier to read, you can select A2:C4 and create a defined name such as Products using Formulas > Define Name. If the range is named Products, and E2 still contains P-104, use:

=VLOOKUP(E2,Products,3,FALSE)

This named-range version works only if Products exists and refers to the intended range. If someone deletes or renames the range, the formula’s reference may no longer be valid. Check Name Manager before replacing a name with a guessed cell range, particularly in a workbook where the table may move or expand.

When debugging, temporarily using an explicit cell range can help determine whether the problem lies in the named range or elsewhere in the formula. Once the range reference works, you can decide whether to restore the name. Keep the dollar signs in a range that will be copied to other rows: they lock the table boundaries while the lookup reference can adjust.

What a Correct Formula Can Still Get Wrong

Fixing #NAME? does not guarantee that VLOOKUP will return the intended result. VLOOKUP searches only the leftmost column of its table and returns a value from a column to its right. If the item to find is in column B but the selected table starts at column A, the formula searches column A, not B. Reorder the source data or choose a lookup method that can search in the required direction.

The column index is counted inside the selected table. In A2:C100, column A is 1, B is 2, and C is 3. If the table starts at B instead, B becomes index 1. The index must also be no greater than the number of columns in the selected table. A bad index may produce #REF! rather than #NAME?, but checking it prevents a second error after the name issue is fixed.

For most everyday lookups, specify FALSE as the fourth argument to request an exact match. If that argument is omitted, VLOOKUP uses approximate matching by default, which generally requires the first table column to be sorted in ascending order and can return an unexpected result when it is not. Exact matching does not require sorting.

If a corrected formula returns #N/A, compare the lookup value with the source value. Look for leading or trailing spaces, different punctuation, inconsistent text-versus-number storage, and characters that look alike but are not identical. A product ID stored as text may not behave like a numeric value. These are data-matching issues, not name errors, and they should be diagnosed separately.

When to Use IFERROR—and When Not To

IFERROR can replace an error result with a message, which may make a report easier to read. It should be added after the lookup works, not used as the first response to #NAME?. If the formula still contains a misspelled function or invalid range name, an error handler can conceal that underlying problem from someone viewing the worksheet.

With the lookup ID in E2 and the lookup table in A2:C100, this formula displays Not found for any error produced by the lookup:

=IFERROR(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not found")

That result is useful only if treating all errors alike is appropriate. IFERROR catches more than #N/A, including a #NAME? caused by a typo, so a formula mistake could appear as Not found. For a worksheet where the difference matters, fix and verify the lookup first, then decide how missing values should be presented.

Frequently Asked Questions

Why does my VLOOKUP say #NAME?
Excel cannot recognize part of the formula. Check the spelling of VLOOKUP, any named range used for the table, and whether a text value typed directly into the formula has straight double quotation marks.

Is #NAME? the same as #N/A in VLOOKUP?
No. #NAME? points to an unrecognized name or text token in the formula. #N/A usually means an exact-match lookup could not find the requested value in the first column of the table.

Do I put quotation marks around FALSE in VLOOKUP?
No. FALSE is a logical value, so enter it without quotation marks. Use quotation marks for a text value typed directly into the formula, such as a product ID; do not quote a cell reference.

Why does VLOOKUP return #NAME? when I use a table name?
The name may be misspelled, deleted, or scoped to another worksheet. Open Formulas > Name Manager to confirm that the name exists and refers to the intended range.

Can a missing lookup value cause #NAME?
Not by itself. With a valid exact-match VLOOKUP formula, a value that is absent from the lookup column normally produces #N/A. Check for #NAME? separately by inspecting the formula’s names and text arguments.

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.