Search results
Aug 25, 2022 · - The formula contains unrecognised text. I'm using the following formula to return multiple values if found: =TEXTJOIN (" || ", TRUE, IF ($B3=Account!$A:$A, Account!$C:$C, "")) - this one works perfectly. =TEXTJOIN (" || ", TRUE, IF ($A3=Contact!$A:$A, Contact!$E:$E, "")) - this one returns "#NAME?".
Sep 28, 2024 · The #NAME? error appears when Excel encounters unrecognized text in a formula. This can happen due to various reasons such as a misspelled function name , undefined range , or missing quotation marks in text strings.
- Overview
- The formula refers to a name that has not been defined
- The formula has a typo in the defined name
- The syntax is missing double quotation marks for text values
- A colon was omitted in a range reference
- You are using a function that requires a specific add-in that is not enabled in Excel
- Need more help?
- See Also
The top reason why the #NAME? error appears in the formula is because there's a typo in the formula name. Look at the following example:
To avoid typos in formula names, use the Formula Wizard in Excel. When you start typing a formula name in a cell or the Formula Bar, a list of formulas matching to the words you've entered displays in a dropdown. Once you enter the formula name and the opening parentheses, the Formula Wizard displays the syntax as hover text.
You can also use the Function Wizard to avoid the syntactical errors. Select the cell with the formula, and on the Formulas tab, press Insert Function.
Excel automatically loads the Wizard for you.
As you select each argument, Excel gives you the appropriate information for each one.
Below are other causes of the #NAME? error.
When your formula has a reference to a name not defined in Excel, you see the #NAME? error.
See the following example of a SUM function referring to Profit, which is an undefined name in the workbook.
Solution: Define a name in Name Manager, and then add the name to the formula by following these steps:
1.If you already have the data in the spreadsheet, and you want to assign a name to specific cells or a cell range, first select the cells in the spreadsheet. If you want to create a new range, you can skip this step.
2.Go to Formulas > Defined Names > select Define Name, and then select Define Name.
3.Enter a unique name.
If the syntax incorrectly refers to a defined name, you see the #NAME? error.
Continuing with the previous example, a defined name for Profit was created in the spreadsheet. In the following example, the name is not spelled correctly, so the function still throws the #NAME? error.
When you include text references in formulas, you need to enclose the text in quotation marks, even if you’re only using a space. If the syntax omits double quotation marks “” for a text value, you see the #NAME error.
See the following example.
The syntax in this example is missing double quotation marks for has; that explains the error.
Solution: Go back to your syntax and manually inspect to make sure that any text values have quotation marks around them.
If you miss a colon in a range reference, the formula displays a #NAME? error.
In the following example, the INDEX formula throws the #NAME? error because the B2 to B12 range is missing a colon.
There are some Excel functions that work only when certain add-ins are enabled. Using these functions otherwise produces a #NAME? error. For example, to use the EUROCONVERT function, the Euro Currency Tools add-in must be enabled. If you use custom functions or macros that require the Analysis ToolPak, make sure that the Analysis ToolPak add-in is enabled.
To enable add-ins in Excel:
1.Go to File > Options.
2.Select Add-ins.
3.In the Manage list box, select Excel Add-ins, and then select Go.
4.Check the relevant box and select OK.
You can always ask an expert in the Excel Tech Community or get support in Communities.
Feb 7, 2024 · The "#NAME" error in Excel, also shown as "#NAME?", typically occurs when Excel doesn't recognize text in a formula. This could be due to various reasons, such as misspellings or formula issues. Learn how to troubleshoot and resolve this error to make your use of Excel smoother and more efficient.
Mar 29, 2021 · The top 4 causes of the #NAME error in Excel are: ...more. This video covers the top 4 causes of the #NAME error in Excel and how to fix them. By the end of this video you will have a fix for...
- 3 min
- 36.5K
- Pete - HowToAnalyst
Mar 14, 2023 · The problem is not in your formula but in the function itself - Excel IF does not support wildcard characters. However, there is a way to get it to work for partial text match, and this tutorial will teach you how.
People also ask
What is a formula name error in Excel?
How to fix error in Excel formula?
Why do I get a 'name' error in Excel?
What does error text mean in Excel?
How to fix a cell with a name error in Excel?
How do I avoid typos in Formula names in Excel?
Aug 1, 2023 · To highlight cells with certain text defined in another cell, you can use a formula in Conditional Formatting. Select the range you want to apply formatting to. In the Ribbon , select Home > Conditional Formatting > New Rule .
