Excel formula returns #NAME? and will not calculate
A #NAME? error means Excel does not recognise something you have typed as a name: a function, a named range, or a piece of text without its quotes.
What causes it
A function spelled slightly wrong
One wrong letter is enough. VLOKUP, XLOOKP, or COUNITF will all return #NAME?. Check the spelling against the function list.
A function your Excel version does not have
XLOOKUP, TEXTBEFORE, TEXTSPLIT and UNIQUE only exist in Microsoft 365 and Excel 2021. On an older version they come back as #NAME?.
Text without quotation marks
A word meant as text, like Paid, must be written "Paid". Without the quotes Excel reads it as a name it cannot find.
A named range that no longer exists
If the formula refers to a name that was deleted or misspelled, Excel cannot resolve it. Check Formulas, Name Manager to see which names really exist.
A function name in another language
A file made in a non-English Excel may carry translated function names. English Excel only accepts the English ones.
What to do
Read the part of the formula Excel has highlighted. If it is a function, check the spelling and whether your version supports it. If it is a word that should be text, put quotes around it. If it should be a named range, confirm the name exists.
Questions
How do I know if my Excel has XLOOKUP?
Type =XLOOKUP( into an empty cell. If a tooltip appears, you have it. If it turns into #NAME?, you are on an older version and need VLOOKUP or INDEX and MATCH.
Why did a formula from a colleague give #NAME? on my machine?
They may be on a newer Excel with functions yours does not have, or on a different language version. The formula that works for them may need rewriting for your setup.
Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.
Excel version
XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.
