This tutorial demonstrates how to fix the #NAME error in Excel and Google Sheets.
When you get a #NAME error as the result of a formula, it’s usually related to a misspelling. It’s important not to ignore it or use the IFERROR Function to resolve it. Instead, find the real problem in the syntax. Below are some of the most common causes of this error.
Function Name Misspelled
Typing a function incorrectly is probably the most usual cause of a #NAME error. Say you have the following dataset 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 yields the correct result.
To avoid this kind of error, you can use the formula autocomplete option, or use the Insert Function button to enter arguments via the dialog box.
Cell or Range Reference Misspelled
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 the 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
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 letters of the named range, and choose from the offered values.
Text Entered Without Double Quotes
In addition to using cell or range reference in a function, you can also use a text string as an argument. But if you enter text without double quotes, you get the #NAME error.
In this case, you entered Monitor as a lookup value, but without double quotes. Text used as a function argument must be within double quotes.
#NAME Error in Google Sheets
All tips above for solving the #NAME error in Excel also apply to Google Sheets.