How to Use the IFERROR Function in Excel
Today, we’ll explore the IFERROR function, a versatile tool available in both Microsoft Excel and Google Sheets. This function is designed to manage errors that may occur from other formulas. Essentially, IFERROR allows you to specify an alternative result in cases where a formula generates an error, ensuring the smooth handling of outcomes in your spreadsheets.
Overview
The syntax of the IFERROR function is:
IFERROR(value, value_if_error)valueis the expression or formula being evaluated for errors.value_if_erroris the value that will be returned if an error is detected in thevalueargument.
Examples
Below are some examples to illustrate how the IFERROR function can be utilized:
Example 1: Division by Zero
Consider a situation where you are performing a division operation, but the divisor (in this case, a cell) contains the value 0. Normally, Excel or Google Sheets would issue a #DIV/0! error. With IFERROR, you can provide a more understandable message or a different value. For instance:
| A | B | C |
|---|---|---|
| 5 | 0 | =IFERROR(A1/B1, “Division by zero”) |
Here, cell C1 will display “Division by zero” instead of the default #DIV/0! error message.
Example 2: Error in VLOOKUP
If you’re using the VLOOKUP function to find a value in a table and the value does not exist, a #N/A error is typically returned. The IFERROR function can be used to show a custom message. Consider the following:
| A | B | C |
|---|---|---|
| 1001 | #N/A | =IFERROR(VLOOKUP(A1, Table1, 2, FALSE), “Value not found”) |
In this scenario, if VLOOKUP fails to locate the value, “Value not found” will be displayed in cell C1, instead of the usual #N/A error.
Conclusion
The IFERROR function significantly enhances error handling in spreadsheet environments such as Excel and Google Sheets. By allowing customized output for errors, it improves readability and clarity in your documents, making them more user-friendly and meaningful.
More information: https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611