Have you ever encountered the frustrating #N/A errors in your Google Sheets formulas? Well, fear not! In this article, I will introduce you to the amazing IFNA function in Google Sheets, which can help you get rid of these pesky errors. You might be wondering why we need this specific function when we can use IFERROR for the same purpose. Let me enlighten you on the unique benefits of the IFNA function and how to use it effectively.
Syntax and Purpose:
The IFNA function in Google Sheets has a simple syntax: IFNA(value, value_if_na). It evaluates the first argument (value) for the #N/A error and returns the second argument (value_if_na) if the evaluated value is indeed an #N/A error. Its sole purpose is to handle and replace #N/A errors in your formulas.
Example to IFNA Function in Google Sheets:
Let’s dive into an example to better understand the power of the IFNA function. Imagine you have two formulas:
- The first formula evaluates an #N/A error as the value to be checked. In this case, the IFNA function would replace the error with the specified value « error ».
=ifna(#N/A,"error")- The second formula simply returns the value 100. In this case, the IFNA function would have no impact, as there is no #N/A error to handle.
=ifna(100,"error")Common Functions That Return #NA Error:
Various functions in Google Sheets, such as Vlookup, Hlookup, Match, Switch, and IFS, can generate #N/A errors. Let’s explore a few examples and learn how to tackle them using the IFNA function.
How to Remove Vlookup #N/A Error with IFNA Function in Google Sheets:
The Vlookup function (and Hlookup) will produce an #N/A error if the search key is not found in the first column (or first row for Hlookup) of the lookup range.
To remove this error and return blank or a specific value, use the following Vlookup formula with the IFNA function:
=ifna(vlookup(D6,A6:B8,2,0),"Not Found")If you prefer a blank cell instead of the error, modify the formula as follows:
=ifna(vlookup(D6,A6:B8,2,0),)N/A Error in Match and How to Remove It:
Similar to Vlookup, we can use the IFNA function with the Match function. Here’s an example:
=ifna(match(D6,A6:A8,0),"Not Found")
Sheets IFNA Function to Remove #N/A Error in IFS Function:
The IFS function is another culprit that generates the #N/A error. Although not commonly used, it’s good to understand its usage.
Let’s assume the value in cell A1 is 40.
=ifs(A1>50,"Passed")Try the above formula and witness the result yourself. However, I would recommend using the following IFS formula with the IFNA function:
=ifna(ifs(A1>50,"Passed"))Another function that can produce an #N/A error is SWITCH.
How to Remove Switch Function #N/A Error:
Here’s how you can rewrite the SWITCH formula to eliminate the #N/A error:
=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
You may wonder why we should choose the IFNA function over the IFERROR function as an alternative. The answer lies in the nature of the #N/A error itself. This error indicates that the search key is not found or there is no matching key in the range. It’s not an actual error; it’s merely informative. If you use IFERROR, you eliminate the opportunity to audit your formula for errors!
Additional Resources:
To further enhance your Google Sheets skills, check out these additional resources:
- 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 and Error Handling
Now armed with the knowledge of the powerful IFNA function, you can confidently eliminate #N/A errors in your Google Sheets formulas. Happy spreadsheeting!
