Excel ISNA Function – How To Use

The Excel ISNA function checks whether a value is #N/A or not. The function only returns TRUE upon finding the #N/A error. It returns FALSE for all other values or errors. The objective of the ISNA function is to solely recognize and handle #N/A errors and return TRUE when an #N/A error is found and FALSE for everything else.

By #N/A here, we mean 'not available' and ISNA literally means 'Is not available?'. Now with the emergence of newer functions, there are more refined ways of dealing with #N/A errors but since the ISNA function has been around since Excel 2003, it has served as a decent method of identified #N/A errors.

In this tutorial we will show you how the ISNA function works, its synergy effects with other functions, and where it stands against similar error handling functions.

Excel ISNA Function

Syntax

The syntax of the ISNA function is as follows:

=ISNA(value)

Arguments:

value – The value or expression to be tested for #N/A error.

Important Characteristics of ISNA Function in Excel

  • The ISNA function only deals with #N/A errors
  • When the function comes across an #N/A error it returns TRUE otherwise it returns FALSE
  • ISNA is generally used along with the IF function to handle #N/A errors
  • If the formula has any typos or misspelling, the function returns a #NAME?

Examples of ISNA Function

Now would be a good time to look into some examples to understand the ISNA function.

Example 1 – Simple ISNA function

Let’s take a look at ISNA in its simplest form.

Excel-ISNA-Function-Example-01

We are checking column A for #N/A errors using the ISNA function in its simplest form:

=ISNA(A2)

The ISNA function finds an #N/A error in "A2" and hence returns a "TRUE". For all other errors and values, ISNA returns "FALSE". This implies that the ISNA function only singles out #N/A errors.

This is a very basic application of the ISNA function. Let’s move onto more practical examples.

Example 2 – ISNA function with VLOOKUP function

While looking up a value from a set of data, there is of course the possibility of not finding it. When trying to find that missing value using a lookup function (including VLOOKUP, HLOOKUP, XLOOKUP), the result is #N/A since the value is not available.

For example, we have a list of dry cake mixes used for baking and their prices.

Excel-ISNA-Function-Example-02

We are trying to find the prices of a chocolate cake mix and a marble cake mix. Let’s use the VLOOKUP function to find them:

=VLOOKUP(E6,$B$2:$C$11,2,0)

The list of cake mixes doesn’t have "Marble cake" which is why VLOOKUP has returned an "#N/A" error.

Let’s have a look at the results after adding the ISNA function.

Excel-ISNA-Function-Example-03

We have added the ISNA function to the VLOOKUP function:

=ISNA(VLOOKUP(E6,$B$2:$C$11,2,0))

All this is doing right now is telling us whether there was an #N/A error while finding the mentioned cake mixes or not.

Was there a 'not available' error finding "Chocolate cake" mix? FALSE. Chocolate cake mix is present in our dataset.

Was there a 'not available' error finding "Marble cake" mix? TRUE. Marble cake mix is not present in our dataset.

Our objective here is to find the prices of the cake mixes which the ISNA function isn’t doing alone. We will have to add the IF function to get the desired results. Let us do that for you.

Excel-ISNA-Function-Example-04

We have nested the ISNA and VLOOKUP functions in the IF function like so:

=IF(ISNA(VLOOKUP(E6,$B$2:$C$11,2,0)), "Not Available!", (VLOOKUP(E6,$B$2:$C$11,2,0)))

This formula works as follows:

VLOOKUP is told to find the data mentioned in cell "E6" which is "Chocolate cake". The table given to VLOOKUP for searching is within the cell range "B2:C11"; VLOOKUP must find "E6" in the 1st column (column B) of the given table and return the corresponding value from the 2nd column (column C). "0" indicates that VLOOKUP must find an exact match.

Since "Chocolate cake" is enlisted in the range of cake mixes, VLOOKUP finds it, picks up the corresponding price from the 2nd column, and returns the value "$3.21".

The result "$3.21" is passed to ISNA where ISNA checks it for an #N/A error. It doesn’t find an #N/A error and results in "FALSE". The result FALSE is passed to the IF function, which then displays the original result of the VLOOKUP function and so the result "$3.21" is displayed.

In the next case, we will now try to find the price of a "Marble cake" mix. Since the "Marble cake" mix is not listed in our data set so the VLOOKUP is not able to find it in column B and results in an #N/A error. The result is handed over to ISNA and it turns "#N/A" into TRUE. The result TRUE is passed to the IF function, which shows the message "Not Available!" as the final output of the formula.

ISNA vs IFNA

Stepping out of the IS functions family a bit, the IFNA function returns the value specified if the expression resolves to #N/A, otherwise it returns the result of the expression. This would be particularly useful against the ISNA function as we get the same result from IFNA without having to nest ISNA and VLOOKUP functions into the IF function.

Let us show you how the same outcome can be easily achieved by the IFNA function.

Excel-ISNA-Function-Example-05

Take note that we have attained the exact same results as in the previous example by lesser work using the formula:

=IFNA(VLOOKUP(E6,$B$2:$C$11,2,0),"Not Available!")

Since "Chocolate cake" was available in our set of data, the VLOOKUP function did its work and returned the value "$3.21". No work for IFNA here.

For the next instance, VLOOKUP could not find "Marble cake" in column B and results in an #N/A error. The result is handed over to IFNA and it turns "#N/A" into "Not available" and returns that as the result.

That was fairly uncomplicated compared to using IF, ISNA, and VLOOKUP functions together.

How to Choose Between ISNA & IFNA

The ISNA function may be a suitable tool where the results are needed as TRUE/FALSE but if you’re going for a specified result where you want to have control over the outcome, ISNA will have to be used with the IF function whereas IFNA alone would do the same thing.

ISNA vs ISERROR

Quite like ISNA but as the name suggests, ISERROR (is error?) deals with a larger amount of Excel errors and returns TRUE if any error is detected and FALSE otherwise. While this may seem to be the answer to more problems, it will be hard to pinpoint what error has been detected by the function. Upon using ISERROR with VLOOKUP and IF, ISERROR will cover up the underlying problem and return the custom result instead.

Let’s refer to the previous example and see how this applies.

Excel-ISNA-Function-Example-06

If you notice, "Chocolate cake" is available in the dataset. Here, we have used the ISERROR function instead of the ISNA function and made a spelling error in the name of the formula (VLOOKUP is misspelled as VLOOKP). The formula:

=IF(ISERROR(VLOOKP(E6,$B$2:$C$11,2,0)), "Not Available!", (VLOOKP(E6,$B$2:$C$11,2,0))) //Incorrect Formula

The result of the VLOOKUP function results in a #NAME? error due to incorrect spelling. The result is passed onto the ISERROR function which results in TRUE as it has found a #NAME? error. IF function is commanded to return the custom text "Not available" which has covered up the #NAME? error and it appears as if the chocolate cake mix data is not available.

This doesn't truly reflect the scenario since the chocolate cake mix and its price is listed in the table. Let's see what happens when we try to pass the same error through the ISNA function.

Excel-ISNA-Function-Example-07

Making the same typo with ISNA would result in a #NAME? error. Hence, a truer representation comes from the ISNA function here which only works for #N/A errors and shows the underlying problem which is the #NAME? error, indicating a spelling error.

Quite useful, ISERROR best serves the need to cover up more errors, affirming whether there are any errors or not.

How to Choose Between ISNA & ISERROR

In most cases, it would be more prudent to use the ISNA function as it would display other errors indicating the relevant issue with the data or formula.

On the other hand, the ISERROR function would work better where more errors need to be handled.  It will deal with a greater number of errors including the #N/A error and will confirm whether there are any errors in the data or not.

Recommended Reading: IFERROR Function In Excel

ISNA vs ISERR

The ISERR function is the same as its ISERROR brother but it returns TRUE for all errors other than the #N/A error and FALSE otherwise. While it will be helpful using ISERR so there is one less error to think about, it will not work for #N/A errors like in the previous example. Let us demonstrate:

Excel-ISNA-Function-Example-08

It's nearly the end of the tutorial and we are well aware that there's going to be no marble cake tonight. As apparent, the ISERR function has resulted in an #N/A error as VLOOKUP could not find "Marble cake" in the dataset despite the command to return "Not Available!".

How to Choose Between ISNA & ISERR

When in need of narrowing down the errors to be checked, ISERR will work for covering up all errors excluding the #N/A error. It is the exact flip of the ISNA function which will be convenient to deal only with #N/A errors. Both result in TRUE/FALSE upon finding the respective errors.

That's all ISNA has to say about itself but we still have a little riddle for you:

What kind of career would a spider excel in?

.

.

.

.

.

Web design

See you with another chip from the Excel block!

About Ankit Kaul

Well, I am Ankit Kaul, the founder of Excel Trick. I am a die-hard fan of Microsoft Excel and have been working with spreadsheets for the past 10+ years. My only aim is to turn you guys into 'Excel Geeks'. Check out more about me here.