If you are looking for the reasons and corrections of NAME Error in Excel, then you are in the right place.
Sometimes working with a dataset in Excel you may unwittingly make some mistakes while using formulas and then it shows NAME Error instead of your results. To know the causes of this error and to avoid it you can follow this article.
Download Workbook
10 Examples of Reasons and Corrections of NAME Error in Excel
I will use the following table, which contains the Sales Records of a company, for demonstrating the causes and the ways of rectifying NAME Error in Excel easily.
For this purpose, I have used Microsoft Excel 365 version, you can use any other versions according to your convenience.
1. Incorrect Formula Name Causing NAME Error in Excel
Let’s say, you want to get the sum of Sales for Apple using the SUMIF function. But you can get here NAME Error due to typing a wrong function name like this example.
➤I have written the following formula for getting the Sum of Sales for Apple.
=SUMF(B5:B12,"Apple",D5:D12)
But, here I have typed SUMF instead of SUMIF function.
Result:
For using the incorrect function name, you will get the NAME Error like the following result.
Read More: [Fixed] Excel Found a Problem with One or More Formula References in This Worksheet
2. Incorrect Range Using in a Formula
Here, we want to get the Region for the Item Banana by using the VLOOKUP function.
But for using the wrong range in the formula we can get the NAME Error instead of our desired result.
➤I am using the following formula in cell F6 for getting the Region.
=VLOOKUP(R17,B5C12,2,FALSE)
But, here I have typed the range as B5C12 instead of B5:C12.
Due to the missing “:” sign in the range we will not get the proper result here.
Result:
For using the incorrect range, you will get the NAME Error like the following result.
If you use any range exceeding the limit of the range A1: XFD1048576 like A1: XFD1048580 then you will also get the NAME Error.
Read More: Errors in Excel and Their Meaning (15 Different Errors)
3. Misspelled Named Range Using in a Formula
Because of not using the named range correctly in a formula can occur the NAME Error also.
➤ Here, the range of the cells in the column Item is named as fruits.
➤ The following formula has been used for getting the Sum of Sales for Apple.
=SUMIF(fruit,"Apple",D5:D12)
I have used here fruit as the named range instead of fruits.
Result:
For using the wrong named range in the formula, you will get the NAME Error like the following result.
Read More: [Fixed!] NUM Error in Excel (4 Reasons with Solutions)
4. Writing Text String in a Formula without Quotation
If you write any text string in a formula without using a quotation sign for this string, then you will get the NAME Error instead of the correct result.
➤I have used the following formula for getting the Region
=VLOOKUP(Banana,B4:D12,2,FALSE)
But, here I have typed Banana instead of “Banana” for the lookup value in this formula.
Result:
For missing the quotation marks for the text string Banana in the formula, you will get the NAME Error like the following result.
Read More: Excel Error: The Number in This Cell is Formatted as Text (6 Fixes)
5. Using the Formula Assistance for Correcting NAME Error
To get rid of the NAME Error, you can use Formula Assistance for having the right formula.
➤When you start to write your function name after pressing the equal sign, you will see all of the matched function names according to your writing.
➤Then just click on your desired formula and press the TAB key.
➤After that, you have to complete the formula by using the correct arguments of this function.
=SUMIF(B5:B12,"Apple",D5:D12)
Here, for the SUMIF function, the first argument was the criteria range which is the range B5:B12, the second argument was the criteria which is Apple and the third argument was the sum range which is the range D5:D12.
Finally, close the bracket.
Result:
After entering the correct formula you will get your desired value for the Sum of Sales for Apple.
Read More: How to Remove Value Error in Excel (4 Quick Methods)
Similar Readings
- How to Fix “Fixed Objects Will Move” in Excel (4 Solutions)
- Excel VBA: Turn Off the “On Error Resume Next”
- How to Fix #REF! Error in Excel (6 Solutions)
- [Fixed] Excel Print Error Not Enough Memory
- On Error Resume Next: Handling Error in Excel VBA
6. Using the Formula Wizard for Correcting NAME Error
You can use the Formula Wizard for avoiding the NAME Error like this example.
➤Select the output cell where you want your result and then the marked sign.
Then, the Insert Function Dialog Box will pop up.
➤Select any of the options in the box select a category ( I have used the Most Recently Used Option)
➤Choose your desired function in the select a function box ( I am using the SUMIF function here)
➤Press OK.
After that, the Function Arguments Wizard will open up.
➤Select the following
Range → B5:B12
Criteria → “Apple”
Sum_range → D5:D12
➤Press OK.
Result:
After that, you will get your desired value for the Sum of Sales for Apple.
Read More: VALUE Error in Excel: 7 Reasons with Solutions
7. Using the Name Manager for Correcting NAME Error
While you have so many named ranges for your dataset it is normal to forget the correct named range for using in a formula. So, to check out the correct named range you can use the Name Manager Option.
➤ Here, I have used items as the name for the range of cells in the Item column.
➤Go to Formulas Tab>>Defined Names Group>>Name Manager Option
After that, the Name Manager Wizard will appear.
➤For the items named range, you can see all of the information for this range like the Values, Sheet Name, etc. By getting the information you can enter the correct name of the range in the formula.
➤We have used the following formula using the correct named range.
=SUMIF(items,"Apple",D5:D12)
Here, items is our correct name of the range.
Result:
Afterward, you will get your desired value for the Sum of Sales for Apple.
Read More: [Fixed!] Null Error in Excel (3 Possible Solutions)
8. Selecting the Range Instead of Typing
While typing the range in a formula you may sometimes miss the “:” sign or you may refer to a wrong range which causes the NAME Error. To avoid this you can select the range instead of typing.
For the table range argument of the VLOOKUP function, we want to select the whole data range from B5 to D12.
➤Select the first cell B5 of the range
➤Drag down and right the arrow sign.
In this way, you will be able to select the whole range.
➤Complete the other two arguments of this function.
Result:
After pressing ENTER, you will get the corresponding Region for the item Banana.
Read More: REF Error in Excel (9 Suitable Examples)
9. Using Go to Special Option for Finding NAME Error
For a large dataset it is difficult to find all of the NAME Errors, so you can use the Go To Special Option to find all of the errors at once.
➤Go to Home Tab>>Editing Group>> Find & Select Dropdown>> Go to Special Option.
Then the Go To Special Dialog Box will appear.
➤Select Formulas
➤Click on Errors
➤Press OK.
Result:
In this way, you will be able to select the cell where this error has occurred.
10. Using Find & Select Option for Finding NAME Error
You can use the Find & Select Option for identifying the NAME Errors.
➤Go to Home Tab>>Editing Group>> Find & Select Dropdown>> Find Option.
➤Type the error name #NAME? in the Find what box.
➤Select the following
Within→ Sheet
Search → By Rows
Look in → Values
➤Click on Find All.
Result:
After that, you will get the cell name $F$7 which has the NAME Error.
Things to Remember
⦿ For opening up an Excel Workbook which has used the new version (like Microsoft Excel 365 version or Microsoft Excel 2019) functions such as the FILTER function, the XLOOKUP function, etc. in an older version may cause the NAME Error.
Practice Section
For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.
Conclusion
In this article, I tried to cover the reasons and corrections of NAME Error in Excel. Hope you will find it useful. If you have any suggestions or questions feel free to share them with us.
Related Articles
- How to Calculate Tracking Error in Excel (with Detailed Steps)
- [Fixed!] Error Messages in Excel (10 Practical Solutions)
- Calculate Mean Squared Error in Excel (3 Easy Methods)
- How to Correct a Spill (#SPILL!) Error in Excel (7 Easy Fixes)
- [Fixed!] ‘There Isn’t Enough Memory’ Error in Excel (8 Reasons)
- How to Find Reference Errors in Excel (3 Easy Methods)