How to Use ISNA Function in Excel (4 Examples)

Excel provides several information functions to provide you the necessary information and help you perform your desired tasks easily and swiftly. Today we are going to show you how to use an information function called: ISNA. For this session, we are using Excel 2019, feel free to use yours (at least 2003).

Practice Workbook

You are welcome to download the practice workbook from the link below.

Excel ISNA Function

1. Basics of ISNA Function

The ISNA function is categorized under the INFORMATION functions in Excel. It checks whether a cell contains the #N/A! error or not and returns TRUE or FALSE depending on the presence of #N/A!.

Summary

Checks whether a value is #N/A, and returns TRUE or FALSE

Syntax

Arguments

value: A value or expression to be checked for #N/A! error.

Versions

Workable from Excel 2003.

2. Uses of ISNA Function

I. Find Value and Check with ISNA

We can find value from string and can check with ISNA for any Not Available (N/A) result.

To keep things simple, we have brought an example dataset of a few random texts.

Here we are going to find some letters randomly within the texts. For example, we are searching for “I” in Hello World! using the FIND function.

The FIND function searches the value from the text and returns the position if found, otherwise returns #VALUE! error. For further information visit this FIND article.

Then we have found “i” within the second text of our stored value. And found the position.

Similarly searching “a” in the third and final value we have found the position.

Here we have found the position number or error.

Can’t we find the value along with the ISNA function? Let’s explore.

Let’s write the FIND formula within ISNA. 

This formula is for the first row of our dataset.

It returned FALSE. Since the FIND portion here didn’t provide #N/A (though we can see the finding value is not in the text), ISNA returned FALSE. 

Similarly, for the rest of the rows, we will find FALSE to be the answer.

II. Use of ISNA with Lookup function

We use various lookup functions to fetch values as per our criteria. The ISNA function can play a vital part while using these lookup values.

Here to show you examples, we have brought a dataset of a few items and their prices.

And we aim to set the item as lookup value criteria and find the price of that.

We will find the price using the VLOOKUP function. To know more about this function visit this VLOOKUP article.

The formula, to complete our task, will be

F4 is a lookup value, B4:C7 is the lookup_range, 2 is the column number, and 0 for an exact match.

F4 contains Pants and we have found the price of Pants. But if we set a lookup value that is not in the lookup range, then?

Let’s check it with an unlisted value. We have set Pajamas as the lookup value.

We have found that our formula returned #N/A since Pajamas are not available in the table.

This type of error value may be irritating as well as reader-friendly. To get rid of the problem we can use the ISNA function along with the lookup functions.

IF will be a supporting function for our formula. To know more about this function, visit this IF article. Our formula will be

Here first of all we have checked whether the VLOOKUP produced #N/A or not by ISNA. If yes then set to trigger “Not Available” otherwise perform the VLOOKUP operation.

Now instead of #N/A, we have found “Not Available”.

III. Find and Retrieve Values

We can find the incomplete values using the ISNA function. To show you an example we have listed few teams.

We have separately stored a couple of teams and keep it incomplete. To complete the list our formula will be

Here we have used INDEX and MATCH functions along with ISNA. You will get the required information regarding these functions from these articles; INDEX, MATCH.

The inner MATCH function compares the partial list against the full lists. For any #N/A the ISNA function TRUE and compare the TRUE lookup_value of outer MATCH. 

Then the INDEX function returns the value. Drag down to the lower cells to find the values. Since it is an array formula you need to press CTRL + SHIFT + ENTER.

IV. NOT logic with Criteria

We can find the value with multiple criteria. The ISNA function will help you to execute NOT logic.

Here we have a dataset of a few students with their respective gender and group.

If we need to find the number of students with gender Male and not address in NY (New York) and SF (San Francisco) then our formula will be

The MATCH function compares the value and sends the values as an array into the ISNA. Then for any #N/A the ISNA function TRUE.  

Then checks the gender and returns the value. This will be an array too. Multiplying these two arrays we find another array. And the SUMPRODUCT function adds them and provides the final output.

Here we have found that 2 male students are on the list not from NY and SF.

3. Things to Remember

1. You can insert #N/A directly into the function.

This will return TRUE.

2. Remember to not put the #N/A within double quotes

If so, this will be counted as a text string and the function will return FALSE.

Conclusion

That’s all for today. We have tried showing how you can use the ISNA function. You can use the function to check whether the not-available error occurs or not. Hope you will find this helpful.

Feel free to comment if anything seems difficult to understand. Let us know any of your ISNA function-related scenarios where you have stuck, we are ready to help.

Further Readings

• How to Use ISLOGICAL Function in Excel (4 Examples)
• How to Use ISTEXT Function in Excel (3 Examples)
• How to Use ISEVEN Function in Excel (2 Examples)
• How to Use ISODD Function in Excel (With 4 Examples)
• How to Use ISNUMBER Function in Excel (7 Examples)
• How to Use ISBLANK Function in Excel (2 Examples)
• How to Use ISERROR Function in Excel (With 5 Examples)