How to use IFNA in Google Sheets to handle errors

IFNA in Google Sheets is a super simple function. It checks if a formula results in an error and returns a specific value if it does. This allows you to clear up errors from any spreadsheet making it easier to read.

In this article we will explore how the IFNA formula works and a few use cases where this function is invaluable.

Spreadsheets are just the start

Syntax of IFNA

The inner workings of this function are straightforward and it has two possible outcomes:

  1. The formula it evaluates doesn’t return an #N/A! error → the output of this formula is shown.
  2. The formula it evaluates returns an #N/A! error →  an alternative message is shown instead.

Its basic syntax looks like this:

=IFNA(value, [value_if_na_error])

  • value – the formula or expression to check for #N/A! errors. If no error is found then this is returned.
  • [value_if_na_error] – The value to return if the formula or expression evaluates to #N/A!

Common uses cases of IFNA in Google Sheets

You could use this function any time a formula returns one of these errors. But there are some specific use cases that are especially common and useful.

IFNA and VLOOKUP

VLOOKUP is a function that returns #N/A! errors every time it doesn’t find a value it is looking for. And depending on the dataset you are working with this can happen a lot.

That’s why it’s a good idea to use both of these functions together. Just use IFNA to return blank values or a specific error message if VLOOKUP doesn’t find anything.

=IFNA(VLOOKUP(E3, $A$3:$C$11, 2, FALSE), “Employee not found”)

Customizing this error message will give better feedback to you or anyone who reads the spreadsheet.

ifna in google sheets 1

IFNA then 0

MATCH is another function that returns #N/A! errors every time the function doesn’t find the value it’s looking for.

Again, to handle this you can use IFNA. This way, if MATCH doesn’t find the desired value, you can make it return 0 instead of an error.

=IFNA(MATCH(E3, $A$3:$A$11, 0), 0)

ifna in google sheets 2

IFNA vs IFERROR

IFERROR is a lot like IFNA. The only difference is that it will detect any error, while IFNA will only look for one specific error. For the most part they are interchangeable, so the last formula we covered would work the same with IFERROR.

=IFERROR(MATCH(A2, C2:C10, 0), 0)

The only cases where these functions aren’t the same is if you are trying to sniff out NA errors in particular.

Learn more about IFERROR for other ways to handle errors in Google Sheets.

Use Sheetgo, reduce errors

Sheetgo offers many ways to optimize your data streams. All of them will keep information moving without the usual errors. These features include:

  • Connections to transfer and process data.
  • Forms to input correct data into your spreadsheets.
  • Workflows to automate your business processes.

Find out how Sheetgo can get your initiatives moving in the right direction.

Conclusion

Using IFNA in Google Sheets is a powerful way to handle errors and maintain clean, readable spreadsheets. 

Whether you’re returning a custom error message, a blank value, or a zero, IFNA provides a simple solution to improve your spreadsheet management.

Ready to streamline your spreadsheet data?

You may also like…