Excel reporting with lookup formulas is really an art, these tactics I have used will help you to focus your knowledge search and to speed your report building.
I adopted some principles from my boss, then I added my own tactics.
Here you have them…
- Place your lookup table in a separate sheet. This way you keep your report clean and focused. Additionally, you can add rows or columns to the source table without affecting the report layout.
- Use absolute reference for the lookup arrays arguments. Whatever report you work in, your source table is always the same, so point to the same table wherever you move your formula. How to do it? Secure the target data once and for all. Press F4 to convert a relative reference A2 into an absolute $A$2
- Use the INDEX and MATCH formula instead of the VLOOKUP function. It allows you to search any column of the lookup table instead of moving the column to the left. Sometimes you have a report that searches in different columns of the same backend table. It does not make sense to duplicate a table to move a different column to the left in each one.
- Always keep your index columns of the backend list clean. No spaces, no leading spaces, no trailing spaces, no wrong spelling, no wrong format. Never write the lookup formula without having checked your index column.
- Test your formulas before sending a report. I call this technique test your formula in the limits. What does this mean? Search a well-known item and test the findings, test a missing item and see what happens. If all is OK, then send the report. If something does not work as expected, improve the formula and test again.
- Use array formulas to accomplish the hard work. This Excel workaround helps you to search more than one column criteria and also create your own advanced lookup constructs. There are no limits, you can search two, three or n column criteria, you can create case sensitive lookup, you can search a month from a date, etc.
- Decide what to do if the item is not found. Never allow your reports to show errors. What to do instead? Present a message, show a number, make other computations but never a cell with #N/A. Think of the NA mesasge as a system message, you are the coder and need to decode that message into something more meaningful for the end-user.
The best tactic I use is to test the lookup formula before sending. This gives me a sense of trust and achievement. Anyway, go ahead and add your own tactics and improve the above list. Remember that reporting is an essential skills at the workplace.
Tell me what tactic you use to enhance your reporting experience…