Check Sheet Logo
Check Sheet

IFNA

The IFNA function checks if a value is an #N/A error and returns a specified value if it is. This is useful when you want to replace #N/A errors with a specific value. The function takes two arguments, the first argument is the value to check for #N/A error and the second argument is the value to return if the first argument is #N/A error.

Usage

Use the IFNA formula with the syntax shown below, it has 2 required parameters:

=IFNA(value, value_if_na)
Parameters:
  1. value (required):
    The value to check for #N/A error.
  2. value_if_na (required):
    The value to return if the first argument is #N/A error.

Examples

Here are a few example use cases that explain how to use the IFNA formula in Google Sheets.

Replacing #N/A errors

When working with large datasets, you may encounter #N/A errors. Instead of leaving them as errors, you can use the IFNA function to replace them with a specific value.

Dealing with missing data

The IFNA function is useful when dealing with missing or incomplete data. It allows you to replace #N/A errors with a value that makes sense in your context.

Cleaning up data

In some cases, data may contain #N/A errors that need to be cleaned up before further processing. The IFNA function can be used to replace these errors with a value that can be further processed.

Common Mistakes

IFNA not working? Here are some common mistakes people make when using the IFNA Google Sheets Formula:

Incorrect number of arguments

One common mistake is providing an incorrect number of arguments to the IFNA formula. It requires two arguments: the value to check and the value to return if the first value is #N/A. Make sure you have included both arguments and they are in the correct order.

Not using #N/A as the first argument

Another common mistake is not using #N/A as the first argument. IFNA only works with the #N/A error, so make sure you are using it as the first argument. If you want to check for a different error, you should use a different formula.

Not wrapping the formula in parentheses

If you are using IFNA as part of a larger formula, make sure you wrap it in parentheses. This will ensure that the formula is evaluated correctly and give you the desired result.

Related Formulas

The following functions are similar to IFNA or are often used with it in a formula:

  • IFERROR

    The IFERROR formula is used to check whether a specified value results in an error or not. If the value results in an error, then it returns a user-specified value instead of the error. This function is commonly used to prevent errors from breaking a formula or to replace error messages with custom messages.

  • ISNA

    The ISNA function checks whether a value is #N/A. If the value is #N/A, the function returns TRUE; otherwise, it returns FALSE. This function is commonly used in combination with other functions that may return #N/A as a result.

  • VLOOKUP

    The VLOOKUP function is a lookup formula used to search for a value in the first column of a range of cells (the search key) and return a value in the same row from a specified column in that range. This function is most commonly used to look up and retrieve data from a table.

Learn More

You can learn more about the IFNA Google Sheets function on Google Support.