What is the Excel IFERROR Function?
The Excel IFERROR function returned a custom result when an error occurred; otherwise returns the formula result.
The IFERROR function is an improvement provided by Excel to trap and handle errors in a formula without having to create multiple if statement
IFERROR Syntax
IFERROR(value, value_if_error)
value, required, the argument to check for an error, could be a value, a reference or a formula
value_if_error, required, the custom return value if an error occurred.
Usage Notes:
- The IFERROR functions, trap and handles seven error types, #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.
- If you want to trap #N/A error only, you can use the excel IFNA function (available in Excel 2013 or newer version).
- If the value or value_if_error argument is an empty cell, IFERROR function treats it as an empty string value (“”).
How to Use IFERROR Function in Excel
IFERROR VLOOKUP
Use the IFERROR function to handle the most frequent error when using the VLOOKUP function, which is the #N/A error.
For example, there is data as shown below. You can’t see the total amount bill because of the #N/A error. The error appears because there is “not in the price list” order. How to remove errors so you can see the total bill amount.
The Formula for the price
Use VLOOKUP function to find out the price for a tall size.
=VLOOKUP(F3,$A$3:$D$15,2,FALSE)
The Formula for Subtotal
Use the multiplication formula to calculate the Subtotal.
=G3*H3
The Formula for Total Amount
Use SUM function to calculate the Total Amount bill
=SUM(I3:I6)
The #N/A error appears in cell H5, I5, and I8. What to do to remove #N/A error? The solution is using the IFERROR function. Please modify the formula to find the prices.
The Modified Formula
=IFERROR(VLOOKUP(F3,$A$3:$D$15,2,FALSE),0)
The value argument remains the previous VLOOKUP function, while the value_if_false argument is filled with number 0, it means if an error occurs, could be #N/A error or other errors, the IFERROR function return zeroes 0 not an error anymore.
After modifying the formula to find prices, the #N/A error in cell H5 disappears, the same error in cell I5 and I8 disappears too.
IFERROR INDEX MATCH
The INDEX function and MATCH function if used together can replace the VLOOKUP function, of course with some advantages.
Using the same data as the previous example. Error #N/A appear too when you use the INDEX function and the MATCH function.
The Formula for the price
Use INDEX function and MATCH function to find out the price for a tall size.
=INDEX($B$3:$B$15,MATCH(F3,$A$3:$A$15,0),1)
The formulas for “Subtotal” and “Total Amount” are the same as the previous example.
The result is as shown below.
Error #N/A appears in the same place as the previous example.
The solution is using the IFERROR function, the same as the previous example.
The Modified Formula
=IFERROR(INDEX($B$3:$B$15,MATCH(F3,$A$3:$A$15,0),1),0)
The value argument uses the same INDEX and MATCH function; the value argument is filled with the number zero. The result, no error in cell H5, I5, and I8.
