How to use the ISNA function in Excel
(+3 examples)

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

In this tutorial, you learn all about the fantastic ISNA function.

ISNA is one of many functions in the IS family of functions.

You use ISNE to check for the #N/A error.

This error occurs when another formula in Excel cannot find what we are asking it to find.

It is not a sign that there is anything wrong with the formula itself.

The ISNA function provides a simple, yet elegant, way to handle the #N/A error.

It allows the user to substitute a different value in place of the error. This can be a custom value such as a text string.

Let’s get into the details…

Kasper Langmann, Co-founder of Spreadsheeto

Syntax

The syntax for ISNA is about as basic as you can get. It requires a single argument, value.

The syntax of the ISNA function

=ISNA(value)

However, the value argument is another formula itself. Therefore, the complexity of your ISNA depends on the situation.

On its own, the ISNA function will return one of two possible values.

It will return either a TRUE or FALSE value based on whether the value argument turns out to be the #N/A error.

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file

Example 1: basic TRUE or FALSE result

One of the more common situations you will run into the #N/A error is when using various lookup functions.

For instance, if you have a VLOOKUP that cannot find a match to the value it is trying to look up, it will return this error.

As we alluded to earlier, this is not a flaw in the VLOOKUP formula itself. It is simpye a matter of its design.

With ISNA, the developers at Microsoft have provided a more elegant way to handle this error.

So let’s look at this in a practical example.

In the following figure, we have a product table containing a code and matching quantity on hand.

We have build a lookup table using the VLOOKUP to return the quantity on hand for the product code we enter.

isna-setup

We will wrap our VLOOKUP formula in an ISNA function so we can test it for the error.

Kasper Langmann, Co-founder of Spreadsheeto
isna-vlookup

When we select a product that exists in the lookup table, the ISNA function evaluates to FALSE.

isna-false

However, when we enter a value that does not exist in our lookup table, the ISNA function evaluates to TRUE.

This is because the VLOOKUP will result in the #N/A error.

isna-true

Example 2: creating something a bit more elegant than TRUE and FALSE

If we recruit the help of a well-placed IF function, we can leverage the TRUE/FALSE nature of the ISNA results.

This will allow us to substitute a custom text string for either outcome.

Let’s use the same VLOOKUP and product table as we used in Example 1.

We will examine how we can use an IF function to return the text string “Available” when we get FALSE from the ISNA.

isna-if

If it results in a TRUE outcome, we will have the IF function output “Invalid Product Code.”

Kasper Langmann, Co-founder of Spreadsheeto
isna-if-detail

Note that you can insert a cell reference for the value argument in the ‘ISNA’ formula.

This is what we have done in our IF formula in cell C15 for Remarks.

Depending on the scenario, you can combine the ISNA function with others to generate some useful results.

Example 3: even more elegant results when combining ISNA with other functions

We showed you how you can generate custom text strings by combining ISNA with VLOOKUP and IF.

In this next example, we are going to take it a step further and use the actual results of a VLOOKUP with a text string.

Of course, we will also generate a custom message to address the #N/A error should it occur.

We have a table that contains room types and the quantity available for use. If the user requires a certain type of room, they can enter it into the Required Room Type field.

room-types

Our formula in the Availability field will tell the user how many rooms are available.

If it is not a valid room type, it will tell them to select a valid room type.

If we select room type D, we get the following result.

room-availability

If we insert room type R, we will get the following result.

room-type-na

So how did we do this? This is another way to leverage ISNA when used in a larger formula to generate different outputs.

Kasper Langmann, Co-founder of Spreadsheeto

We will place a bare bones VLOOKUP in cell F6. Then substitute that cell reference to make things a bit easier to read.

initial-vlookup

This is definitely a long formula.

Yet, the important thing to note is that this is the same concept as our previous example. The difference here is that we have used some concatenation to create a text string.

This string includes the resulting value from the VLOOKUP when there is no #N/A error.

Conclusion

The ISNA function is simple in concept and design. But often, this is exactly what can make a function so powerful.

As you can see after a few examples, the ISNA makes for elegant handling of the #N/A error.

We can also integrate ISNA into a larger formula. This allows us to generate custom outputs can that are both elegant and relevant.

Kasper Langmann, Co-founder of Spreadsheeto