Disguise unavoidable formula errors by replacing them with meaningful text
by Kara Hiltz
Application:
Microsoft Excel 2000/2002/2003/2004/2007
Operating Systems:
Microsoft Windows, Macintosh
My spreadsheet allows customer service reps to look up a customer’s open balance using a customer number. However, if a customer has no current balance due, he’s taken off the lookup list. Since my VLOOKUP formula searches for exact matches only, a user who looks up a customer with no open balance sees a #N/A error. This confuses users and gives the impression that I didn’t create the formula correctly. Can I replace those formula errors with something users can understand?
It’s true that sometimes formula errors aren’t necessarily errors — but users don’t know that. In your case, you must look up the exact customer number; an approximate customer number just won’t do. Fortunately, if you’re anticipating formula errors, you can choose to replace them with a default value or text.
Identify the problem
We created a sample worksheet to illustrate the problem you’re having with formula errors. Our workbook has a lookup worksheet for users to enter a customer number. Excel uses VLOOKUP formulas to automatically fill in the customer’s first and last name and his current balance. The customer list is in a different worksheet and that’s where our VLOOKUP for
