0 ITEMS
How To Remove #NA Error With ISNA Excel Function
Financial modeling and analysis not only involves making a complex evaluation of a business, company, or investment. It also involves looking up and identifying values to facilitate comparative analysis, data analysis, etc. When formulas and functions in financial models cannot find the information needed, it results in #NA error in Excel. How to identify and handle #NA error in Excel? What is the ISNA function in Excel? What are the parts of the ISNA Formula Excel? How to use the ISNA Excel function? Can you combine ISNA Formula Excel with other Excel functions? In this article, we will discuss the answers to common questions about ISNA in Excel.
The Basics of ISNA in Excel Function
Excel has many error checking and handling functions but the ISNA Excel function only focuses on one single error type, the “not available”, “no value available” or more commonly called the #N/A error in Excel.
The ISNA function in Excel is found under the Information functions and helps users identify if a cell contains the #NA error by displaying either TRUE/FALSE.
ISNA Formula Excel
Where:
Value
– A value, formula, function, or reference cell that is to be tested for #NA error (required argument).
– When #NA error is identified, ISNA Formula Excel returns TRUE otherwise FALSE.
Here are examples of how the formula for the ISNA in Excel works.
The ISNA Excel Function with IF Function
The ISNA function in Excel returns either TRUE/FALSE but you can customize the return by combining the IF and ISNA Excel functions.
For example, using the previous example, you can remove the #NA error and replace it with the text error message “Not Applicable”.
Let’s go over the combined IF and ISNA in Excel functions.
- The TRUE/FALSE values resulting from the ISNA function in Excel go through the logical test of the IF Function.
- If the test is TRUE (#NA error) then customized “Not Available” error message is displayed.
- If the test is FALSE (no error or other than #NA error) then the value or error is displayed.
How to Use IF, VLOOKUP, and ISNA Excel Functions Combined
The VLOOKUP function looks up values vertically in a table but when the value is missing or not found, the formula returns the #NA error.
For example, the December EUR/USD FX table below uses the VLOOKUP function by looking up a specific day and returns the corresponding FX value.
VLOOKUP Formula In Excel =VLOOKUP(D2,A1:B16,2,FALSE).
However, the lookup date in reference cell D2, November 15, 2019, is not valid and results in the #NA error. Errors appearing in a financial model make it less professional, less polished, and not visually appealing.
A better way to handle the #NA error in Excel is to use the ISNA in Excel function.
ISNA Formula Excel and VLOOKUP formula =ISNA(VLOOKUP(D2,A1:B16,2,FALSE))
Doing so removes the #NA error and replaces it with TRUE.
But this is not meaningful and relevant for the user, a clearer and customized error message such as “Not Found” will be helpful.
The best way to handle #NA error is by inserting another function, the IF function.
IF formula, ISNA Formula Excel, and VLOOKUP formula
=IF(ISNA(VLOOKUP(D2,A2:B16,2,FALSE)),”Not Found”,VLOOKUP(D2,A2:B16,2,FALSE))
The combination of these three formulas is confusing at first but the logic is simple.
- ISNA function in Excel checks whether the VLOOKUP results in #N/A error. When it does, ISNA Excel formula returns TRUE otherwise FALSE.
- The TRUE/FALSE values go through the logical test of the IF Function:
- If the test is TRUE (#NA error) then customized “Not Found” error message is displayed.
- If the test is FALSE (the lookup value is found) then the VLOOKUP formula returns the matched value.
In newer versions of Excel, a function combines both IF and ISNA Excel functions into one single function; the IFNA function.
Key Areas to Remember using the ISNA Excel Function
- ISNA function in Excel handles #NA error only and disregards all others.
- The ISNA Formula Excel returns either TRUE/FALSE.
- For the ISNA in Excel function to return customized error messages, the IF and ISNA Excel functions must be combined.
- Alternatively, you can use the IFNA function to catch #NA error only with a customized error message. This function combines both IF and ISNA into one single function.
- To catch and handle specific errors, use the following functions:
- ISERROR Function to catch all errors and return TRUE/FALSE
- IFERROR Function to call all errors with a customized error message
- ISERR Function to catch all errors except #NA error and return TRUE/FALSE
Here is a list of financial model templates that you can use as a base to try using the ISNA Excel Function:
