In Google Sheets, getting #N/A errors can ruin your calculations because they will cause many of your formulas to break if you include them in any kind of calculation.
Luckily there are a few functions we can use to control these # N/A errors. One such function is the ISNA function.
In this tutorial, I will show you how to use the ISNA function in Google Sheets.
Table of Contents
ISNA Function
The ISNA function will check whether a value is an #N/A error. If it is an #N/A error, the function will return TRUE, otherwise it will return false.
The syntax of this function is:
=ISNA(value)
- Value – this is the value or cell reference that you want to check for an #N/A error.
Here is an example of how this would be used in a spreadsheet:
In this example data, I have a lookup function in column B, and some of the cells contain #N/A errors. You can see that my ISNA function in column C returns TRUE for the cells containing the errors and FALSE for everything else.
This function only has one argument, so it is very simple to do. But the ISNA function is not often used by itself, it is usually used with another function such as the IF function. Below we will take a look at how these would be used together.
Using IF and ISNA Together
Like I was saying earlier, the ISNA function is usually used with IF statements. By combining these two functions you can return a certain value if your ISNA function returns TRUE, and if it returns FALSE you can return a different value.
Here is an example of how this can be used using my previous data:
The exact formula I used is:
=IF(ISNA(VLOOKUP(A2,Sheet2!$A$2:$B$4,2,0)),”No Hours Worked”,VLOOKUP(A2,Sheet2!$A$2:$B$4,2,0))
To break this down into the general syntax this formula is:
=IF(ISNA(value)),value_if_true,value_if_false)
You can see that by nesting these two functions together if there is an #N/A error in my data, I can return a specific value.
Closing Thoughts
As you continue to work with spreadsheet programs, controlling for the different types of errors that you will get becomes very important.
If you want your calculations to work properly, sometimes you have to control for errors like the #N/A error.
The ISNA function is only one way to do this. There are other ways as well.
More Google Sheets Tutorials:
How to Use the IFNA Function
How to Do Multiple IF Statements
How to Hide Errors