Excel practice › Excel errors › #NAME? error
#NAME? error in Excel: what it means and how to fix it
#NAME? means Excel found a word in the formula that it doesn't recognize. Check the function name first: =VLOKUP(F2,A2:D7,4,FALSE) is one letter short of =VLOOKUP(F2,A2:D7,4,FALSE).
If the function names are right, look for text without quotation marks. =COUNTIF(C2:C7,Paper) makes Excel look for something named Paper. =COUNTIF(C2:C7,"Paper") counts the cells that say Paper.
The causes, in the order worth checking
- A misspelled function name.
- Text without quotation marks.
- A range with its colon missing, like
D2D7forD2:D7. - A named range that doesn't exist, or is misspelled.
- A function your version of Excel doesn't have.
- A function from an add-in that isn't switched on.
All six are the same event: Excel reads a word, looks for a function or a name called that, and finds none.
1. A misspelled function name
Microsoft's own help page gives this as the top reason for the error. A stock list, and a lookup with a typo in it:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | SKU | Item | Category | On hand | SKU | On hand | |
| 2 | P-110 | Copy paper | Paper | 140 | W-214 | #NAME? | |
| 3 | P-125 | Legal pads | Paper | 60 | |||
| 4 | W-201 | Gel pens | Writing | 320 | |||
| 5 | W-214 | Markers | Writing | 85 | |||
| 6 | F-330 | File folders | Filing | 210 | |||
| 7 | F-345 | Binders | Filing | 48 |
VLOOKUP and the formula returns 85. In Excel, pick the function from the list that appears as you type and the name can't be misspelled.Wrapping a formula in IFERROR doesn't fix a typo. It hides it. This is the same misspelled lookup with an IFERROR around it, and it says "Not found" for a SKU that is in the list:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | SKU | Item | Category | On hand | SKU | On hand | |
| 2 | P-110 | Copy paper | Paper | 140 | W-214 | Not found | |
| 3 | P-125 | Legal pads | Paper | 60 | |||
| 4 | W-201 | Gel pens | Writing | 320 | |||
| 5 | W-214 | Markers | Writing | 85 | |||
| 6 | F-330 | File folders | Filing | 210 | |||
| 7 | F-345 | Binders | Filing | 48 |
2. Text without quotation marks
In a formula, text goes in double quotes. Without them Excel reads the word as a name:
| #NAME? | Works |
|---|---|
=COUNTIF(C2:C7,Paper) | =COUNTIF(C2:C7,"Paper") |
=IF(D2<50,Reorder,OK) | =IF(D2<50,"Reorder","OK") |
Cell addresses, numbers, TRUE and FALSE take no quotes. Everything else that is meant as text does, including a single letter or a space. The first exercise below is this one.
3 and 4. A missing colon, a name that doesn't exist
=SUM(D2D7) isn't a range: without the colon, D2D7 is one unknown word, and Excel answers #NAME?. Write =SUM(D2:D7).
A named range gives the same error when the name isn't defined in this workbook or is spelled differently: =SUM(Stock) only works if a range called Stock exists. Formulas tab, Name Manager, lists the names the workbook has.
5. A function your version of Excel doesn't have
A formula can be spelled correctly and still not be known to the Excel that opens it. FILTER, SORT and UNIQUE exist in Excel 2021 and later and in Microsoft 365. Microsoft states that XLOOKUP is not available in Excel 2016 and Excel 2019. A workbook built with them shows #NAME? in those cells when an older version recalculates it.
The sheet below behaves like one of those older versions: it has no FILTER.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | SKU | Item | Category | On hand | Writing on hand | |
| 2 | P-110 | Copy paper | Paper | 140 | #NAME? | |
| 3 | P-125 | Legal pads | Paper | 60 | ||
| 4 | W-201 | Gel pens | Writing | 320 | ||
| 5 | W-214 | Markers | Writing | 85 | ||
| 6 | F-330 | File folders | Filing | 210 | ||
| 7 | F-345 | Binders | Filing | 48 |
=SUMIF(C2:C7,"Writing",D2:D7) returns 405.If the file has to work for people on older versions, write it with functions they have: SUMIF and COUNTIF in place of SUM or COUNT around a FILTER, VLOOKUP or INDEX/MATCH in place of XLOOKUP.
6. A function from an add-in
A few functions only exist while an add-in is enabled. Microsoft's example is EUROCONVERT, which needs the Euro Currency Tools add-in. Without it, the formula shows #NAME?. Add-ins are switched on under File, Options, Add-ins.
Practice: 3 formulas to fix
Type the formula in the box and press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data.
=COUNTIF(C2:C7,Paper) should count the items in the Paper category. It shows #NAME?. In F2, write the formula that works.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | SKU | Item | Category | On hand | Paper items | |
| 2 | P-110 | Copy paper | Paper | 140 | ||
| 3 | P-125 | Legal pads | Paper | 60 | ||
| 4 | W-201 | Gel pens | Writing | 320 | ||
| 5 | W-214 | Markers | Writing | 85 | ||
| 6 | F-330 | File folders | Filing | 210 | ||
| 7 | F-345 | Binders | Filing | 48 |
Show the answer
=COUNTIF(C2:C7,"Paper") gives 2.
With quotes, Paper is a piece of text to compare each cell with. Without them it's a name Excel has never heard of.
=IFERROR(VLOKUP(F2,A2:D7,4,FALSE),"Not found") says Not found for SKU W-214, which is in the list. In G2, write the formula that returns the quantity on hand for the SKU in F2, or Not found when the SKU doesn't exist.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | SKU | Item | Category | On hand | SKU | On hand | |
| 2 | P-110 | Copy paper | Paper | 140 | W-214 | ||
| 3 | P-125 | Legal pads | Paper | 60 | |||
| 4 | W-201 | Gel pens | Writing | 320 | |||
| 5 | W-214 | Markers | Writing | 85 | |||
| 6 | F-330 | File folders | Filing | 210 | |||
| 7 | F-345 | Binders | Filing | 48 |
Show the answer
=IFERROR(VLOOKUP(F2,A2:D7,4,FALSE),"Not found") gives 85.
Once VLOOKUP is spelled right, the lookup runs, and "Not found" appears only when the SKU really is missing. IFNA in place of IFERROR would have shown the typo as #NAME? from the start.
This sheet behaves like Excel 2019: it has no FILTER, so =SUM(FILTER(D2:D7,C2:C7="Filing")) shows #NAME?. In F2, write a formula that returns the total on hand for the Filing category with a function every version has.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | SKU | Item | Category | On hand | Filing on hand | |
| 2 | P-110 | Copy paper | Paper | 140 | ||
| 3 | P-125 | Legal pads | Paper | 60 | ||
| 4 | W-201 | Gel pens | Writing | 320 | ||
| 5 | W-214 | Markers | Writing | 85 | ||
| 6 | F-330 | File folders | Filing | 210 | ||
| 7 | F-345 | Binders | Filing | 48 |
Show the answer
=SUMIF(C2:C7,"Filing",D2:D7) gives 258.
SUMIF checks C2:C7 for Filing and adds the matching cells of D2:D7. It gives the same result as SUM around FILTER, in every version of Excel.
Questions people ask
What does #NAME? mean in Excel?
Excel found a word in the formula that isn't a function, a named range or anything else it knows. It's a spelling or syntax problem in the formula itself, not a problem with your data.
Why do I get #NAME? when the function is spelled correctly?
Then the unknown word is somewhere else in the formula. Look for text without double quotes, a range with its colon missing, a named range that doesn't exist in this workbook, or a sheet name with spaces that isn't wrapped in single quotes. If none of those fit, your version of Excel may not have the function.
Why does XLOOKUP or FILTER show #NAME??
Your version of Excel doesn't have it. XLOOKUP isn't available in Excel 2016 or 2019, and FILTER, SORT and UNIQUE came with Excel 2021 and Microsoft 365. A formula that uses them may also appear with _xlfn. in front of the function name. Rewrite it with functions your version has, or open the file in a newer Excel.
How do I fix a #NAME? error?
Click the cell and read the formula word by word. Correct the function name, put text in double quotes, add the missing colon. Don't cover it with IFERROR: the formula would still be wrong, and nothing would tell you.