Why VLOOKUP vs INDEX (MATCH) shows your lack of embracing change, innovation and better excel skills
Over the past year I've met many finance professionals as part of our FOFM world tour and future of finance workshops.
Despite all the hype of digital finance transformation and the rise of analytics and advanced AI and ML there is one thing that still concerns me deeply about our ability to adopt change, innovation and better Excel skills.
I'm not even referring to adopting new tools and technologies (many of which will radically change our work today and are here now) but changing how we use our existing tools.
It's our lack of embracing even the smallest change (a different Excel formula) that really highlights how fixed minded we (Finance/FP&A) still are today.
This one we cannot blame senior management for not investing in training, we cannot blame lack of time as this blog post with links will take less than 30min to review. The bottom line is we just don't like change and making an effort to embrace it.
Period!
If we really want to have more rewarding careers we have to be more open minded and learn more skills, not by just attending a course but actually implementing small changes in our daily work routines.
What is the big deal?
Fact is, if we don't learn new skills, the existing tasks we do today will be automated.
There are also many links and videos to the benefits on index match over vlookup, here are a few.
Yes, these are “just tools and not a religion” as quoted from Oz Du Soliel, but in Finance we need to explore alternative contexts and be aware of both.
Fact is many finance people don’t even know about the existence of Index and Match, and that’s the key issue. Index alone is very powerful.
But let me explain it even more simply without showing you Excel.
If you wanted to find directions to a friends house today you simply type it into Google maps or Waze.
Before these apps we had a map book.
To find their street you first went to the street index and area and found the page with that specific street name.
Then worked back from that area (range) and main roads back to your house. Sound easy?
Imagine you didn't have the index page and paged through the 100+ pages of the map book (page by page) trying to find that single street.
Stop when you find the first street name, drive to the house and hope its your friends.
I bet you would never do that? I don't think anyone does.
You would be driving for a long time finding their house.
Everyone uses the map index, except 90% of finance people stuck on vlookup with coffee breaks whilst Excel locks up in massive files.
I suspect when map books or a single map sheet were first created no index was used.
That’s how outdated most of Finance spreadsheets are today, compared to today's apps. We are way overdue on our own upgrade.
Well guess what, by using vlookup in Excel you are doing the same thing, using a long outdated and superseded formula.
Calculation speed is understandably dramatically slower, its volatile (recalculates every time you save) and it will stop on the first answer in an often badly sorted dataset with no index.
Doesn't feel like this is the best way to find your friend, does it?
Conclusion
Why most of the finance people that I meet (90%) still use vlookup is a reflection of the lack of innovation and adopting change.
For 2019, I challenge all vlookup users to explore index(match) and be aware of how it works.
If you want to change, that's your decision but at least be aware of it.
It's a measure of your willingness and ability to change and embrace something really small and very flexible, Index alone is powerful.
I would love to hear your feedback and progress on this challenge.
Share with your fellow colleagues and challenge them to be more innovative and open to change.
Remember I am not even talking about PowerBI, PowerQuery, Modeler, Modano, PowerPivot, DAX, Python or any of the many more analytics and modeling tools and languages here now.
Just an Excel formula loved and hated by many.