Chapter 14: Using Formulas for Matching and Lookups
14
Hiding errors returned by lookup functions
So far, we’ve used FALSE for the last argument of our lookup functions so that we return
only exact matches. When we force a lookup function to return an exact match and it can’t
find one, it returns the #N/A error.
The #N/A error is useful in Excel models because it alerts you when a match cannot be
found. But you may be using all or a portion of your model for reporting, and #N/As are
ugly. Excel has functions to see those errors and return something different.
Figure 14.5 shows a list of companies and CEOs. The other list shows CEOs and salaries. A
VLOOKUP function is used to combine the two tables. But we obviously don’t have salary
information for all of the CEOs, and we have a lot of #N/A errors.
=VLOOKUP(C3,$F$3:$G$11,2,FALSE)FIGURE 14.5
A report of CEO salaries
In Figure 14.6, the formula has been changed to use the IFERROR function to return a
blank if there’s no information available.