What is Excel ISNA function?
ISNA is one of the so-called IS-functions in Excel. It checks whether a cell contains a #N/A error, which Excel returns when the value is not available to the formula for some reason.
Why do we need ISNA?
The primary rationale for using Excel ISNA function is to catch #N/A errors in a worksheet. There are situations when you expect some of your formulas to return this error. Specifically, if you use VLOOKUP, HLOOKUP, LOOKUP, and MATCH functions in a worksheet, this error may signify that a range of cells does not contain the value you’re looking for. In those cases, you might want to catch this error with a formula and treat it appropriately.
Let’s say we need to link a roster of all sales people in a company to the sales report for a particular month (see Figure 1). The roster contains all the names on a company payroll while a sales report only includes people who generated some sales in a specific month. Therefore, if you try using VLOOKUP formula to get the data from the sales report for each name on the roster, you’ll get a #N/A error for employees with no sales. This makes a table look less professional and also breaks any formulas that reference the range of cells containing #N/A – e.g., the SUM formula at the bottom of the roster table (cell C13).
ISNA function can help you quickly solve this problem. For example, you can use it to find all #N/A errors in a table and substitute them with zeros. This would both make the table look more trustworthy and enable you to reference this range of cells in other formulas. Figure 2 shows how this new, improved table would look like. Please note that the SUM formula in C13 now returns a valid result, as the range doesn’t contain any non-numeric values anymore.
How to do it
Please follow the following steps to apply the abovementioned solution to your VLOOKUP, HLOOKUP, LOOKUP, or MATCH formula:
- Add ISNA check to the formula
- Select and copy your existing formula in a formula bar (Ctrl + C)
- Start a new formula with an IF-clause: =IF(
- Set ISNA as a condition for the IF-clause: ISNA(
- Paste your original formula from the clipboard (Ctrl + V)
- Close ISNA function: )
- Set zero as a value_if_true argument of the IF-clause: ,0,
- Paste your original formula (Ctrl + V) to set it as a value_if_false argument of the IF-clause
- Close the IF function: )
- Copy the improved formula to the remaining cells in a range
For instance, the corrected formula in C11 would look like this (Figure 3):
Beware of other types of #N/A errors
It’s important to remember that the solution above is only designed to capture #N/A errors resulting from values missing in a respective data range. We can call this type of errors “intentional #N/As”. We know from the very beginning that, even if all formulas are totally correct, there might be some cases when we get #N/A error just because the data range does not include the value we are looking for.
On the other hand, sometimes a #N/A error reflects actual mistakes in the formulas we’ve written. Before we proceed with the ISNA check offered in the previous sections, we need to test for those “unintentional #N/As”. Here is the list of possible mistakes that might return a #N/A value:
- The approximate match in an unsorted table. If the range_lookup argument of VLOOKUP or HLOOKUP functions or the match_type argument of MATCH function is set to 1, please make sure the table is sorted numerically or alphabetically. On the other hand, if you are looking for an exact match, just set the last argument in those functions to 0, and then you can proceed with an unsorted table
- Incorrect range size in an array formula. If you’re using an array formula – a visual indication would be curly braces in a formula bar – please make sure the range size of your arguments is the same as the size of the range that includes your array formula
- Errors generated by custom functions or macros. If you created custom Excel functions with VBA, please make sure that those functions don’t miss any required arguments, and that they are active and available for use in a worksheet