Why #NAME? Occur And How To Fix #NAME? In Excel - Excel Tip

Why #NAME? occur And How to Fix #NAME? in Excel

In this article, we will learn about why #NAME? occur And How to Fix #NAME? in Excel.

In excel, at times we get #NAME? error. This error occurs when the formula you have supplied to the cell is not recognized by Excel. As per Microsoft official site a “#NAME? is Excel's way of saying, There's something wrong or missing with the characters your formula used.

Let’s understand it with some examples

#NAME? error due to using text without a quote.

The most basic reason of #NAME? error is when text is provided as the argument to the function. Excel understands predefined text characters and numbers but if some particular text is used in the formula without quotes or cell reference then this error occurs.

Example:

Let's try to substituting characters in the text. Formula shown at the top in the insert function box.

Text to the function is provided directly. Substituting a in abc with b.

How to fix it!

two ways to fix the #NAME? Error

First method

Using quotes ( " ) with text ( start and end ) whenever providing the text in the formula as in snapshot shown below.

Second Method

Use the cell reference whenever providing the text in the formula as in snapshot shown below.

#NAME? error when the names are misspelled.

The most basic human error is the another reason of #NAME? error is when any predefined values or text method. Excel understands predefined text characters and numbers and the change in value returns this error.

Predefined Text method and characters in Excel.

1. Function name
2. Array range
3. Named range

We will consider each and every predefined case and how to fix the error if generated.

Misspelled Function Names:

For example, we will take an example of the LOOKUP function.

LOOKUP function looks up the value from one array and returns the value from the corresponding array.

In the snapshot shown below, the function finds the Color matching with the 2 ( ID ).

Look! There's #NAME? Error due to the spelling error of the function. LOOKUP function spelling is wrong in the above formula used. Excel understand LOOKUP predefined text as function.

How to fix this!

Select the cell and edit the text with the correct spelling of the function to get the result from the formula used.

As you can see how Spelling error with predefined function name returns #NAME! error and after the correction the formula works fine.

Array argument to the Formula spelled wrong

For example, we will take an example of the LOOKUP function.

LOOKUP function looks up the value from one array and returns the value from the corresponding array.

In the snapshot shown below, the function finds the Color matching with the 2 ( ID ). First array is A2 : A9 & second array is B2 : B9.

Look! There's #NAME? Error due to the spelling error of the array argument. Array have a predefined method to call in excel. Excel understand only predefined text as argument. Arrays must be given as A2:C9 where A2 is the start cell and C9 is the last cell. A2:C9 will select all the cells between A2 and C9 cells.

How to fix this !

Select the cell and edit the text with the correct spelling of the array argument to get the result from the formula used. As you can see how Spelling error with predefined array argument name returns #NAME! error and after the correction the formula works fine. Named Range Array argument to the Formula spelled wrong.

For example, we will take an example of the LOOKUP function. In the snapshot shown below, the function finds the Color matching with the 2 ( ID ). The first array is A2:A9 named as ID & second array is B2 : B9 named Colors.

Look! There's #NAME? Error due to a spelling error in the array argument where the named range array is used. Array has a predefined method to call in excel. Excel understands only predefined text as an argument. Named range array here is Colors instead of Colours. Learn more about the named range here.

How to fix this ! 

Select the cell and edit the text with the correct spelling of the array argument to get the result from the formula used.

As you can see how Spelling error with predefined array argument name returns #NAME! error and after the correction the formula works fine.

 

Hope this article about how Why #NAME? occur And How to Fix #NAME? The error in Excel is explanatory. Find more articles on Excel error functions here. Please share your query below in the comment box. We will assist you.

Related Articles

#VALUE Error And How to Fix It in Excel

How to correct a #NUM! Error

How to use the wildcards in excel

Validation of text entries

Create drop down list in excel with colour

Popular Articles

Edit a dropdown list

Absolute reference in Excel

If with conditional formatting

If with wildcards

Vlookup by date

Join first and last name in excel

Count cells which match either A or B

Convert Inches To Feet and Inches in Excel 2016

50 Excel Shortcut to Increase Your Productivity