If I were asked to name the function which I use more often than any other, I would probably have to say the IFERROR function. This is not because it does anything particularly powerful or unique. In fact, the IF and ISERROR functions together can accomplish everything the IFERROR function can (explained at the end). It's just much messier. The IFERROR function exists purely for convenience, but convenience is a wonderful thing. Here's how it works:
There are 2 parameters in the IFERROR function. The first parameter is what you want the cell to contain. The second parameter is what you want the cell to contain instead in the event that the first parameter returns an error. Here's an example of how this might be used:
= IFERROR (A1 / B1,0)
In the above example, the function will take the value of cell A1 and divide it by the value of cell B1. However, if cell B1 contains zero or is blank then A1 / B1 will result in a # DIV / 0! error. But, using the IFERROR function as in the example above, the function will instead return zero.
Usually when I use this function it is in conjunction with the VLOOKUP function. For example:
= IFERROR (VLOOKUP (C1, A: B, 2, FALSE), “”)
This looks up the value of C1 in column A and returns the result which is in the same row of column B. However, if the value of C1 is not found in column A, the VLOOKUP function will return the # N / A error. But, because of the IFERROR function, the cell will be blank instead (due to the empty quotes).
I have always found that blank cells looks much more professional that having errors all over your spreadsheet. In addition to being helpful when using the VLOOKUP function, this technique is also quite useful when using SEARCH, FIND, or really any other function that has a knack for turning up errors.
Unfortunately, the IFERROR function is only available for Excel 2007 and up. But, here's how to work around it for those who are still using Excel 2003. In the previous example you would instead use:
= IF (ISERROR (VLOOKUP (C1, A: B, 2, FALSE)), “VLOOKUP (C1, A: B, 2, FALSE)
It's messier to look at due to the fact that you are required to input your original function twice, but it will accomplish the exact same thing in the end.