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 quote.

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

For example,

Substituting character in text. Formula shown at the top in the insert function box.

Here 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

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

#NAME? error when name or character function misspelled.

The most basic human error is the another reason of #NAME? error is when any predefined values or text method. Excel understand predefined text characters and numbers and 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 name

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 ). First array is A2:A9 named as ID & second array is B2 : B9 named Colors.

Look! There's #NAME? Error due to the spelling error of the array argument where named range array is used. Array have a predefined method to call in excel. Excel understand only predefined text as argument. Named range array here is Colors instead of Colours. Learn more about 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? error in Excel is explanatory. Find more articles on Excel errors 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

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube