Excel ISNA Function (Table of Contents)
ISNA in Excel
IsNA function is one of those functions used to find whether a cell has any error. And this only detects one type of error, which is #N/A which usually occurs when we try to look up at any value from the lookup range, and that value is not there. So, in the end, we get #N/A which summarizes that the value which we want is “Not Available”, and the IsNA function detects if the process would get #N/A or not by returning the TRUE and FALSE statement.
ISNA Formula in Excel:
Below is the ISNA Formula in Excel:
ISNA Formula in Excel has one compulsory parameter: i.e. value.
- Value (compulsory argument) – The value or expression that needs to be tested is entered manually or defined variables or a cell reference to use instead.
How to Use the ISNA Function in Excel?
ISNA Function in Excel is very simple and easy to use. Let us understand the working of ISNA Function in Excel by some ISNA Formula in Excel example.
Example #1 – ISNA Function
In the below-mentioned table. I have the various type of error values in the ERROR_VALUES column (column D); here, With the help of the ISNA Function in Excel, I need to find out #N/A error in column D.
Let’s apply the ISNA function in cell “E8”. Select the cell “E8” where the ISNA function needs to be applied.
Click the insert function button (fx) under the formula toolbar; a dialog box will appear, type the keyword “ISNA” in the search for a function box & under select a category dropdown, select “INFORMATION” so that the ISNA function will appear in select a Function box. Double click on ISNA Function.
A dialog box appears where arguments for ISNA Function needs to be filled or entered, i.e. =ISNA(value)
Value: Here, I have to check whether the value in cell D8 contains the #N/A error or not. I have to mention the cell reference “D8”, i.e. =ISNA(D8).
Here, =ISNA(D8) will return TRUE if the value is an #N/A error or return FALSE if it doesn’t contain #N/A error.
The cell D8 does not contain the #N/A error; it will return FALSE as an output in the cell “E8”.
To get the output for the complete dataset, click inside cell E8 and you’ll see the cell selected, then Select the cells till E14. Once it got selected, column range will get selected, click on Ctrl + D so that the ISNA formula is applied to the whole range.
E13 cell returns TRUE value because the D13 cell contains #N/A Error.
Example #2 – ISNA & IF Function with VLOOKUP Function to Avoid #N/A Error
In the below-mentioned table1. I have the list of students in the student column (Column J) & their scores in the Score column (Column K).
Table 2 contains a list of student name in the student column (column M); our objective here in this table is to find student score based on his name, i.e. student name (refer to table 1), by using the VLOOKUP Function.
Prior to applying a VLOOKUP formula, You should be aware of it. Vertical lookup or VLOOKUP references vertically-aligned tables and quickly finds data in relation to the value the user enters.
Let’s apply VLOOKUP Function in cell “N7”. Select the cell “N7” where VLOOKUP Function needs to be applied.
Click the insert function button (fx) under the formula toolbar; a dialog box will appear, type the keyword “VLOOKUP” in the search for a function box; VLOOKUP Function will appear in select a Function box. Double click on the VLOOKUP function.
A dialog box appears where arguments for the VLOOKUP function needs to be filled or entered.
The syntax for the VLOOKUP function is:
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value: the value you want to look up, i.e. “M7” or “CINDER”.
- table_array: range where the lookup value is located, i.e. select table1 range J6:K17.
- col_index_num: column number in a table array from which the matching value should be returned. Here the student score in table 1 is in the second column, i.e. 2.
- range_lookup: FALSE for an exact match or TRUE for an approximate match. Select 0 or false.
=VLOOKUP(M7,J6:K17,2,0) returns the score of cinder, i.e. 77.
To get the finalized data for other students, click inside cell N7 and you’ll see the cell selected, then Select the cells till N12. Once it got selected, column range will be selected, click on Ctrl + D so that the VLOOKUP formula is applied to the whole range.
Here, the #N/A error is returned in cell N10 & N12 because the Vlookup function fails to find a match to the supplied lookup_value in the reference table range.
#N/A error looks odd. For a better outlook of a final dataset without N/A error. ISNA function is used with the IF function in the VLOOKUP function to avoid the #N/A error where the #N/A error value is replaced with a customized text string.
Suppose I want a customized message, i.e. “Not found” instead of the #N/A error.
Let’s work on this in table 3; below mentioned ISNA function with IF is used with the VLOOKUP function to avoid the #N/A error.
Three parts of the formula are
The first part of the formula means if the function doesn’t find the value of cell P7 in table range J6:K17, then display the second part.
- “Not found.”
Display “Not found” instead of a #N/A.
If the function finds the value, then perform the VLOOKUP formula as usual.
Let’s apply this formula in cell “Q7”.
The Result will be :
To get the finalized data for other students, we need to apply this formula for a whole date range. Click inside cell “Q7”, and you’ll see the cell selected and then Select the cells until Q12. Once it got selected, column range will get selected, click on Ctrl + D so that the formula is applied to the whole range.
It will return a customized text string mentioned in the formula, i.e. “Not found” instead of the #N/A error in the cell N10 & N12.
Things to remember about the ISNA Function in Excel
- ISNA argument value can be a blank (empty cell), text, number, error, logical, or reference value, or a name referring to any of these that you want to test.
- IS functions are significant in formulas for testing the outcome of a calculation. If it is combined with IF logical conditions, it will help out to find the error value.
This has been a guide to ISNA in Excel. Here we discuss the ISNA Formula in Excel and How to use ISNA Function in Excel, along with practical examples and a downloadable excel template. You can also go through our other suggested articles –