Google Sheets IFNA function is a powerful tool to eliminate #N/A errors in formulas. But why should you use this function instead of IFERROR? In this article, I’ll explain how to use the IFNA function in Google Sheets and why it’s necessary.
Understanding the Syntax of IFNA Function
The syntax of the IFNA function is as follows: IFNA(value, value_if_na). Let’s break it down:
- value: This is the value you want to evaluate for a #N/A error.
- value_if_na: This is the value you want to return if the evaluated value is a #N/A error.
Purpose of the IFNA Function
The purpose of the IFNA function in Google Sheets is simple. It allows you to return a specified value if the evaluated value is a #N/A error. This logical function is designed specifically for this purpose.
Example of Using the IFNA Function
To illustrate how the IFNA function works, let’s look at two formulas:
-
The first formula evaluates an #N/A error as the value to evaluate. The function will return the specified value, « error »:
=IFNA(#N/A,"error") -
The second formula evaluates the value 100, which is not an error. In this case, the function will return 100:
=IFNA(100,"error")
Common Functions That Return #NA Error
Several functions in Google Sheets can return #N/A errors. The main ones include Vlookup, Hlookup, Match, Switch, and IFS.
Removing Vlookup #N/A Error with IFNA Function
The Vlookup function (Hlookup as well) returns an #N/A error if the search key is not found in the lookup range. To remove this error and return a blank or a specific value, you can use the following formula:
=IFNA(VLOOKUP(D6,A6:B8,2,0),"Not Found")You can modify this formula to return a blank by excluding the second argument.
N/A Error in Match and How to Remove It
Similar to Vlookup, the Match function can also return an #N/A error. To remove this error, you can use the IFNA function in Google Sheets with Match. Here’s an example:
=IFNA(MATCH(D6,A6:A8,0),"Not Found")Using IFNA Function to Remove #N/A Error in IFS Function
The IFS function is another culprit that can return the #N/A error. Although not commonly used, understanding how to handle it can be useful. For example, if the value in cell A1 is 40, you can use the following formula:
=IFNA(IFS(A1>50,"Passed"))Remove Switch Function #N/A Error with IFNA
The SWITCH function is another function that can generate an #N/A error. To remove this error, you can rewrite the SWITCH formula as follows:
=IFNA(SWITCH(B2:B8,"Sun",1,"Mon",2,"Tue",3,"Wed",4,"Thu",5,"Fri",6,"Sat",7))Advantages of Using IFNA Over IFERROR
Although you can replace IFNA with IFERROR in the above examples, it’s not recommended. The #N/A error simply indicates that the search key is not found, and it’s not an actual error. By using IFERROR, you eliminate the opportunity to review your formula for errors.
Additional Resources
If you want to delve deeper into error handling in Google Sheets, here are some additional resources for you:
- Difference Between ISERR and ISNA Functions in Google Sheets
- Different Error Types in Google Sheets and How to Correct Them
- How to Use the ISERROR Function in Google Sheets for Effective Error Handling
Now that you know how to use the IFNA function and its advantages, you can confidently handle #N/A errors in your Google Sheets formulas. Happy spreadsheeting!
