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

Overview - Excel ISNA Function

Practice Workbook

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

ISNA Function

1. Basics of ISNA

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

Syntax - Excel ISNA Function

Summary

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

Syntax

ISNA (value)

Arguments

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

Versions

Workable from Excel 2003.

2. Uses of ISNA

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.

Dataset FIND - Excel ISNA Function

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.

FIND I in text - Excel ISNA 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.

FIND i in text - Excel ISNA Function

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

FIND a in text - Excel ISNA Function

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. 

=ISNA(FIND(“I”,B4))

This formula is for the first row of our dataset.

ISNA with FIND - Excel ISNA Function

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.

FIND - ISNA examples - Excel ISNA Function

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.

Lookup example dataset - Excel ISNA Function

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

=VLOOKUP(F4,B4:C7,2,0)  

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

VLOOKUP formula - Excel ISNA Function

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.

Not listed value - Excel ISNA Function

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

=IF(ISNA(VLOOKUP(F9,B4:C7,2,0)),"Not Available",VLOOKUP(F9,B4:C7,2,0))

VLOOKUP ISNA formula - Excel ISNA Function

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.

Complete the partial - Excel ISNA Function

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

=INDEX(B4:B10,MATCH(TRUE,ISNA(MATCH(B4:B10,D4:D5,0)),0)) 

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. 

Complete the incomplete - Excel ISNA Function

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 found the value with multiple criteria. The ISNA function will help you to execute NOT logic.

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

Dataset for NOT logic - Excel ISNA Function

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

=SUMPRODUCT((C4:C9=C12)*ISNA(MATCH(D4:D9,C13:D13,0))) 

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.  

NOT logic ISNA- Excel ISNA Function

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.

Direct input - Excel ISNA Function

This will return TRUE.

Direct input result - Excel ISNA Function

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

Text input - Excel ISNA Function

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

Text input result- Excel ISNA Function

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.

shakil

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo