WHY VLOOKUP (VS INDEX(MATCH) SHOWS YOUR LACK OF EMBRACING CHANGE, INNOVATION AND BETTER EXCEL SKILLS
BACKGROUND
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. See a blog post I wrote on this evolving topic.
Machines not coming they are here.
Why I disrupted my own role and you need to do the same.
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.
If you want to learn more follow us and read more about the content we are planning to release for the Future of Financial Modeling and Finance.
I encourage you to take a peek at my past articles on financial modeling which is the foundation of business decision making, planning and forecasting.
We have also redesigned our website to help you along your journey of levelling up in this space.
We will continue to discuss this topic and you can click to follow me on Twitter or LinkedIn or subscribe to our short but sweet newsletter.
Be sure to check out our pods and video page.
Here are also some past blogs that might be of interest.
Why I disrupted my own role and you should do the same?
Your Financial Model can save lives!
The Demand for Financial Modeling Skills Reaches new Heights
Data Analytics / Big Data is not Financial Modeling
Do You Model Off against the Masters of Financial Modelling?
Virtual CFO the Good, Bad and the Ugly
Lance Rubin is the Founder of Model Citizn, partner of theOutperformer, approved training provider to the Financial Modeling Institute and Group CFO for SequelCFO.
Lance has more than 20 years of combined experience working in model audit, investment banking, corporate finance, finance business partner and Fintech CFO.
Organisations he has worked with include PwC, KPMG, National Australia Bank, Investec Bank and Banjo small business lender.
We have a YouTube channel dedicates to the Future of Financial Modeling and also provide access to Models via Eloquens with thousands of viewers and downloads.
