The IFNA function in Google Sheets is useful if you want to handle the #N/A errors on your formulas.
Meaning, the IFNA function traps and handles #N/A error that may appear in formulas. The function lets you assign an alternate value if a formula results in an #N/A error.
Table of Contents
The rules for using the IFNA function in Google Sheets are as follows:
- If an empty cell is passed to value_if_na, the function would treat it as an empty string value (“”).
- Likewise, if no second argument is provided, the IFNA function would treat it as an empty string value (“”).
- The IFNA function is more specific and only targets the #N/A error. All other errors, such as #DIV/0! or #REF!, are ignored by the IFNA function.
- The IFERROR, on the other hand, will trap all other errors. Please visit our previous article for more information about the IFERROR function.
Let’s take an example.
James would like to make his record in Google Sheet look professional by handling all the #N/A errors that appear in it.
For all the instances of #N/A that appear on his list, James likes them to be some sort of text warning that would flag him immediately.
That’s where the IFNA function will come in handy. He can specify an alternate value to #N/A error.
Since James likes to be warned whenever a person, who’s not registered on his master list, appears in the Attendees column, he can assign the text “Not on Masterlist” and use conditional formatting to highlight it using a fill color.
See his improved record below:
James can now easily identify those who attended his discussion but never were on his supposed list.
Let’s have another example!
Cindy, a payroll officer, is having trouble computing the total hours worked of their employees in Google Sheet.
Not all employees are going to work as expected. Some have incurred absences, which result in issues on her lookup formula.
Furthermore, these lookup issues hinder the calculation of the total hours worked. See below:
Luckily, James and Cindy are friends. Cindy asked for help from him and they both agreed that it’s better to leave a blank or null value whenever a particular date is not found in the login-logout record of their employees.
James used the IFNA function to her record. See Cindy’s improved table below:
Good collaboration, right?
Watch out for a more advanced tutorial and examples on how you can use the IFNA function in the coming weeks. Be sure to subscribe to be notified.
Perfect! Let’s begin getting to know more about our IFNA function in Google Sheets.
The Anatomy of the IFNA Function
So the syntax (the way we write) the IFNA function is as follows:
Let’s dissect this thing and understand what each of these terms means:
- = the equal sign is just how we start any function in Google Sheets. It is how Google Sheets understand that we are asking it to either do computation or use a function.
- IFNA() is our IFNA function. It handles #N/A errors.
- value is the reference, expression, or formula to check for an #N/A error.
- value_if_na is the value to return if an #N/A error is found.
A Real Example of Using IFNA Function
Let’s take a look at James’ now-professional-looking table to see how the IFNA function is used in Google Sheets.
Notice there are only two arguments needed for the IFNA function to work.
First, there’s your calculation or another function. Secondly, a text/string that you want to appear, or another calculation that you want the IFNA function to perform once the #N/A error is returned by your first argument.
In our example above, James only needed the IFNA function to return the text ‘Not in Masterlist’ to warn him that the name doesn’t appear on his lookup reference.
What does the IFNA function do if the name appears on his list?
Simple. It just tells the first argument to perform its job. In this case, the VLOOKUP function looks for the name and returns the corresponding preferred schedule based on the lookup reference.
If James decides to not put any text and just make the instances of #N/A error disappear, he can change the second argument to an empty value (“”).
See the example below:
The IFNA function is normally used alongside a lookup function such as the VLOOKUP, HLOOKUP, MATCH, and LOOKUP functions.
You may make a copy of the spreadsheet using the link I have attached below.
How to Use IFNA Function in Google Sheets
- Click on any cell to make it the active cell. For this guide, I will be selecting E2, where I want to show the result.
- Next, type the equal sign ‘=‘ to begin the function and then follow it with the name of the function, which is our ‘ifna‘ (or ‘IFNA‘, not case sensitive like our other functions).
- Type open parenthesis ‘(‘ or simply hit Tab key to let you use that function.
- Now the exciting part! Let’s give our function its first argument, the value. This is where you put your main function or formula. In this scenario, using the VLOOKUP function, we will pull the preferred schedule of the name of a person in column D based on the master list in columns A and B. Type in ‘vlookup(D2,A:B,2,false)’ as the first argument of our IFNA function.
- To let the Google sheet know that we’re done typing our first argument, we should now type in the delimiter or the character that separates each argument on a function. In this case, type comma ‘,’.
- Next, let’s provide a value that the IFNA function will return if the result of our VLOOKUP function is #N/A. In this scenario, type in quotation mark (“) and follow it with ‘Not in Masterlist’. Don’t forget to close it with another quotation mark (“).
- Finally, hit your Enter or Tab key.
- Copy the formula down to the remaining cells. Rows in column E will now show you the preferred schedule of the names in column D, if found, in the Masterlist column. Otherwise, it would show the text ‘Not in Master list”.
That’s pretty much it. You can now use the IFNA function in Google Sheets together with the other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier.