How to Use the IFERROR Function in Excel
Description of the IFERROR Function
The IFERROR function will return a value that you specify if an error occurs in a cell. There are numerous errors in Microsoft Excel that can be removed with the IFERROR function and are described below:
- #N/A – Means that a function or a formula can’t find data that is referenced
- #VALUE! – In this case the wrong type of function or operand has been used in an argument
- #REF! – Means that a specific reference is not valid within a function or argument
- #DIV/0! – Means that a function or operation is trying to divide by zero
- #NUM! – Occurs when a formula has an invalid numeric value for the given operation
- #NAME? – This appears when text within a formula is not recognized
- #NULL! – Occurs in a formula when a space is used that references multiple ranges and when a comma segregates range references
If any of these errors occur, the IFERROR function can replace the error with any value that you would like to appear in the cell. Additionally, you can also display no value at all. Note that the function does not have to be used only when there is going to be an error. As the name suggests (IFERROR), the value will only be returned if an error occurs.
Syntax of the IFERROR Function
To be able to use the IFERROR function, the syntax of the function must be followed perfectly. Below the caption, the proper syntax is displayed. The syntax contains the "Value" argument and the "Value_if_error" argument which are both required for the function to work. The value argument is the value that is checked for an error. An operation that could have an error would be placed here. Next, the value if error is what will be returned if there is in fact an error. If there is not an error, the operation will resume as normal. Let's look at an example.
Example 1 VLOOKUP REF Error
In the example below, I use the VLOOKUP function and show that the #NA error appears if the function cannot reference a cell. In the first row of the columns labeled Revenue 2017, the function is searching for the date 3 columns to the left. Once the VLOOKUP function finds the date, it can return the specified revenue value. Since the lookup value for the VLOOKUP function is unavailable the #NA is returned.
Lookup Table Missing Lookup Value
#NA Error Returned
Say we don't want the error to appear and would like to have a blank space to alleviate any possible confusion. This can be accomplished with the IFERROR function. The function that contained the VLOOKUP with an error is shown below. The function that follows includes the IFERROR function to remove the error.
When using this IFERROR function in this case, the VLOOKUP function in its entirety is the value argument with the equals sign removed. Next, a comma needs to be added to separate the arguments. Next the value that you want to return needs to be added. Since I want a blank space I use quotes, then close the function with parenthesis. You can return any text that you want by placing that text within the quotes. If you would like a number value to appear, the quotes are unnecessary.
Microsoft. (n.d.). IFERROR function. Retrieved January 7, 2019, from https://support.office.com/en-us/article/IFERROR-function-C526FD07-CAEB-47B8-8BB6-63F3E417F611
Bluttman, K. (n.d.). Excel Error Messages to Get to Know. Retrieved January 7, 2019, from https://www.dummies.com/software/microsoft-office/excel/excel-error-messages-to-get-to-know/
© 2019 James Smith