How to Use Google Sheets IFNA Function

0

Google Sheets IFNA function is useful to get rid off #N/A errors in formulas. Why this specific function is required when we can use IFERROR for the same purpose?

I will answer this question after introducing to you how to use the IFNA function in Google Sheets.

Syntax:

IFNA(value, value_if_na) 

Arguments:

value – The value to evaluate for an #N/A error.

value_if_na – The value to return if the evaluated value, the first argument, is an #N/A error.

Purpose:

The IFNA function in Google Sheets can return a specified value if the evaluated value argument is an #N/A error. That’s the one and only purpose of this logical function.

Example to IFNA Function in Google Sheets:

Here are two formulas.

The value to evaluate (first argument) in the below formula is an #N/A error. So this formula would return the specified value (value_if_na), i.e. “error”

=ifna(#N/A,"error")

This formula would return 100.

=ifna(100,"error")

Common Functions That Return #NA Error in Google Sheets

Vlookup, Hlookup, Match, Switch, and IFS are the main functions that return #N/A errors.

Here are a few examples.

How to Remove Vlookup #N/A Error with IFNA Function in Google Sheets

The Vlookup function (Hlookup also) would return an #N/A error if the search key is not available in the first column (first row in the Hlookup) of the lookup range.

Vlookup #N/A Error in Google Sheets

In this Vlookup the search key is “D” which is not available in the first column in the lookup range A6:B8.

How to remove this #N/A error and return blank or a specific value in Google Sheets? You can use the Vlookup formula as below.

=ifna(vlookup(D6,A6:B8,2,0),"Not Found")

If you want blank instead of the said error in Vlookup, modify the formula as given below.

=ifna(vlookup(D6,A6:B8,2,0),)

#N/A Error in Match and How to Remove It

Similar in Vlookup we can use the IFNA function in Google Sheets with the Match function.

Here is one example.

=ifna(match(D6,A6:A8,0),"Not Found")
Google Sheets IFNA Function to Remove Error in Match

Sheets IFNA Function to Remove #N/A Error in IFS Function

IFS is another function that returns the above error. It’s not so common in use but understanding the usage may be useful.

Assume the value in cell A1 is 40.

=ifs(A1>50,"Passed")

Try the above formula and see the result yourself. Here I would recommend you to use the IFS formula as below.

=ifna(ifs(A1>50,"Passed"))

Another function that would return an #N/A error is SWITCH.

How to Remove Switch Function #N/A Error

IFNA Function with SWITCH in Google Sheets

Here you can rewrite the SWITCH formula as below.

=ifna(switch(B2:B8,"Sun",1,"Mon",2,"Tue",3,"Wed",4,"Thu",5,"Fri",6,"Sat",7))

The Advantage of Using Google Sheets IFNA Function Over Iferror

In all the above examples, you can replace IFNA with IFERROR. But I won’t recommend you to use IFERROR function as an alternative to IFNA. Why?

The error value #N/A simply says the search key is not found or there is no matching key in the range. So isn’t an error exactly.

If you use IFERROR, you are eliminating your chance to audit your formula for errors!

Additional Resources:

  1. Difference Between ISERR and ISNA Functions in Google Sheets.
  2. Different Error Types in Google Sheets and How to Correct It.
  3. How to Use the ISERROR Function in Google Sheets and Error Handling.
Prashanth KV
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

LEAVE A REPLY

Please enter your comment!
Please enter your name here