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
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.
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.
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 use the wildcards in excel
Create drop down list in excel with colour
Popular Articles
If with conditional formatting
Join first and last name in excel
Count cells which match either A or B
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
