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
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.
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-handle tool, 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.
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:
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")
=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 😉