This tutorial demonstrates how to fix the #NAME Error in Excel and Google Sheets.
When you get a #NAME error as a result of a formula, it’s usually related to misspelling. It’s important not to ignore it or use the IFERROR Function to resolve it, but to find where is the real problem in the syntax. Here are some of the most common causes of this error.
Function Name Misspelled
Typing a function wrongly is probably the most usual cause of a #NAME error. Say that you have the following data set with Product in column B and Price in column C. You wanted to get a Price for Monitor in F2, but you got this error.
In this case, you have to correct the name of the function, since it’s missing one O (VLOKUP to VLOOKUP). After you change it, the formula will give the correct result.
In order to avoid this kind of error, you should be using the formula autocomplete option, or use the Insert Function button to enter arguments via the dialog box.
Cell or Range Reference Misspelled
Similar to function, it can also happen that a range or a cell in the function arguments is misspelled.
In this case, instead of B2:C7, the range is BB:C7. To correct this, you can correct it to B2:C7 manually. However, it is recommended that you select a range while typing the formula or use the function dialog box. This way, you won’t have a chance to misspell a range.
Named Range Misspelled
Instead of using cell references, you can create a named range and use it as an argument in a function. Say that your range with products and prices (B1:C7), has a range named products.
Now, if you manually type the named range in the formula, you can easily misspell it.
In this case, instead of products, there is productss named range as a function parameter (with double s). To avoid this, when entering the argument, type several first words of the named range, and choose from the offered values.
Text Entered as Function Argument Without Double Quotes
Apart from using cell or range reference in function, you can also put a text as an argument. If you enter this text without double quotes, you will get the #NAME error.
In this case, you entered Monitor as a lookup value, but without double-quotes. To use text as a function argument, you have to enter it with double quotes.
Fix the #NAME Error in Google Sheets
All hints for solving the #NAME error in Excel stand also for Google Sheets.