Sometimes when doing formulas in Google Sheets you will end up with an #N/A error. This can make it difficult to do calculations with cells that are returning this error.
Luckily, there is a built-in function we can use to get rid of these errors.
In this tutorial, I will show you how to use the IFNA function in Google Sheets
How to Use the IFNA Function
The IFNA function will evaluate a value and if the value is an #N/A error it will return a value that you specify in your formula.
The syntax of this function is:
=IFNA(value, value_if_na)
- value – this is the value that you want to check to see if it is an #N/A error
- value_if_na – this is the value to return if your checked value is an #N/A error
The IFNA function is most often used to wrap another formula that commonly gives #N/A errors. For example formulas like VLOOKUP, HLOOKUP, IFS, and MATCH, will often give #N/A errors if there are no matching values.
Let’s take a look at this example:
Here I have a basic VLOOKUP function that returns #N/A errors because there is no matching data for the lookup. I also have a sum at the bottom trying to sum up all the data but because there is #N/A errors my math operation does not work. This is why #N/A errors need to be fixed.
To fix this problem in my spreadsheet, I will wrap my VLOOKUP in an IFNA function to return a 0 if my value is #N/A.
Here are the steps to do this:
1. To work in this formula, the IFNA function should be at the very beginning of your formula, so here I am taking my existing VLOOKUP and placing an IFNA at the beginning
2. At the end of my VLOOKUP, I add a comma, and then place a zero (0) inside quotation marks. Add a closing parenthesis “)” and then press Enter to calculate the formula
3. Paste your formula down to any other rows that you need to and your #N/A errors should be replaced with zero’s
You can see in the example above that after replacing all my #N/A’s with zero’s my SUM function worked properly.
The complete formula I have used in my example is this:
=IFNA(VLOOKUP(A2,Sheet2!$A$1:$B$6,2,0),“0”)
This is just one way to use the IFNA function. You can also return blanks by placing nothing inside your quotation marks as the value_if_na argument in your function.
Closing Thoughts
This is just one example of how to use IFNA function in your spreadsheet. In this example, we have used it to wrap our VLOOKUP function to control for errors.
The process for using it with the other functions like HLOOKUP, IFS, MATCH, and others is the same. You will wrap your function in the IFNA function and set the value you want to return if your formula calculates to an #N/A error.
Hopefully, by now you can see that this is a very useful function that you should be using in your spreadsheets.
More Google Sheets Tutorials:
How to Use the ISDATE Function
How to Use the IMPORTRANGE Function
How to Use the ISODD Function
How to Use the ISNA Function