How to Use The IFNA Google Sheets Function: The Only Tutorial You Need

Google Sheets uses various strings to represent errors, for example #N/A, #REF, #DIV/0, etc. These strings are meant to help the creator of the worksheet audit their formulas. They also help stop an operation from computing further in case an error is encountered.

As helpful as these error strings are, they can sometimes be an eyesore for a regular viewer looking at your worksheet. Inexperienced users might also not be able to understand what the symbols mean. As such, it is always a good idea to plan ahead and provide alternatives in case a formula returns one of these errors.

The #N/A error is returned when a function cannot find a search key in a given range. It is usually returned by functions like VLOOKUP, HLOOKUP, IFS, and SUMIFS and can be handled using the IFNA Google Sheets function.

In this tutorial, we will show you how to use the IFNA function in Google Sheets to take care of #N/A errors.

To demonstrate the use of the IFNA function, we will look at two common use-cases where the IFNA function can be quite helpful. We will finish the tutorial with a brief comparison between the IFNA and ISNA functions.

What Does the IFNA Google Sheets Function Do?

The Google Sheets IFNA function evaluates a given value. If the value is an #N/A error, then the IFNA function performs a specified action. In this way, the function is a kind of error handler.

Syntax for the IFNA Function

The syntax for the Google Sheets IFNA formula is as follows:

IFNA(value, value_if_na)

Here,

• value can be a direct value, a formula, or a cell reference
• value_if_na is a value that will be returned if the first parameter returns an #N/A! error.

How to Use IFNA Function in Google Sheets

You should use the IFNA function wherever you expect that a formula or function might return an #N/A error. The function basically lets you assign an alternate value or perform an alternate action if a formula results in an #N/A error.

The #N/A error simply means that a value is ‘Not Available’. This helps indicate to the calling function that the range or cell it is trying to access cannot be accessed, so the function should stop calculation.

Let us look at two use-cases in which the IFNA function can be quite helpful.

Use-Case 1: When You Want the Google Sheets IFNA VLOOKUP to Return a Particular Text in Case of #N/A Error

Consider the lookup table shown in the image below:

 

 

Say we want to use a separate table to access cells from the above lookup table based on the ID number in column E:

 

 

We would then need to use the VLOOKUP function as follows:

 

In the above image, notice that the value 1345 does not exist in the lookup table. So the VLOOKUP function simply returns an #N/A error, since the value the function is trying to access is ‘Not Available’.

 

To make the worksheet easier for the user to read and understand, it might be helpful to have a text that says ‘Not found’ or ‘Not available’ instead of ‘#N/A’.

This is easily accomplished using the IFNA function. All you need to do is:

1. Wrap an IFNA function around the VLOOKUP formula
2. Pass the alternate text as the second parameter of the function.

For example, you can replace the formula in cell F2 with:

=IFNA(VLOOKUP(E2,$A$1:$B$7,2,FALSE), “Not found”)

Here’s what we get when we apply the IFNA and VLOOKUP Google Sheets functions together to all cells of column E:

 

 

You could also choose to display a blank cell instead of the ‘#N/A’ error. In that case, simply pass a blank text string as the second parameter of the IFNA function:

=IFNA(VLOOKUP(E2,$A$1:$B$7,2,FALSE), “”)

Here’s what we get when we apply the above formula to all cells of column E:

 

Use-Case 2: When You Want to Lookup Some Other Sheet if a VLOOKUP Results in #N/A! Error

Instead of just returning a given string, the IFNA function can also be used to specify an alternative action to be performed if the #N/A error is encountered.

For example, consider a case where VLOOKUP extracts a value from a lookup table that is in Sheet1:

 

You might have lookup tables in multiple sheets. So if VLOOKUP does not find the lookup value in the specified sheet, you might want to look into a different sheet.

Let’s say we want to VLOOKUP the table in Sheet2 instead if the search string does not exist in Sheet1.

 

For this, we can specify another lookup formula in the second parameter of the IFNA function, as follows:

=IFNA(VLOOKUP(A2,Sheet1!$A$1:$B$7,2,FALSE),VLOOKUP(A2,Sheet2!$A$1:$B$7,2,FALSE))

Now notice that the value 1345 does not exist in Sheet1, but it does exist in Sheet2. The above formula will thus return the value “Tammy”, as shown below:

Difference Between the Google Sheets IFNA and Google Sheets ISNA Functions

The ISNA function is another function that can be used to handle #N/A errors. However, there are slight differences between the IFNA and ISNA functions:

• While the IFNA function takes two arguments, the ISNA function takes just one – the value that is to be evaluated for #N/A error. So its syntax is =ISNA(value).
• The IFNA function returns the value to be displayed or alternate formula to be executed in case of #N/A error, while the ISNA function returns a TRUE/FALSE value based on whether the argument passed is an #N/A value or not.
• In this way, while the IFNA function handles both the detection and correction of an #N/A value, the ISNA function only handles the detection.

To handle errors, the ISNA function needs to be nested in an IF or IFS function, for example:

=IF(ISNA(VLOOKUP(E2,$A$1:$B$7,2,FALSE)),"Not found",VLOOKUP(E2,$A$1:$B$7,2,FALSE))

 

Points to Remember About the IFNA Function in Google Sheets

• The second parameter for this function is optional, so if you do not pass a value for it, the function returns an empty string when it encounters a #N/A error.
• The Google Sheets IFNA function specifically targets the Google Sheets if #N/A error. So all other errors, like the #DIV/0, #REF! etc. are ignored by it. If you want to target all errors, you can use the IFERROR function instead.
• It is recommended not to replace the IFNA error with IFERROR if you are expecting other errors. This is because the value #N/A only tells that a match for the search key you are looking for has not been found in the given range. However, using IFERROR eliminates your chance to audit your formula for other errors.

Click here to know more about Formula parse errors and how to fix them.

In this tutorial, we looked at the IFNA google Sheets function, its syntax, and some use-cases. We also explained in which cases it is advised to opt for IFNA, rather than the IFERROR or ISNA functions. We hope this was helpful.