**How to use the ISNA function in Excel**

(+3 examples)

(+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…

**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.

**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.

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

When we select a product that exists in the lookup table, the **ISNA function evaluates to 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.

**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.

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

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.

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.

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

So how did we do this? This is

another way to leverage ISNAwhen used in a larger formula to generate different outputs.

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

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.