How to Use ISNA Excel Formula: Functions, Examples and Writing Steps


Home >> Excel Tutorials from Compute Expert >> Excel Formulas List >> How to Use ISNA Excel Formula: Functions, Examples and Writing Steps



In this tutorial, you will learn how to use the ISNA excel formula completely. We will discuss its functions, examples, writing steps, input, use cases, and alternatives.

When working in excel, we sometimes need to anticipate an error when we don’t find our data using a formula. ISNA can help you to identify this error value so you can decide what to do with it.

Want to learn more about ISNA usage in excel? Read all parts of this tutorial!







What is ISNA?

ISNA is an excel function that can identify a #N/A (Not Available) error. This formula is one of the IS formulas that excel provides to identify various data types.



ISNA Function in Excel

ISNA can help us to identify a #N/A error type and give us TRUE/FALSE depending on the identification result. #N/A is the error we get when excel doesn’t find the data we want to find using a lookup formula.



ISNA Result

ISNA result is a logic value that is either TRUE or FALSE. ISNA gives us TRUE if the data/formula we input has a #N/A error value. Otherwise, it gives us FALSE.



Excel Version from Which We Can Use ISNA Excel Formula

We can use ISNA in excel since excel 2003.



The Way to Write It and Its Input

The general writing form of ISNA in excel is as follows.

= ISNA ( value )


The value input there is the data/formula we anticipate the #N/A error value from. We usually input a lookup formula in ISNA, such as VLOOKUP, HLOOKUP, or MATCH. That is because we almost always get a #N/A error value from these kinds of formulas.



Example of Its Usage and Result

Here is an ISNA implementation and result example in excel.

How to Use ISNA Excel Formula: Functions, Examples and Writing Steps - Screenshot of the ISNA Implementation Example

In this example, we use ISNA to identify the result of the HLOOKUP we write to get a banana stock quantity. For the identification process, we input the cell coordinate where we write our HLOOKUP to ISNA.

In the HLOOKUP reference table, there isn’t a banana stock quantity that our HLOOKUP wants to find. Thus, our HLOOKUP returns a #N/A error. Because of that, when we input the HLOOKUP result to our ISNA, we get a TRUE logic value from it.



Writing Steps

After discussing the ISNA writing form, input, and implementation example, now let’s discuss the steps to write it. As the ISNA writing form is quite simple, we only need to follow a few steps here to write ISNA completely.

  1. Type an equal sign ( = ) in the cell where you want to put your ISNA result in

    How to Use ISNA Excel Formula: Functions, Examples and Writing Steps - Screenshot of Step 1

  2. Type ISNA (can be with large and small letters) and an open bracket sign after =

    How to Use ISNA Excel Formula: Functions, Examples and Writing Steps - Screenshot of Step 2

  3. Input the data or the formula from which you want to identify the #N/A error value

    How to Use ISNA Excel Formula: Functions, Examples and Writing Steps - Screenshot of Step 3

  4. Type a close bracket sign

    How to Use ISNA Excel Formula: Functions, Examples and Writing Steps - Screenshot of Step 4

  5. Press Enter
  6. Done!

    How to Use ISNA Excel Formula: Functions, Examples and Writing Steps - Screenshot of Step 6






ISNA Combination with Other Formulas 1: IF ISNA VLOOKUP Yes No

As you may have already suspected, since the ISNA result is TRUE/FALSE, we usually combine its writing with another formula. Two of the formulas we usually combine with ISNA are IF and VLOOKUP.

By combining ISNA, IF, and VLOOKUP, we can identify the availability of particular data and get value from the identification result. The value we produce from the formulas’ combination is often yes or no. We surely can produce other values besides yes or no. However, these values are often the standard ones we use.

Yes usually means the data is available and no usually means the data isn’t available. The general writing form of IF, ISNA, and VLOOKUP combination to produce this kind of result is as follows.

= IF ( ISNA ( VLOOKUP ( lookup_reference , reference_cell_range , result_column_index , lookup_mode ) ) , “No” , “Yes” )


We input our VLOOKUP to ISNA so ISNA can identify the VLOOKUP result, whether it is #N/A or not. If it is #N/A, then we set our IF to produce a “No” text. Otherwise, we set our IF to produce a “Yes” text.

Here is the implementation example of the IF, ISNA, and VLOOKUP combination to produce yes or no in excel.

How to Use ISNA Excel Formula: Functions, Examples and Writing Steps - Screenshot of the IF, ISNA, and VLOOKUP Combination Implementation Example

As you can see in the example, we get a “No” from the IF, ISNA, and VLOOKUP combination we write.

That is because our VLOOKUP doesn’t find the data we want and that makes ISNA produces TRUE. This, in turn, makes IF produce its TRUE result, which is a “No”.



ISNA Combination with Other Formulas 2: IF ISNA MATCH

Another ISNA formulas combination we might use is IF ISNA MATCH.

The function of MATCH is to find the row/column position of data we want to find. Therefore, in a way, the result we get from IF, ISNA, and MATCH combination is similar to IF, ISNA, and VLOOKUP. You can think of IF, ISNA, and MATCH combination as an alternative to the IF, ISNA, and VLOOKUP.

Here is the three formulas’ combination writing form if we also use “Yes” or “No” as our final result.

= IF ( ISNA ( MATCH ( lookup_value , cell_range , [ match_type ] ) , “No” , “Yes” )


If we use MATCH instead of VLOOKUP, remember that MATCH can only process one row/column cell range. If we input a cell range larger than one row/column, then our IF can produce a #N/A error!

Make sure also that we input a direct lookup value to our MATCH. That means if we want to find a banana stock quantity, then we should input the stock quantity to MATCH. Inputting “Banana” like in a VLOOKUP will surely make our MATCH produces a #N/A error.

Here is the implementation example of IF, ISNA, and MATCH formulas combination in excel. We use “There Is” and “There Isn’t” as our IF result.

How to Use ISNA Excel Formula: Functions, Examples and Writing Steps - Screenshot of the IF, ISNA, and MATCH Combination Implementation Example

As you can see in the example, we get “There Is” from our IF, ISNA, and MATCH combination. That is because the data we want to find using MATCH, “Michael”, is available in the name column of the table.

As we want to find our data in the name column, we input this column cell range into our MATCH. We also input 0 in the MATCH because we want to find an exact match for the data we look up.

Our MATCH produces the “Michael” row position in the name column. As our MATCH doesn’t produce a #N/A error, that makes our ISNA produces FALSE. This causes our IF to produce its FALSE result as the final result, which is “There Is” in the example.




Identify All Types of Errors: ISERROR

Want to produce a TRUE logic value for all types of error not just a #N/A error? If that is what you need, then don’t use ISNA to help you identify your data/formula value. Use ISERROR instead.

ISERROR will produce TRUE for any type of error that its input has. The general writing form of ISERROR in excel is similar to ISNA.

= ISERROR ( value )


Just input the data/formula which value you want to examine to ISERROR. ISERROR will produce TRUE if the data/formula has an error value!

For a better understanding of ISERROR, here is its implementation example in excel. We will compare its implementation result with ISNA too so you can see their differences clearly.

How to Use ISNA Excel Formula: Functions, Examples and Writing Steps - Screenshot of the ISERROR Implementation Example and Its Result Comparison with ISNA

In the example, we input the same error value to ISERROR and ISNA in each row.

As you can see, ISERROR produces TRUE for all error values we input in it. Meanwhile, ISNA only produces TRUE for the #N/A error while it produces FALSE for other error values. That is because it can only identify the #N/A error.

Therefore, if you need to identify all error values in your formula, not just #N/A, use ISERROR instead of ISNA!



Exercise

After you have learned how to use the ISNA excel formula completely, now let’s do an exercise. This is so you can understand the tutorial lessons more practically!

Download the exercise file and do all the instructions. Download the answer key file to check your answers if you have finished the exercise and want to check your answers. Or, probably, if you are confused about how to do the instructions

Link to the exercise file:
Download here

Instructions:

Use ISNA to do all the instructions!
  1. Fill column no. 1 cells with TRUE/FALSE. The logic value depends on whether the parallel cell in column 1 contains #N/A or a number!
  2. Fill column no. 2 cells with V or X. The fill depends on whether the parallel cell in column 2 contains #N/A or a number! Use IF together with ISNA to do this instruction!
  3. Sum the parallel cell values in columns 1, 2, and 3. If there is a cell that contains a #N/A error, then assume it is 0 in the sum process! Use IF together with ISNA to do this instruction!

Link to the answer key file:
Download here



Additional Note

As ISNA only produces a TRUE/FALSE logic value, you almost certainly use it within an IF formula in excel.



Related tutorials you should learn:



Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!


Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!



Follow our tutorial content also on




Hand-Picked CE Tutorials

Excel Calculation

How to Sum in Excel

Subtraction in Excel

Multiplication in Excel

Division Excel Calculation

Average Excel Calculation



Excel Formula

VLOOKUP Excel Formula

Excel IF Function

SUM Formula in Excel

COUNTIF Formula in Excel

COUNT Function in Excel



Excel Tips and Trick

How to Print in Excel

Convert Number to Text Excel

Excel Worksheet Definition

Excel Range Definition

How to Add Columns in Excel



Excel Consultation

Contact Us

Privacy Policy

Affiliate Disclosure

Terms & Condition



© 2021 Compute Expert