Today; I will share with you my love by the Excel Lookup Formulas…
Why do I love Formulas?
The relevant benefits of using formulas make them irreplaceable!
Reporting in Excel by writing lookup formulas is harder than dragging-and-dropping fields in a Pivot table Report but in my humble opinion, there are benefits you only get using formulas.
You may have heard your peers that are fans of Pivot Tables beating Formulas, and believe me; from time to time I beat them too. Especially because of two main reasons:
- When I feel reports are easier said than done
- When I cannot create as many reports as I would like due to the time prohibitive nature of writing all the needed formulas
Given said that, I think that the lookup formulas (VLOOKUP, INDEX and MATCH, SUMPRODUCT, Array Formulas, SUMIF,COUNTIF, SUMIFS, etc) are really the gems of Excel.
Here are the 4 reasons why I love reporting in Excel using lookup formulas…
1) I love Reporting in Excel using lookup formulas because I can arrange the data in any printable format
Reason: you can prepare your report layout first and then pull the answers you need in the given cells. Sometimes your boss needs specific figures arranged in a specific way. To create this advanced reports, formulas can extract values and put them in any given cell.
2) I love Reporting in Excel using lookup formulas because I can see instant changes made at the backend list
Reason: even if I made one change or mass changes at the backend table, I see the updated output immediately in the frontend report. This is very useful when I need to have control cells in analysis.
3) I love Reporting in Excel using lookup formulas because I can create complex computations
Reason: I can embed any given pulled answer from a table into a formula and create any given math calculation with this value.
4) I love Reporting in Excel using lookup formulas because I can check the source of the output in a cell
Reason: I can press F2 and see the ranges and criteria involved. Even better, you can change the criteria or ranges involved.
If you are not experiencing all these thrilling benefits yet…
The best part of formulas is that they occupy the space you assign for them, you can create advanced Excel reports in the format your end-users need. Additionally, you feel the freedom of finding patterns based on the queries you code in cells.
Definitely, the most loved feature is the immediate update you get when changes are made in any frontend control cell.