It is not breaking news, but something that anyone working on an Excel Report using formulas and functions, will know the keys of successfully using lookup formulas. They are vital for making almost any spreadsheet or dashboard.
That is why when you learn how to extract information from Excel tables, you will be truly excited, because you will be able to go into a deep and profound understanding of spreadsheets and the use of Excel formulas .
The Excel formulas used to create an Excel Report are perceived as very problematic. Here are 9 simple tips you can apply right now that should help a lot to avoid problems.
Know Your Formulas!
- Be aware of the methods you have available: VLOOKUP, INDEX and MATCH, SUMPRODUCT, Array Formulas, COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS
- Dedicate time to prepare the back end index column so you avoid future “not-matching headaches“. To avoid wrong spelling, delete leading/inter/trailing spaces, revert numbers stored as texts, put dates in proper serial number format, etc.
- Have an escape plan for a legitimate NA, a report with NA errors looks non-professional and untrustworthy
Do it Like an Expert!
- Avoid hard coded arguments. In case you need to make a change to various instances of the formula across the sheet, you only change the source cell
- Learn how to reference lookup arguments, especially dates
- Use absolute reference when specifying the arrays in lookup formulas. This way when you copy and paste the formula, it will always points to the correct data set
Make a Backup Plan!
- Check various lookup values to increase the security of your formula. Test if your formula retrieves the values as expected from the backend table
- If you get in a NA trap, don’t despair, use a plan to escape. Check for not-matching issues on the left index column first, then move to any issue on the lookup formula
- Use the simplest formula version, for example: SUMIF instead a SUM IF array formula , if you use Excel 2007, use SUMIFS, COUNTIFS, AVERAGEIFS instead of array formulas
I use the above techniques all the time. You can also get the results you need by using them on a regular basis.
Hope you like the tips. Happy Excelling… 🙂
Also See