Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Use ISNA Function in Excel (3 Suitable Examples)

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

Excel ISNA Function Overview


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.

ISNA Function syntax

⦿ Summary:

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

⦿ Syntax:

ISNA (value)

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

To show an example of usage of ISNA function

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

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.

Rest of our Input has a data type other than #N/A


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.

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.

Using a formula, to complete our task

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

Checking with an unlisted 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))

Use of ISNA with VLOOKUP and IF Functions

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


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.

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.  

Use ISNA Function with NOT Logic

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

Inserting #N/A directly into the function

This will return TRUE.

Returning a value TRUE

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

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.

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


Further Readings

Shakil Ahmed

Shakil Ahmed

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