Error Handling!

Here is the scene: You are constructing a report that takes data from a table via a VLOOKUP function. Being the exacting person you are, you want the report to have a clean and professional look, without any extraneous and distracting Error Messages.

So, for example, what do you do when you get occasional (and annoying) #N/A results? The answer, of course, is the use of an Error-Handling function. There are several functions that can be used to handle errors, and three of the most common are as follows:

• ISNA – Checks whether a value is #N/A, and returns TRUE or FALSE
• ISERROR – Checks whether a value is any error, and returns TRUE or FALSE
• ISERR – Checks whether a value is an error, excluding #N/A and returns TRUE or FALSE

So back to our example where you are getting some #N/A results while using a VLOOKUP function. The syntax for the VLOOKUP function is as follows,

VLOOKUP( value, table_array, index_number, exact_match ), where the:

1. First argument is the Key Field in the first column of the table.
2. Second argument is the Table.
3. Third argument is Row from which you wish to retrieve the result.
4. The fourth argument refers to an Exact Match (expressed as “False”). Note: If you wish to retrieve and approximate value, use “True” in the fourth argument.

If No Exact Match is found for your first argument, then the VLOOKUP function will return #N/A

If you wish to concentrate only on #N/A errors, and return a blank cell when they occur, you can insert the error-handling function, and use the following formula:

IF(ISNA(VLOOKUP( value, table_array, index_number, exact_match )), “”,VLOOKUP( value, table_array, index_number, exact_match ))

If, on the other hand, you wish to Do Away with the potential for Any Type Of Error message in your result, (for instance, if the index_number is greater than the number of columns in table_array, the VLOOKUP function will return #REF!), you can use the ISERROR function as follows:

IF(ISERROR(VLOOKUP( value, table_array, index_number, exact_match )), “”,VLOOKUP( value, table_array, index_number, exact_match ))

The Error-Handling functions can be used in a variety of cases other than VLOOKUP, of course. In fact, if you get used to using these easily to master tools, you may Never Again produce a report with ugly error values in it! And that would be Very Cool!