When working with formulas in Google Sheets, you may see a #N/A error. It may be difficult to perform calculations with cells that are producing this error as a result of this.
Fortunately, we can utilize a built-in method to eliminate these problems and this is IFNA function.
IFNA Function and Steps to Use It
This function will return the value that you’ll specify in your formula if value will be an #N/A error. The syntax for this function is –
=IFNA(value, value_if_na) here,
value – this is the value you’re looking for to see if it’s a #N/A mistake.
value_if_na is a #N/A error, value if #N/A is the value to return.
The IFNA function is frequently used to wrap another formula that frequently results in #N/A errors. If there are no matching values, formulas like VLOOKUP, HLOOKUP, IFS, and MATCH will frequently return #N/A errors.
Let’s understand this more clearly with an example.
- Write the IFNA function in your desired cell. But after =IFNA, put a basic VLOOKUP method that returns #N/A errors because there is no matching data for the lookup. I also have a sum at the bottom that attempts to total up all of the data, but my math operation fails due to #N/A errors. This is why #N/A errors must be corrected.
With my spreadsheet, I’ll wrap my VLOOKUP in IFNA function, which’ll return a 0 if my value is #N/A.
2. Now, I’ll put a comma at the end of my VLOOKUP and then a zero (0) inside quotation marks. To calculate the formula, add a closing parenthesis “)” and then press Enter.
3. Copy and paste your formula to any more rows you require, and your #N/A errors should be replaced with zeros.
This is only one example. Alternatively you can also return blanks by using the value if na argument in your function with nothing inside the quotation marks.
This is only one way to use the IFNA function in a spreadsheet. We used it to wrap our VLOOKUP method in this example to check for problems.
It’s the same procedure for using it with other functions like HLOOKUP, IFS, MATCH, and others. If your formula returns a #N/A error, you’ll wrap it in the IFNA function and set the value you want to return.
By now, you should be able to see why this is a highly important function to have in your spreadsheets.