Knowing how to look up data in a complex data set is the digital equivalent of knowing how to flip through your Filofax, locate the details of a contact, and be certain it’s the correct person (based on your self-styled cataloguing of your contacts). As tech has improved and digital databases have grown, so too have our ‘look up’ tools. The humble VLOOKUP and its laid-back friend the HLOOKUP are now redundant and we should celebrate that. They changed the world of work forever. Their successor should make data more accessible, more reliable and more accurate for the masses.
The VLOOKUP – its good, bad and ugly impact in the workplace
The VLOOKUP was a tricky function. It frequently entered the lives of junior business administrators whose technical introduction to its beauty was without full appreciation. There were people copying VLOOKUPs without a real understanding of how they operated and why the function was written into the spreadsheet they had inherited.
I was one of the lucky ones, I was introduced to the VLOOKUP and its even harder to execute HLOOKUP on the same day very early in my non-technical career. The person who explained it started with table arrays, navigating data and the ‘watch outs’ around columns indexing. That one day of training kick-started a comfortable place for working with big data sets.
Understanding how to look data up vertically and horizontally – and return corresponding information from parallel columns or rows – ushered in the democratisation of home-made calculators, ready reckoners, predictive modelling, and faster accounting. They paved the way for a new era of analytics around richer business metrics. In theory the humble LOOKUP sounds easy, so how was it lost on so many who'd been on the Excel training? Training that senior management teams in their droves deemed not to be a return on investment.
Here are my workplace observations from the front line.
The V is meaningless if you don't understand that H exists. It looks like code and it feels like another language. And it is code, V is for vertical and H is for horizontal.
Many people were counting visually and not using the counter to index the column or row they wanted to look up. This was taxing and open to errors. Particularly when datasets got bigger and people were hiding columns that were not getting counted.
It was a hungry function. A lot of energy was spent continuously looking down and across table arrays automatically recalculating all the while. For people who adopted the index match, the VLOOKUP is well and truly a thing of the long past.
Absolute matching criteria caught a lot of people out. No one wants to have to write another function to mitigate ugly N/As, but when the absolute match function wasn’t set and the formula returned the next sequential match it added rogue data that rocked the trust boat up the chain! Confidence once lost, is very hard to rebuild.
Also, the VLOOKUP only looked right, and not left. What about all those columns that needed indexing left of the lookup criteria?
So what do we all owe the VLOOKUP. Why was it a good guy?
It’s hard to quantify how many millions of hours of work were made redundant by the VLOOKUP.
It helped us connect datasets trapped in separate systems and add richness to analytics.
Planning was revolutionised. We became more informed.
It was accessible to all business people and helped technical and non-techs articulate and innovate great developments that leveraged a lot for operational success.
More hours were saved with more happy customers.
So why don’t we need it now?
Well, we have the XLOOKUP and there are many great demonstration videos on the internet. The internet is littered with demos of Excel functions and the contribution from the community is epic. Like the Excel training of yesteryear, none of these videos will help with the adoption of these skills if the application is not appreciated.
That is why ivity exists - to help modern data skills flourish in your workplace.