How to use IFERROR function in Excel?

The `IFERROR` function in Excel is a very useful tool for handling errors in formulas and returning a specified result if an error is found. Here’s a breakdown of how to use it, including its syntax and a few examples:

Syntax

The syntax for the IFERROR function is:

=IFERROR(value, value_if_error)

value: This is the expression or operation you want to evaluate. It could be a cell reference, a formula, or any logic that might return an error.
value_if_error: This is the result you want to display if an error occurs in the “value” part. This could be text, a number, another formula, etc.

Examples

Basic Example:

Suppose you are dividing numbers in cells A1 and B1, and you want to avoid the #DIV/0! error if B1 is 0. You can use:

   =IFERROR(A1/B1, "Error: Division by zero")

If B1 is zero, instead of showing a division error, it will display the message “Error: Division by zero”.

Dealing with VLOOKUP Errors:

If you are using `VLOOKUP` and the lookup value is not found, it will normally return a #N/A error. You can handle this using `IFERROR`:

   =IFERROR(VLOOKUP(F1, A1:B10, 2, FALSE), "Not Found")

Here, if the lookup value isn’t found, the formula returns “Not Found” instead of throwing an error.

Handling Error in Calculations:

If you have a more complex formula, such as:

   =IFERROR((D2*E2)/C2, 0)

This calculates the result of `(D2*E2)/C2` and returns 0 if there is any error (like C2 being 0), ensuring the spreadsheet doesn’t display an error message.

Usage Tips

Error Types: Common errors `IFERROR` can handle include #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM!, #NULL!, and #ERROR.
Compatibility: The `IFERROR` function is available in Excel 2007 and later versions. For earlier versions, you’ll need to use a combination of `IF` and `ISERROR` functions.
Performance: Use `IFERROR` to improve readability and performance of your Excel sheets by minimizing distracting error messages.

`IFERROR` can be a powerful way to make your Excel files more user-friendly and professional by cleanly managing potential errors in calculations and data retrieval.

facebooklinkedin