Excel ISNA Function (Table of Contents)
ISNA in Excel
- ISNA Function is a pre-built function in excel that is categorized under INFORMATION Function & It is IS group or family of functions.
- Usually #N/A! an error appears when a value is not available in the data range.
- ISNA Function along with other function allows us to substitute a different value in place of the error i.e. modification of text string with a customized one.
- Most commonly used a function for data analysis in finance domain.
- ISNA Function easy to use & flexible function where we need to enter an only Single argument, ISNA Function in Excel is used as worksheet & VBA Function in excel.
- With the help of ISNA Function, we can inform Excel to leave the cell blank or to display any specified or desired text like “Value not found” instead of displaying #N/A! error.
The ISNA Function in Excel will return TRUE if the value is an #N/A error and return FALSE if it is not present.
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) – It is the value or expression that needs to be tested are entered manually or define 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 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 ERROR_VALUES column (column D), Here With the help of ISNA Function in Excel, I need to find out #N/A error in column D.
Let’s apply ISNA function in cell “E8”. Select the cell “E8” where ISNA function needs to be applied.
Click the insert function button (fx) under 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 #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 it will return FALSE if it doesn’t contain #N/A error.
The cell D8 does not contain #N/A error, it will return FALSE as an output in the cell “E8”.
To get the output for complete dataset, click inside cell E8 and you’ll see the cell selected, then Select the cells till E14. So that column range will get selected, once it got 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 student column (column M), our objective here in this table, is to find student score, based on his name i.e. student name (refer table 1) by using 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 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 VLOOKUP function.
A dialog box appears where arguments for 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. So that column range will get selected, once it got selected click on Ctrl + D so that the VLOOKUP formula is applied to the whole range.
Here, #N/A error is returned in cell N10 & N12. because 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 IF function in VLOOKUP function to avoid #N/A error. Where #N/A error value is replaced with a customized text string.
Suppose I want a customized message i.e. “Not found” instead of #N/A error.
Let’s work on this in table 3, Below mentioned ISNA function with IF is used with VLOOKUP function to avoid #N/A error.
Three parts of formula are
The first part of formula means if the function doesn’t find the value of cell P7 in table range J6:K17 then display second part.
- “Not found”
Display “Not found” instead of a #N/A.
If the function finds the value then perform VLOOKUP formula as usual.
Let’s apply this formula in cell “Q7”.
The Result will be :
To get the finalized data for other students, need to apply this formula for a whole date range. click inside cell “Q7”, and you’ll see the cell selected, then Select the cells till Q12. So that column range will get selected, once it got 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 #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.
You can download this ISNA Function Excel template here – ISNA Function Excel Template
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 downloadable excel template. You can also go through our other suggested articles –