Microsoft Office Tutorials and References

In Depth Information

**Using the IFERROR or IFNA Function to Simplify Error Checking**

calculate than the new IFERROR function. However, this particular formula

is tame compared to some of the formulas needed to check for errors.

A common error occurs when you use the VLOOKUP function to retrieve a

value from a lookup table. In
Figure 12.8
,
the VLOOKUP function in cell D2

asks Excel to look for the rep number S07 from cell B2 and find the corres-

ponding name in the lookup table of F2:G9. This works great, returning JESSE

from the table. However, a problem arises when the sales rep is not found in

the table. In row 7, rep S09 is new and has not yet been added to the table, so

Excel returns the #N/A result.

Figure 12.8.

Figure 12.8.
AAn #N/A

#N/A error means that the value is not in the lookup table.

error means that the value is not in the lookup table.

If you want to avoid #N/A errors, the generally accepted workaround in leg-

acy versions of Excel was to write this horrible formula: