Using IFERROR function with VLOOKUPs

One of my favorite new Excel 2007 functions is IFERROR. It provides an elegant way to handle errors that previously would have required ugly IF functions. I use it a lot when I have a column of VLOOKUP functions, which can return #N/A if it can’t find whatever it is looking for. Here’s an example:

The IFERROR function requires two parameters. The first is usually a formula that you want evaluated. If an error is not detected, IFERROR  returns the value of the first parameter. If an error is found, it returns the second parameter. The following errors are supported by the function: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

To adapt the formula used in the above example, we simply use the VLOOKUP as the first parameter in the IFERROR function, like =IFERROR(VLOOKUP(D3,$A$3:$B$5,2, FALSE),"NOT FOUND!"). Here’s what it looks like in action:

The IFERROR function can really clean up a worksheet and eliminate those unsightly errors.

Leave a Reply

Your email address will not be published. Required fields are marked *