Excel #NAME? error with IFNA

excel excel-formula excel-2016
f_qi picture f_qi · May 8, 2017 · Viewed 7.1k times · Source

Strange behavior in Excel 2016 running on Windows 7.

Please refer to the screenshots below:
In Pic 1, the cell in red box shows #NAME? error. When I click "Enable Editing" on top, you can see in Pic 2, the cell shows value. Pic 3 shows the formula I'm using for this cell.

Additional information: I have multiple copies of this excel file, filled with different information. Only a few of them show this error, others display the value with no problem.

My question is, what causes this and how I can fix it?

Pic 1:
enter image description here

Pic 2:
enter image description here

Pic 3:
enter image description here

Answer

user4039065 picture user4039065 · May 8, 2017

The IFNA function was introduced with Excel 2013. If the workbooks are being opened on computers using Excel 2010 (or earlier) the function will not work and produce a #NAME? error as Excel 2010 (or earlier) does not have IFNA as a native function. Further, if the workbooks are saved in Excel 2010 (or earlier), the #NAME? remains and will be displayed for Excel 2013 and 2016 users.

Switch to the IFERROR function which was introduced with Excel 2007 to provide wider compatibility. For the purposes of offering a default value in the case of no match to the lookup, they operate exactly the same way. The IFNA is only useful to isolate errors that are not #N/A.

Related questions

Remove AM/PM from "time" cell

I am calculating all the hours/minutes/seconds of all calls made on our PABX system. The cell has to be "time" format to display correctly. BUT, this adds an AM/PM at the end of the result as if …

Read More
Dynamically expand an excel table based on entries in another table

I'm setting up a spreadsheet with the goal of allowing users to enter data by employee and date. I'd like to create a dynamic table that expands based on entries into another table. E.g., can I automatically expand TABLE1 …

Read More
Shortcut to Apply a Formula to an Entire Column in Excel

If I select a cell containing a formula, I know I can drag the little box in the right-hand corner downwards to apply the formula to more cells of the column. Unfortunately, I need to do this for 300,000 rows! Is …

Read More