The excel ISERROR function:YOUR OBJECTIVE
You decided to use an Excel worksheet to write an invoice. Since you know you will have to enter the customer’s address in the table in which you are creating the invoice, you want to just enter a customer code in the invoice’s worksheet and have the customer’s address inserted automatically. Since customer codes and addresses are listed next to one another in another worksheet, the problem of getting an address from the other worksheet and inserting it into your invoice’s worksheet lends itself to a solution that uses the VLOOKUP function. With that in mind, you proceed to insert the VLOOKUP function in the invoice table. After testing your solution, however, you notice that if you enter an incorrect customer code, Excel’s standard error message #N/A appears in the cell where the address is supposed to appear. This is not satisfactory. Whenever an invalid customer code is entered, you would prefer to see a message, which you define yourself, appear in the address cell. In Excel, the ISERROR function can help you attain that result.
In this learning module, you will be shown how to use the excel ISERROR function.
HOW DOES THE EXCEL ISERROR FUNCTIONWORK?
Using the excel ISERROR function, you can intercept an error caused by a function or formula then either display a message of your own or perform a different action.
In our current example, you can solve the task by using the excel ISERROR function in an IF function’s argument. Then, after entering an incorrect customer code, the VLOOKUP function would display your message instead of Excel’s standard error message (#N/A).
When using the VLOOKUP function, whenever the sought value is not found in the Table array, “#N/A” is returned instead of the item you were looking for. In the next illustration, you can see what happens when you use the VLOOKUP function without the excel ISERROR function. To improve the situation, you can place the VLOOKUP function in an IF function, and then let it execute only if the VLOOKUP function will not return an error. You can use the excel ISERROR function to detect any error condition and on that basis carry out your own action. This may sound more complicated than it is. The practical example presented here should clarify things. We will proceed step-by-step.
The usual approach to solving such problems, is to insert in the worksheet whichever formula or function you need to get the result you want, without regard for possible errors. You would then test your formula or function, noting the circumstances that produce a standard error message.
If you look in the formula bar in the above illustration, for exmple, you will see a VLOOKUP function that enables you, after typing the customer number into B3, to automatically insert the customer’s address from an Array table, located on the Addresslist worksheet, into B5. This works fine, except for the fact that whenever the customer number does not exist in the Table array on the other worksheet, the standard error message appears (as shown). We will now proceed to revise that solution to get to a more desirable result.
Note that when there is an error condition in B5, ISERROR(B5) would return the result TRUE
Therefore, as a second step, in cell B5, you want to insert an IF function that uses an excel ISERROR function to find out if an error or a valid result would be returned by the VLOOKUP that you already tested. If the excel ISERROR would reveal an error, you could then insert your own error message, for example, but if the excel ISERROR reveals no error, you would simply execute the VLOOKUP again, this time knowing in advance that the result would be valid.
Before inserting the IF function in B5, copy the existing VLOOKUP function to the clipboard, making certain that you do not copy the equal (=) sign. Now delete the entire function from the formula bar. Start the Function Wizard by clicking the button just to the left of the formula bar. In the Logical category, select the IF function and click OK. The Function Arguments dialog box for the IF function opens. Click in the Logical_test input box.
Now click the small arrow next to the Name Box. From the selection list that appears, click More functions, the last entry in the list. Look for and find excel ISERROR in the Information category. Then click OK.
Now, in the Function Arguments dialog box for excel ISERROR, click in the Value input box (shown below). Paste the copied function from the clipboard into the Value argument input box by pressing [Ctrl]+[V].
Next, click on the IF in the formula bar an illustrated below:
The Function Arguments dialog box for the IF function opens again. In the Logical_test argument field, you should now see the excel ISERROR function with the function you copied as its argument. Now you only have to make appropriate entries for the Value_if_true and the Value_if_false arguments.
In the Value_if_true argument enter what you want to happen if the Logical_test returns TRUE (ISERROR returns TRUE if the VLOOKUP function in our example finds no match.) This can be your own text, i.e., what you want to display in the target cell when there is no match. For the Value_if_false argument, enter what will happen if the above Logical_test would return FALSE, i.e., the VLOOKUP function would not return an error. In this case, you simply want to execute the VLOOKUP. Therefore, paste the formula that is still in the clipboard into the Value_if_false argument field by pressing [Ctrl]+[V] again. To finish, click OK.
The formula bar now shows the complete IF function:
This can be read this as follows: If the VLOOKUP function returns an error (i.e., there was no match), display “Customer # not available”. Otherwise execute the VLOOKUP function normally (you now know it will return a match).
You do not have to retype a formula or function that already exists. As described above, you can copy it onto the clipboard and paste it into the IF function when you need it.
Here we illustrate another example that manages an error that can result from the use of a simple math formula:
At the left, a formula (in B6) divides B3 by B4. If the divisor is 0 (zero), as shown here, an error message (#DIV/0) will be returned. (From mathematics, recall that it is not possible to divide a number by zero; any attempt to do so always generates an error.) At the right, the excel ISERROR function is used to intercept the error message (in E6). The excel ISERROR function, used in combination with an IF function, as seen in the formula bar, works like this:
IF the division of E3/E4 would cause an error,
THEN display a user-defined error message ,
OTHERWISE divide E3/E4.