Excel IFNA Function
If you are an Excel user, you may know how annoying and time-consuming the errors could be. Most of the time, when you work with functions unlike HLOOKUP, VLOOKUP, MATCH, etc., you encounter the #N/A error. The blunt #N/A looks unprofessional and messy when you present the formula sheet to your colleagues or the management. Now the question arises of how to deal with this error. To your, surprise Excel has provided a solution for this as well. You can use the inbuilt IFNA Excel function to replace the #N/A error with your customized message.
What is IFNA function?
The Excel IFNA function is introduced by Microsoft to specifically trap and manage #N/A errors in your worksheet, ignoring the other errors. This function returns the customized message that you have specified if Excel finds any #N/A error value in the function; else, in case of no error, IFNA returns the result of the formula.
Instead of using the IFERROR function (that traps all types of errors), you can switch to the IFNA function to catch and handle #N/A errors specifically that may appear in functions that execute lookup formulas such as MATCH, VLOOKUP, HLOOKUP, etc. The IFNA function returns a customized output whenever Excel traps any #N/A, and the good part is that it returns normal output if no error is detected in your worksheet. Please note that the IFNA function will only handle #N/A errors, and all the other Excel errors will still be displayed in your Excel sheet. The IFNA function falls under the category of Excel Logical functions.
NOTE: Microsoft Excel has introduced the IFNA function in Excel 2013 version, so if you are working with an older version of Excel, you may not find this function.
Syntax
Parameters
- Value (required)- This is parameter represents the text value for which you want to check if there is an #N/A error or not.
- Value_if_error(optional)- In this parameter, we specify the custom text that would be displaced in place of the error if in the cell there is a value of #N/A.
Return
The IFNA function returns a customized output whenever Excel traps any #N/A, and the good part is that it returns normal output if no error is detected in your worksheet.
Points to Remember for IFNA function
- If the user provides “value” or “value_if_na” as an empty cell, the IFNA function considers it as an empty string value (“”).
- If you pass an array formula in the “value” parameter, the IFNA function will return an array of outputs for each cell in the range given in the value.
- The IFNA function helps to catch and handle #N/A errors and is handy when dealing with vast and complex data sets.
- This function is faster quicker when it comes to troubleshooting because it is designed for a specific error (#N/A) compared to other functions such as IFERROR, ISERROR, etc.
Examples
Example 1: Divide Col A by Col B and show the results using IFNA function.
IFNA becomes very useful when you only want to catch only the #N/A error. To trap and handle #N/A errors using the IFNA function follow the below-given steps:
STEP 1: Add a helper column named “Output using IFNA”
Place your mouse cursor to the cell next to “ColA” and name the new column as “Output using IFNA”.
It will look similar to the below image:
In this column we will type our IFNA formula and will trap #N/A errors for different data values.
NOTE: Format the helper column and match it with the first column to make your Excel sheet more attractive.
STEP 2: Type the IFNA formula
Put your cursor to the second row and start typing the function = IFNA(
It will look similar to the below image:
STEP 3: Insert the text parameter
- The formula will firstly ask you to enter the “value” parameter. We will specify the first cell value or pass the cell reference for which you want to check the #N/A error. Here, the cell reference for our data is B4, so our formula becomes: =IFNA( B4,
It will look similar to the below image:
- In the second parameter, we will specify the custom message that this function will throw in case if finds any #N/A error in the cell. Here, we have mentioned “OOPs! Error”, so our formula becomes: =IFNA( B4, “OOPS! Error”)
It will look similar to the below image:
STEP 4: IFNA will return the result
Since IFNA has found no #N/A error, so it has returned the original value. When we apply the formula for the next row, we will see what happens if it encounters a #N/A error.
It will look similar to the below image:
STEP 5: Drag the formula to other rows to repeat
Place your mouse cursor on the formula cell and point the cursor to the right corner of the cell. To your surprise, the mouse pointer will turn into a ‘+’ icon.
Refer to the below image:
Drag the ‘+’ icon down the cells. It will copy the function to all your cells, changing the cell reference as respective to the cell. As you can see below, when the function encounters #N/A error, it is handled by the IFNA function, and it replaces “#N/A” with the custom text “OOPS! Error” and displays it.
It will look similar to the below image:
That’s it; following the above few steps, you easily apply the IFNA function to your Excel worksheet to handle the # N/A errors.
Example 2: Using the IFNA function find the monthly salary of the given employees and if #N/A appears, then change it to ERROR IN SALARY.
| Emp ID | Designation | Annual Salary |
|---|---|---|
| E0001 | AVP | £80,000 |
| E0002 | Analyst | £50,000 |
| E0003 | Data Scientist | #N/A |
| E0004 | Analyst | £40,000 |
IFNA function becomes handy when you are dealing with vast and complex Excel data where the user would skip different types of errors. To trap and handle #N/A errors using the IFNA function follow the below-given steps:
STEP 1: Add a helper column named “Monthly Salary”
Place your mouse cursor to the cell next to “Annual Salary” and name the new column as “Monthly Salary”.
It will look similar to the below image:
In this column we will type our IFNA formula and will trap #N/A errors for different data values.
STEP 2: Type the IFNA formula
Put your cursor to the second row and start typing the function = IFNA(
It will look similar to the below image:
STEP 3: Insert the text parameter
- The formula will firstly ask you to enter the “value” parameter. We will specify the first cell value or pass the cell reference for which you want to check the #N/A error.
- Since we also have to find the monthly salary so we will divide the value with 12. Here, the cell reference for our data is D3, so our formula becomes: =IFNA( D3/12,
It will look similar to the below image:
- In the second parameter, we will specify the custom message that this function will throw in case if finds any #N/A error in the cell. Here, we have mentioned “ERROR IN SALARY”, so our formula becomes: =IFNA( D3, “ERROR IN SALARY”)
It will look similar to the below image:
STEP 4: IFNA will return the result
Since IFNA has found no #N/A error, so it has returned the monthly salary by dividing the annual salary 12.
It will look similar to the below image:
STEP 5: Drag the formula to other rows to repeat
- Place your mouse cursor on the formula cell and point the cursor to the right corner of the cell. It will turn into a ‘+’ icon.
- Drag the ‘+’ icon down the cells. It will copy the function to all your cells, changing the cell reference as respective to the cell. As you can see below, when the function encounters #N/A error, it is handled by the IFNA function, and it replaces “#N/A” with the custom text “ERROR IN SALARY” and displays it.
It will look similar to the below image:
