If I were asked to name the function that I more often than others to use, I would probably say IFERROR function. This is not because there is something very powerful or unique. In fact, IF and ISERROR functions together can achieve all possible IFERROR function (described later). It was only much messier. IFERROR functions exist solely for the convenience, but convenience is a wonderful thing. Here’s how it works: There are two parameters in the IFERROR function. The first parameter is what you want the cell contains. The second parameter is what you want instead of the cell containing the first parameter it returns an error. Here is an example of how it can be used: = IFERROR (A1/B1, 0) In the above example, the function takes the value of cell A1 and dividing by the number of B1 cells. However, if cell B1 contains zero or blank it A1/B1 will generate # DIV / 0! error. However, using the IFERROR function as in the example above, the function returns a bit zero.
Usually when I use this function in conjunction with the VLOOKUP function. Example: = IFERROR (VLOOKUP (C1, A: B, 2, FALSE), “”) This looks up the value of C1 in column A and returns the results that are in the same row of column B. However, if the value of C1 is not found in column A, VLOOKUP returns the # N / A error. However, because the IFERROR function, the cell is not empty (as empty quotes). I have always found that the blank cells have a more professional look mistake in your entire spreadsheet. Besides useful when using VLOOKUP function, this technique is also very useful when using the SEARCH, FIND, or really any other function that has the ability to errors.
Unfortunately up, IFERROR function is only available for Excel 2007 and above. But, here is a way to work around it for those who are still using Excel 2003. In the previous example you would prefer to use: = IF (ISERROR (VLOOKUP (C1, A: B, 2, FALSE)), “”, VLOOKUP (C1, A: B, 2, FALSE)) It looked a mess that must be paid to the fact that you are required to enter your original function twice, but will achieve the exact same thing in the end.