Financial models contain numerous assumptions and formulas which are based on data and inputs. Sometimes these data and inputs are invalid, missing, or simply not available causing the financial model to display #NA error. The #NA error in Excel does not necessarily give value or information to the end-user. Wouldn’t it be better to check if a formula results in a #NA error? Will a specific error message provide a much clearer meaning for the user? The IFNA Function in Excel checks if a formula returns the #NA error and replaces it with a specific error message. In this article, we will learn the basics of the IFNA Function and how to handle and replace the #NA error.

The Basics of Excel IFNA Function

The IFNA Excel function is found under the Logical Function in Excel. The Excel IFNA formula checks #NA error only and disregards other errors such as #VALUE, #REF, etc. When the IFNA Function identifies #NA error, the function replaces it and returns the user’s preferred error message.

To fully appreciate the IFNA Function in Excel, it is best to understand the IF Function.

The IF function is a simple Logical function that:

1. Evaluates or tests data, formula, or expression based on given criteria
2. Depending on the outcome of the test,  it performs a specific course of action otherwise an alternative course of action

The IFNA Excel function works in the same way.

The IFNA Excel Formula

Where:

Value
– Cell reference, formula, or expression to be tested for #NA error. (Required Argument)
– Non-#NA errors are ignored.

Value_If_NA
– The value to return if #NA error is identified. (Required Argument)
– This can be a number, text string, blank cell, or cell reference.

It is important to keep in mind that when the logical test in the IFNA Excel formula evaluates a #NA error, only then the second argument is triggered and returns the error message given in the Value_If_NA argument.

IFNA Function Examples

The below table illustrates the basic application of the Excel IFNA function.

Using Excel IFNA with Lookup Functions

The lookup functions such as VLOOKUP, HLOOKUP, MATCH, and INDEX are powerful tools to find and retrieve data. But, when data is not found or missing, the #NA error in Excel is displayed. Financial models containing errors look unprofessional and gives no added value for the end-users. The Excel IFNA function replaces the #NA error with something more relevant and specific.

To illustrate the IFNA Function in Excel,  assume below table of February commodity prices for 2 types of commodity, A and B. To lookup the price by date and commodity type, the VLOOKUP and MATCH functions are combined for a 2-way lookup.

=VLOOKUP(F2,A2:C24,MATCH(F3,A2:C2,0),FALSE)

The price for Commodity A on Feb. 14, 2019 is $91.42.

But entering an invalid lookup date or invalid commodity type will result in #NA error. This doesn’t necessarily give the end-user a meaningful result or information.

Invalid Lookup Date: March 14

Invalid Commodity Type: Commodity C

A sophisticated financial model notifies the user instead of by returning “Not Found” or “Not Available”. Using the IFNA Function, you can test a formula’s result for the #NA error and instruct it to replace and return a clearer error message.

Inserting the IFNA Function in Excel into the combined VLOOKUP and MATCH functions will replace the #NA error with “Not Available”.

Invalid Lookup Date: March 14. IFNA Function replaces #NA error with “Not Available”

Invalid Commodity Type: Commodity C. IFNA Function replaces #NA error with “Not Available”

Let’s go over the Excel IFNA, VLOOKUP and MATCH functions combined.

The IFNA Function in Excel tests if the Value argument, results in #NA error or not.

• If Value argument does not result in an error then return the result of the VLOOKUP and MATCH
• If the Value argument results in an error but is a non-#NA error (#VALUE, #REF, #DIV/0, etc.) then display the error
• If the Value argument results in #NA error then return the Value_if_NA argument of “Not Available”

Important Notes on IFNA Function in Excel

• Replacing a true error in a financial model can lead to wrong decisions, it is best to use Excel’s error-checking tools or create error checks specific to the financial model.
• The IFNA Function in Excel only tests #NA error and disregards the rest. Unlike the ISNA function which returns only TRUE/FALSE when it catches #NA error, the IFNA Excel function allows users to customize and return a clearer error message.
• To catch and handle specific errors with the respective course of action, use the following functions:
  • ISNA to catch #NA errors only and return TRUE/FALSE
  • ISERR to catch all errors except #NA error and return TRUE/FALSE
  • ISERROR to catch all errors and return TRUE/FALSE
  • IFERROR to catch all errors and replacing it