Exceljet

Quick, clean, and to the point

How to fix the #NAME? error

Excel formula: How to fix the #NAME? error
Explanation 

The #NAME? error occurs when Excel can't recognize something.  Frequently, the #NAME? occurs when a function name is misspelled, but there are other causes, as explained below. Fixing a #NAME? error is usually just 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 example below, the VLOOKUP function is used to retrieve an item price in F3. The function 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 4.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

Below the range used in F3 has been fixed:

#NAME error example range entered FIXED

Note: forgetting to include a colon (:) in a range will also trigger the #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 function returns #NAME?

=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 not enclosed by quotes ("") in a formula.

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 can cause a #NAME? error when no match is found. In the example below, the LEN function is used to get the length of the word "apple". In B3 the formula is entered without the text string "apple" in quotes (""). Because apple is not a function name or named range, the result is #NAME?

=LEN(apple) // returns #NAME?

#NAME error text string entered without quotes

Below, quotes have been added and the LEN function returns the correct length of the string "apple":

=LEN("apple") // returns 5

#NAME error text string entered without quotes FIXED

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.