If you use the VLOOKUP function – or HLOOKUP or MATCH – you may have noticed that some of your formulas result in #N/A errors. #N/A errors can make your worksheet look sloppy and unprofessional.
If you would rather see a dash (-), a 0, or any other message you specify, consider using the IFNA function.
The Excel IFNA function returns an alternate value as specified by the user when a formula returns an #N/A error. When you combine the IFNA function with other functions in Excel, you can eliminate #N/A errors.
IFNA is one of the functions participants in our Microsoft Excel training are always eager to learn about.
Syntax for the IFNA Function in Excel
The syntax for the Excel IFNA function is pretty simple. There are only two arguments, both are required.
=IFNA(value, value_if_na)
value is the formula or expression that is checked for an #N/A error.
value_if_na provides the value to be returned in place of the #N/A error.
Excel IFNA Function Examples
Let’s take a closer look at IFNA function. These examples incorporate the VLOOKUP function. It’s pretty common to use the IFNA function with VLOOKUP.
In the spreadsheet below, I’m using a VLOOKUP to determine the price of various items in column A. I clicked in cell D2 and typed:
=VLOOKUP(A2,$F$2:$G$6,2,FALSE)
We were able to find prices for cookies and donuts but pizza isn’t in the list of items so Excel returned the dreaded #N/A error.
Add the IFNA Function to Suppress the #N/A Error
Next, let’s add IFNA to suppress the #N/A error.
This time, I’ll click in cell D2 and type:
=IFNA(VLOOKUP(A2,$F$2:$G$6,2,FALSE),”Not Found”)
This time, “Not Found” is substituted for the #N/A error resulting in a more polished looking spreadsheet. If you don’t want any text to appear in place of #N/A, just use an empty string value (“”).
Is the IFNA Function the Same as IFERROR in Excel?
Many spreadsheet users have heard of the IFERROR function in Excel and wonder if it accomplishes the same thing as IFNA.
Here’s the low down.
The IFERROR and IFNA functions both trap errors but the IFERROR function substitutes an alternate value if it encounters any number of errors. IFNA will only substitute a user-defined value if it encounters an #N/A error.
Which Versions of Excel Have the IFNA Function?
Microsoft introduced the IFNA function when it released Excel 2013. You can use the IFNA function in Excel 2013 or later… and the current version of Office 365.
What’s Next?
Learning about functions is a terrific way to become more productive in Excel.
If you’ve enjoyed reading about the IFNA function, you should check out XLOOKUP vs VLOOKUP: Excel Trainer Explains Key Differences. These functions are incredibly useful for anyone who needs to combine data from multiple sources.
See you next time!
