shakil
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.
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!.
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.
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.
=ISNA(FIND(“I”,B4))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
=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.
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
=IF(ISNA(VLOOKUP(F9,B4:C7,2,0)),"Not Available",VLOOKUP(F9,B4:C7,2,0))
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
=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.
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.
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.
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
- You can insert #N/A directly into the function.
This will return TRUE.
- 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.
