Excel Report – A Simple Procedure You must implement to Increase your Confidence in your Lookup Formulas

If you feel your stomach in your throat each time you email a report to your boss, this tactic is for you.

I have used this handy secret with great results for my mental health. No joke!

Here’s the tactic…

  1. Once you have done all your lookup stuff, make a final test for the formula (VLOOKUP, INDEX and MATCH, Array Formulas, etc.). Open the file and perform a what-if analysis as your boss would do it.
  2. Gain confidence by searching two or three well known values. For example, if you are searching Names, pick the most obvious and see what happens. The returned attributes will give you confidence that the formula works.
  3. Proof your formula against crashes. Evaluate how your formula behaves with missing values. Now is the time to search missing values, this way you can test your trap error messages.

A good approach I use,  is imagining that the end-user opens the email and starts making what-if tests. I imagine his stomach when he receives a #N/A message.

By assigning enough pain to the fact that your boss gets unexpected results, then you will have the energy and commitment to check your lookup formulas against bugs.