8 Ways to Use the Excel IFERROR Function and Improve Your Spreadsheet

Excel’s IFERROR function is a lifesaver when it comes to handling errors in your spreadsheets. Whether you’re dealing with division by zero, missing data, or lookup failures, IFERROR can help keep your data clean and user-friendly. Here are some practical examples and easy steps to master the IFERROR function in Excel.

How do I use the Excel IFERROR function?

1. Basic Use of IFERROR

1. Open Excel and select the cell where you want the result.
2. Enter the formula where you suspect an error might occur. For instance, enter =A1/B1. If B1 is zero, Excel will return a #DIV/0! error.
3. To catch this error, modify your formula to use IFERROR and press Enter: =IFERROR(A1/B1,"Error in calculation")

Using this function, if there’s an error in the calculation, Error in calculation will be displayed instead. This helps avoid displaying the raw error to users, making your spreadsheet look cleaner.

2. Using IFERROR with VLOOKUP

1. In the cell where you want the result of the VLOOKUP function, start typing your VLOOKUP formula: =VLOOKUP(A2, B2:C10, 2, FALSE)
2. If the lookup value in A2 is not found, VLOOKUP will return a #N/A error. To handle this, wrap the VLOOKUP function with IFERROR (press Enter to run the formula): =IFERROR(VLOOKUP(A2, B2:C10, 2, FALSE),"Not found")

With this formula, if the lookup value isn’t found, Not found will be displayed instead of the #N/A error. This is useful when you want to provide a more user-friendly message when a lookup fails.

3. Handling multiple errors

1. If you have a formula that can result in multiple errors, use IFERROR to handle them. For example, consider the formula =SUM(D5:D15). If the range contains errors like #N/A, the sum will fail.
2. Modify your formula to handle errors using IFERROR: =SUM(IFERROR(D5:D15,0))
3. Press Ctrl + Shift + Enter to enter it as an array formula if you are using Excel 2010 or earlier. In Excel 2013 or later, just press Enter.

This formula will replace any error in the range with 0 before summing, thus preventing the overall SUM function from failing.

4. Suppressing Division by Zero errors

1. In the cell where you want the result, type your formula that includes division, for example, =A1/B1.
2. To prevent errors when B1 is zero, use the following IFERROR function and press Enter to run it: =IFERROR(A1/B1, 0)

By using this formula, if B1 is zero, the function will return 0 instead of the #DIV/0! error. This is useful in avoiding disruptions in calculations due to division by zero.

5. Requesting user input before calculation

1. If you want to prevent calculations until a user provides specific input, start with your formula, such as =A1/B1. This will give an error if B1 is empty.
2. Modify your formula to use IFERROR and provide a message: =IFERROR(A1/B1,"Please enter a value in B1")
3. Press Enter.

This formula displays a custom message prompting the user to input a value instead of showing a calculation error. It ensures that users are aware of missing inputs needed for accurate calculations.

6. Nesting multiple IFERROR functions

1. If you need to perform sequential lookups and handle errors at each step, start with your initial VLOOKUP formula: =VLOOKUP(A2,Sheet1!A:B,2,FALSE)
2. To handle multiple sheets, nest IFERROR functions with this fformula and press Enter: =IFERROR(VLOOKUP(A2,Sheet1!A:B,2,FALSE),IFERROR(VLOOKUP(A2,Sheet2!A:B,2,FALSE),"Not found"))

This formula will check Sheet1 first, and if the value is not found, it will proceed to check Sheet2. If it’s still not found, Not found will be displayed. This ensures all possible sources are checked before concluding the value is missing.

7. Using IFNA for specific errors

1. In your VLOOKUP formula, replace IFERROR with IFNA if you only want to catch #N/A errors and not other types of errors: =IFNA(VLOOKUP(A2, B2:C10, 2, FALSE),"Not found")
2. Press Enter.

By using IFNA, only the #N/A errors will be caught and handled, while other errors will still be displayed. This is useful when you want to distinguish between different types of errors in your data.

8. Summing while ignoring errors

1. In the cell where you want the sum, start with the SUM function: =SUM(D5:D15)
2. To ignore errors in the range, use IFERROR inside the SUM function: =SUM(IFERROR(D5:D15,0))
3. Press Ctrl + Shift + Enter to enter it as an array formula if you are using an older version of Excel. In newer versions, just press Enter to run it.

This will ensure that errors within the range are treated as 0, allowing the SUM function to calculate correctly without being interrupted by errors.

By mastering the IFERROR function, you can make your Excel spreadsheets more robust and user-friendly. Remember to always validate your data inputs and consider using data validation rules to prevent errors from occurring in the first place.