Many times, while searching for a value in the spreadsheet we have to deal with #N/A errors. Every time the function like VLOOKUP, HLOOKUP, MATCH, or XLOOKUP formula cannot find the results, it returns a #N/A error. For dealing with such #N/A errors in excel, the excel ISNA function becomes very useful.

In this tutorial, we would uncover the ISNA formula in excel and how it works with examples.

Here we go 😎

**When to Use ISNA Function of Excel**

The expression ISNA represents “Hey! Is it a #N/A error?”. The ISNA formula of Excel returns a logical value ‘TRUE’ or ‘FALSE’ based on the condition that whether the supplied argument returns #N/A error.

ISNA Formula belongs to the Information functions group of Excel.

**Syntax and Arguements**

**=ISNA(value)**

The below point explain the required function argument for the ISNA function of Excel.

**value –**This argument could be a cell reference, range of cells or a formula result in which we want to check the presence of #N/A error.

**Points To Remember About ISNA Formula**

One should keep the following points in mind before actually using the ISNA function of Excel.

- The ISNA Formula does not return any error, whatever be the function argument.

- There can be only two possible output of the excel ISNA formula – TRUE or FALSE.

- We can use the ISNA Function with IF formula to modify and replace the #N/A error message with some other message.

- We can also pass the direct formula as the function argument like this =ISNA(A2/B2) and the formula will return a TRUE/FALSE if the formula result is a #N/A error.

**Examples – Actual Usage of ISNA Excel Function**

In this section of the blog, we will have a glance at some of the examples to learn how to use the Excel ISNA formula to find if #N/A exist or not.

**Ex.1 – Finding #N/A errors in excel – ISNA Formula**

In this example, we will be experimenting with different values and check the ISNA function results. Have a look at below values in column A.

To find if a cell contains #N/A error, simply use the following formula.

=ISNA(A2)

As a result, the function returns a logical TRUE in cell B2.

Copy the formula to the remaining cells in column B or use the Excel

Fill-handletool, to copy the formula to cells below.

**Explanation – **We have passed cell A2 as the function argument in the ISNA function. The function interpreted that the cell contains a #N/A error and thus returned a logical TRUE as its result.

It is important to note that the cells A3 and A5 also contain errors, but they are not #N/A error. Therefore, the function returned a logical FALSE for those values in cells A3 and A5. Similarly, cells A4 and A6 contain a text string (i.e. not #N/A error code), and therefore formula returns FALSE.

To check if the argument returns error (other than #N/A), use the **ISERR **or **ISERROR **formula in excel.

**Ex.2 – Representing #N/A Error Using IF formula with ISNA Function**

Below image contains details about the car brand and quantity available for renting.

Suppose, we want to check for the availability of the car for a particular brand. To achieve that we would be using the **VLOOKUP formula**, like this:

=VLOOKUP($A$9,$A$2:$B$6,2,0)

As a result, the formula has returned the quantity of Suzuki Alto Available in cell B9.

Now, what if we look for a car that is not available in the list of cars, say “Honda Brio”.

As a result, the VLOOKUP function returns a #N/A **excel error code**. This is because the car that we are looking for is not available in the list.

In such a scenario, the error message #N/A looks very weird. The error code is not self-explanatory in itself and may confuse a novice excel user as to what does this #N/A denote.

To mitigate this, we can use the ISNA excel formula in conjunction with the VLOOKUP and IF formula and turn the error message code in to something more meaningful.

=IF(ISNA(B12),"Car not available","There are "&B12&" cars available")

or

=IF(ISNA(=VLOOKUP(A9,A2:B6,2,0)),"Car not available","There are "&B12&" cars available")

This time the formula returns a text string “Car not available” when we look for a value that is not in the lookup array.

Let us see for a car that is available. i.e Tata Nano

This time the function returns the text string merged with the number of cars like this “There are 7 cars available”.

**Explanation – **The condition of the IF formula is ISNA(B12). The condition will return a TRUE if the Lookup function in cell B12 returns a #N/A error ( lookup value not found ). If the number of cars is successfully returned in cell B12, then the condition becomes FALSE. When the condition is TRUE ( value not found) then the function returns a text string “Car not available” or else the condition is FALSE and the function returns “There are “&B12″ cars available”. We have merged the text before and after the B12 (number of cars) using a glue called ampersand (&).

This brings us to the end of the ISNA Function blog.

Thank you for reading 😉

## RELATED POSTS

- ISERROR Function in Excel – Checking for Errors
- ISNUMBER Function of Excel – Checking If Cell Contains a Number
- ERROR.TYPE Function in Excel – Error Codes,Examples
- N Function in Excel – Usage and Examples
- ISERR Function in Excel – Checking If The Cell Contains An Error
- Using =IF() Function For Partial Match in Excel