ISNA in Excel

What is ISNA in Excel?

ISNA is one of the built-in excel functions, which returns true when there is a #N/A error in the cell. ISNA function is also known as information function. You can utilize the ISNA function along with the IF function test to display a message when the error appears in those cells. 

Syntax of Excel ISNA

ISNA (value)

Value – This is an expression where you enter the cell value that needs to be evaluated and tested. This is a required parameter.

If the value that you enter is #N/A error value, then it returns true or else it returns false.

Let’s look at a sample:

The G2 cells return true because the value in the F2 cell is #N/A error. The formula applied in the G2 cell is shown in the formula bar.

Understanding ISNA in Excel

From this, we can conclude that the ISNA function returns true only when the cells hold #N/A error.

ISNA is one of the inbuilt functions and it is applicable in all versions of excel. (Excel 2011 for Mac, Excel for Office 365, Excel XP, Excel 2000, Excel 2003, Excel 2007, Excel 2013, Excel 2016, Excel 2019)

Excel ISNA Function

ISNA function is one of the worksheet functions (WS). Follow the steps to implement the ISNA function from the Formulas tab:

1: Go to the Formulas tab 

2: Click on the insert function icon.

Note: A dialog box appears, type the keyword “ISNA” in the search for a function box. Choose Information from the drop-down list under select a category. The ISNA function appears in the select a Function box. 

3: Click on ISNA Function.

4: Press OK.

Note: Another dialog box appears asking for the cell value.

5: Fill the cell reference in the value and press ok.

Value: Here, the user wants to check whether the value in cell F2 contains the #N/A error or not. Hence, the cell reference is “F2”, i.e. =ISNA(F2).

Here, =ISNA(F2) will return true if the value is an #N/A error or else it returns false.

Cell F2 contains the #N/A error, hence it will return ‘true’ as an output in the F4 cell.

ISNA function along with IF, Vlookup function

Formula: 

“IF(ISNA(VLOOKUP(D3,A1:B5,2,FALSE)),”no data available”,VLOOKUP(D3,A1:B5,2,FALSE))”

This function has three parts:

1. IF(ISNA(VLOOKUP(D3,A1:B5,2,FALSE))

It means if the function doesn’t find the value of cell D3 in the cells A1:B5 then display the second part.

2. “no data available “

It instructs the cell to fill with ‘no data available’. The user can write anything between quotation marks to display it instead of a blank cell.

3. VLOOKUP(D3,A1:B5,2,FALSE))

It means if the function finds the value then it can perform the vlookup activity. 

Observe the formula of the E3 cell in the formula bar. The formula means that if the function doesn’t find the value of cell D3 in the table array, then display “no data available”.

Note: You have to enter the vlookup content twice for IF- ISNA formula to work.

Things to Remember:

1. The ISNA function is one among the IS functions.

2. ‘ISNA’ function verifies the results of the formula in a cell for errors. 

3. The function helps in identifying and handling #N/A! errors.

Summary

1. ISNA function helps in identifying the #N/A error in the cells of the spreadsheet.

2. ISNA function is also a part of IS functions.

3. It is more or less an elegant way to handle the error.