Overview
The IFNA function returns a user-defined result when a formula evaluates to an #N/A error, and returns the expected result when no error is detected. IFNA is a way to trap and handle #N/A errors specifically without catching other errors.
Syntax
=IFNA(value, value_if_na)
Arguments
value – Any value, expression or reference to check for an #N/A error.
value_if_na – The user-defined result when the formula evaluates to an #N/A error.
Version
Excel 2013 and onward
Purpose
The IFNA function is used to trap and handle #N/A errors when the error is detected within a value, expression or reference. When the value returns a normal or expected result, the IFNA function returns the normal or expected result. It is only when the value results in an #N/A error that the IFNA function changes the result.
Although the IFERROR function traps and handles #N/A errors along with a wide variety of errors in Excel, the IFNA function is particularly useful when working with lookup functions, such as MATCH, VLOOKUP, HLOOKUP or XLOOKUP. This is because lookup functions will typically result in an #N/A error when values aren’t found during the lookup process. This helps better identify why a function or formula is not working as expected.
Examples
The IFNA function can handle the following types of errors:
- #N/A
Using IFNA to handle your #N/A errors can be extremely beneficial for maintaining data integrity. For larger workbooks with many dependent cells, a single error can cascade quickly into a large problem.
Let’s look at the following example, where we are trying to lookup specific fruits to determine how many were sold:
=VLOOKUP(F5, B5:C7, 2, FALSE) // Results in a #N/A error
Here we are getting a #N/A error because the fruit ‘Pear’ doesn’t exist in our list of fruits in B5:B7. The error arises because the Pear value is ‘Not Available’. Instead of this showing up as an error, let’s trap and handle this error using the IFNA function:
Similar to the IF function, the value_if_na argument needs to be bounded by quotation marks if the result is a string value. If it is numerical, it does not need to be bounded by quotation marks:
In Excel, there is no one-correct-way to trap and handle errors. Depending on your application, you may want to only determine if a formula or expression is resulting in an error, as opposed to automatically handling and correcting that error.
As such, below is a list of other error functions in Excel:
- IFERROR function – traps errors and provides a user-defined result
- ISERROR function – returns TRUE for any error
- ISERR function – returns TRUE for any error except the #N/A error
- IFNA function – traps #N/A errors and provides a user-defined result
- ISNA function – returns TRUE for only #N/A errors
- ERROR.TYPE function – returns the numeric code for any given error
Notes
- If value_if_error is left blank, it will default to FALSE
Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has been consulting on and teaching best practices in Microsoft Excel to over 400 companies across 30+ countries.
Want to learn more?
Join My Excel Academy Today!
This All-Access Pass Includes:
- Access to All My Excel Academy Courses
- 20+ hours of on-demand video
- 1-on-1 instructor support
- Mobile learning supported
- Frequently updated content
- Learn at your own pace
- 500+ practice problems
- Certificate of completion
30-Day Money-Back Guarantee
Want to learn more?
Join My Excel Academy Today!
All-Access Pass Includes:
- Access to All My Excel Academy Courses
- 20+ hours of on-demand video
- 1-on-1 instructor support
- Mobile learning supported
- Frequently updated content
- Learn at your own pace
- 500+ practice problems
- Certificate of completion
30-Day Money-Back Guarantee
Want to learn more?
Join My Excel Academy Today!
30-Day Money-Back Guarantee
This all-access pass includes:
- Access to all My Excel Academy Courses
- 20+ hours of on-demand video
- 1-on-1 instructor support
- Mobile learning supported
- Frequently updated content
- Learn at your own pace
- 500+ practice problems
- Certificate of completion
Don’t hesitate – join now!
