As you know, the fourth argument of the VLOOKUP worksheet function determines whether VLOOKUP finds an exact match or an approximate match. If you set the last argument to False, and there is no exact match in the lookup list, the formula will return #N/A!.
In Excel 2003, the best way to capture this error, is to use the ISNA worksheet function, such as in this example:
This checks to see if the formula returns the NA error and, if so, returns an empty string (to look like a blank cell). There is a big downside to this: For each cell this is used in, Excel has to calculate the VLOOKUP function twice. If you have a lot of them, it can really slow things down. Unfortunately, I don’t know of a better way.
In Excel 2007, however, a new worksheet function was introduced. IFERROR not only shortens your formulas, but can speed up calculations. In 2007, it looks like this:
It only evaluates VLOOKUP once, which is nice. The downside to this function is that it masks errors other than NA. For example, if you misspelled MyTable, you would not receive a #NAME? error, you would get nothing and possibly reach the wrong conclusion. So don’t make an mistakes. :)