Excel ISNA function checks the value within a cell, if a cell value is #N/A then it will return TRUE otherwise FALSE. The cell value could be a formula, name that refers to a cell or value. This function belongs to IS group of function. Excel ISNA Function always returns a Boolean value, TRUE or FALSE.
Why we use this?
This function returns TRUE for #N/A error otherwise returns FALSE.
What is the syntax for ISNA?
Parameters or arguments
Value (required) –a value within a cell to be checked
What it returns as result?
This function always returns a boolean value as TRUE or FALSE.
Use Excel ISNA Function with Vlookup Function
We have a table below to understand the #N/A error and how to exchange it with a suitable value.
With below image, we will understand how #N/A error happens and how to remove this error with a better option.
We have the dataset heading like- “Name”, “Zone” and “Value” and the related data is under the headings. See below table 1
Now in the second table, apply the VLOOKUP function to find the value of “Neeraj” in the second column and the result is “44”. See VLOOKUP formula in the above image.
Again we apply the same function to find the value of “Mukesh” in the second column. And this time result is #N/A. This is because “Mukesh” does not exist in the table no.1.
So, VLOOKUP Function will return #N/A error, if a lookup value is not existing in the dataset.
#N/A error does not look good in the dataset, better to rename it with “Not available” which is understandable.
We will use a function like – “IF + ISNA + VLOOKUP” to change the cell value from #N/A with “Not available”.
See below how function will work.
=IF(logical_test, [value_if_true], [value_if_false]
Put in place of logical_test – ISNA (value)
Apply VLOOKUP function in place of –“value” in ISNA function
[value_if_true] – type “Not available”, if value is #N/A error,
[value_if_false] – type same VLOOKUP function again for value existing
The whole function will look like: =
=IF(ISNA(VLOOKUP(A25,A13:C18,3,FALSE)),”Not available”, VLOOKUP(A25,A13:C18,3,FALSE))
Press enter, if values are not existing, then the formula will show “Not available” otherwise the values will show. See in the image, table no.2.