What Occurs If A Name Is Spelled Incorrectly In A Data File?

The #NAME? mistake occurs when Excel can't recognize something. Frequently, the #NAME? occurs when a part proper name is misspelled, only there are other causes, as explained below. Fixing a #Proper noun? mistake is commonly merely a matter of correcting spelling or a syntax.

The examples below show misconfigured formulas that return the #NAME error and the steps needed to fix the error and get a working formula again.

Function name misspelled

In the case below, the VLOOKUP part is used to retrieve an item price in F3. The office name "VLOOKUP" is spelled incorrectly, and the formula returns #NAME?

                                    =VLOKUP(E3,B3:C7,              2              ,              0              )              // returns #Name?

#NAME error example function named misspelled

When the formula is fixed, the formula works properly:

                                    =              VLOOKUP              (E3,B3:C7,              2              ,              0              )              // returns four.25

#NAME error example function named misspelled FIXED

Range entered incorrectly

In the example below, the MAX and MIN functions are used to find minimum and maximum temperatures. the formulas in F2 and F3, respectively, are:

                                    =              MAX              (C3:C7)              // returns 74              =              MIN              (CC:C7)              // returns #NAME?

#NAME error example range entered incorrectly

Beneath the range used in F3 has been fixed:

#NAME error example range entered FIXED

Note: forgetting to include a colon (:) in a range will likewise trigger the #Proper name? error.

Named range misspelled

In the example below, the named range "data" equals C3:C7. In F2, "data" is misspelled "daata" and the MAX role returns #Proper noun?

                                    =              MAX              (daata)              // returns #Name? error

#NAME error named range misspelled

Below, the spelling is corrected and the MAX function correctly returns 325 as the maximum sales number:

                                    =              MAX              (data)              // returns 325

#NAME error named range misspelled FIXED

Notice named ranges are non enclosed by quotes ("") in a formula.

Named range has local telescopic

Named ranges can have local (this worksheet just) or global (all worksheets) scope, and so you lot might see a #Name? fault if you attempt to reference a valid named range that is scoped locally to a different worksheet. You tin utilise the Name Manager (Control + F3, Windows only) to cheque the telescopic named ranges. Read more about named ranges here.

Text value entered without quotes

When a text value is input without double quotes, Excel thinks tries to interpret the value as a function name, or named range. This tin can crusade a #Name? error when no lucifer is found. In the instance beneath, the LEN office is used to become the length of the give-and-take "apple tree". In B3 the formula is entered without the text string "apple" in quotes (""). Considering apple is not a function name or named range, the result is #Proper name?

                                    =              LEN              (apple)              // returns #NAME?

#NAME error text string entered without quotes

Below, quotes have been added and the LEN function at present works correctly:

                                    =              LEN              (              "apple"              )              // returns 5

#NAME error text string entered without quotes FIXED

Text value with smart quotes

Text values needed to be quotes with straight double quotes (i.e. "apple"). If "smart" (sometimes called "curly") quotes are used, Excel won't translate these as quotes at all and will instead return #NAME?

                                    =              LEN              ("apple")              // returns #NAME?

To fix, simply replace the smart quotes with direct quotes:

                                    =              LEN              (              "apple"              )              // returns 5

Notation: some applications, like Microsoft Word, may modify direct quotes to smart quotes automatically, so accept care if you are moving a formula in and out of unlike applications or environments.