Shakil Ahmed
Excel provides several information functions to provide you with 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, ISNA in Excel. For this session, we are using Excel 365, feel free to use yours (at least 2003).
Download Practice Workbook
You are welcome to download the practice workbook from the link below.
Introduction to ISNA Function in Excel
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:
| Argument | Required/Optional | Explanation |
|---|---|---|
| value | Required | A value or expression to be checked for #N/A! error |
⦿ Versions:
Workable from Excel 2003.
3 Examples of Using ISNA Function in Excel
Example 1: Find #N/A Error with ISNA Function
To keep things simple, we have brought an example dataset of a few random data like the image below.
- The main function of the ISNA function is to find N/A in a cell.
- In cell B5, we have a #N/A data.
- And in the Output column of D, we will try to utilize the ISNA function to view if the data in cell B5 is available or not.
- As the data is Not Available (#N/A), the function returns the answer to the question (is the data in a cell available or not?) as TRUE.
- Again, for the second row, we have Text data, in cell B6.
- And we will use the ISNA function to see if the data in cell B6 is available or not. The result is FALSE because “Hello” is a text value.
- Now the rest of our Input has a data type other than #N/A and consequently the rest of the Output is also FALSE.
Example 2: Use of ISNA with VLOOKUP and IF Functions
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.
- The formula, to complete our task, will be:
=VLOOKUP(G4,B5:D8,2,0)Here,
- G4 is a lookup value,
- B5:D8 is the lookup_range,
- 2 is the column number, and
- 0 for an exact match.
- If G4 contains Pants and we will find the price of the Pants. But if we set a lookup value that is not in the lookup range, then?
- Let us 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 function will be used in our formula.
=IF(ISNA(VLOOKUP(G7,B5:C8,2,0)),"Not Available",VLOOKUP(G7,B5:C8,2,0))
Here first we have checked whether the VLOOKUP produced #N/A or not by ISNA. If yes, then set it to trigger “Not Available” otherwise perform the VLOOKUP operation.
Now instead of #N/A, we have found “Not Available”.
Important Note:
- You can use the XLOOKUP function instead to avoid the complicacy of using a formula with three functions. But XLOOKUP is only available for users of Excel 2016 or higher versions.
If you are an Excel 2013 user, you can try the IFNA function instead of the IF-ISNA combination.
Similar Readings
- How to Use ISLOGICAL Function in Excel (4 Examples)
- Use ISTEXT Function in Excel (8 Suitable Examples)
- How to Use ISEVEN Function in Excel (2 Examples)
Example 3: Use ISNA Function with NOT Logic
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 address.
- If we need to find the number of students with gender Male and not addressed in NY (New York) and SF (San Francisco) then our formula will be
=SUMPRODUCT((C5:C10=C12)*ISNA(MATCH(D5:D10,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 is 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.
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 you how you can use the ISNA function. You can use the function to check whether the not-available error occurs or not. I 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.
