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.

Skip to the 3 exercises ↓

The causes, in the order worth checking

  1. A misspelled function name.
  2. Text without quotation marks.
  3. A range with its colon missing, like D2D7 for D2:D7.
  4. A named range that doesn't exist, or is misspelled.
  5. A function your version of Excel doesn't have.
  6. 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:

Live example#NAME?: VLOKUP
G2fx
ABCDEFG
1SKUItemCategoryOn handSKUOn hand
2P-110Copy paperPaper140W-214#NAME?
3P-125Legal padsPaper60
4W-201Gel pensWriting320
5W-214MarkersWriting85
6F-330File foldersFiling210
7F-345BindersFiling48

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:

Live exampleA typo hidden by IFERROR
G2fx
ABCDEFG
1SKUItemCategoryOn handSKUOn hand
2P-110Copy paperPaper140W-214Not found
3P-125Legal padsPaper60
4W-201Gel pensWriting320
5W-214MarkersWriting85
6F-330File foldersFiling210
7F-345BindersFiling48

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.

Live example#NAME?: FILTER in a version that lacks it
F2fx
ABCDEF
1SKUItemCategoryOn handWriting on hand
2P-110Copy paperPaper140#NAME?
3P-125Legal padsPaper60
4W-201Gel pensWriting320
5W-214MarkersWriting85
6F-330File foldersFiling210
7F-345BindersFiling48

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.

Exercise 1Text without quotes

=COUNTIF(C2:C7,Paper) should count the items in the Paper category. It shows #NAME?. In F2, write the formula that works.

F2fx
ABCDEF
1SKUItemCategoryOn handPaper items
2P-110Copy paperPaper140
3P-125Legal padsPaper60
4W-201Gel pensWriting320
5W-214MarkersWriting85
6F-330File foldersFiling210
7F-345BindersFiling48
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.

Exercise 2A typo behind an IFERROR

=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.

G2fx
ABCDEFG
1SKUItemCategoryOn handSKUOn hand
2P-110Copy paperPaper140W-214
3P-125Legal padsPaper60
4W-201Gel pensWriting320
5W-214MarkersWriting85
6F-330File foldersFiling210
7F-345BindersFiling48
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.

Exercise 3A function this version doesn't have

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.

F2fx
ABCDEF
1SKUItemCategoryOn handFiling on hand
2P-110Copy paperPaper140
3P-125Legal padsPaper60
4W-201Gel pensWriting320
5W-214MarkersWriting85
6F-330File foldersFiling210
7F-345BindersFiling48
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.

Keep going

Ten questions in a real spreadsheet, from SUM to INDEX/MATCH, with a level at the end and the lesson to start from. Free, no sign-up.

Related

VLOOKUP function, with live examplesCOUNTIF function, with live examplesSUMIF function, with live examplesFILTER function, with live examples#VALUE! error in Excel: meaning and fixes#SPILL! error in Excel: meaning and fixesExcel formula errors, fixed liveExcel practice online

Written and tested by Nico, who builds SheetDrill (about). Every formula on this page ran through the engine that checks the exercises before the page was published. Checked against: Microsoft Support: How to correct a #NAME? error, Microsoft Support: XLOOKUP function, Microsoft Support: FILTER function, Microsoft Support: How to avoid broken formulas in Excel. Last checked November 2026.