X rated content -what was hot and not about our old friend the Vlookup.

Updated: 4 days ago

Knowing how to look up data in a complex data set is the digital equivalent of knowing how to flip through your Filofax and locate the deets 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 database have grown, so too have our lookup 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. It’s successor should only make data more accessible, more reliable and more accurate for the masses.
The vlookup - it's good bad and ugly impact in the workplace. The vlookup was a tricky mother function. It frequently entered the lives of junior 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 got introduced to the vlookup in up and it’s even harder to execute hlookup on the same day, very early in my non-technical career. The person who eplained it started with explaining table arrays, navigating data and the watch out around columns indexing. That one day of training kickstarted a comfortable place for working with big data. Understanding how to lookup data vertically and horizontally and return corresponding information from parallel columns/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 absolutely meaningless if you don't understand that h exists. It looks like code, it feels like another language. And it is code, v is for vertical and h is for horizontal.
Many people were counting columns visually and not using the row counter to index the column they wanted to lookup. This was taxing and open to errors. Particularly when data got BIG and people were hiding columns that were not getting counted.
It was a hungry mother 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 criterea, caught a lot of people out. No one wants to have write another function to navigate ugly N/As but when the absolute match function wasnt set and the formula returned the next sequential match and added rouge data, that rocked the trust boat up the chain! Confidence once lost, is very hard to rebuild. It 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 disparate databases and add richness to analytical data layers when databases. Planning was revolutionised. 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 x lookup and there are many great demo videos on the internet. The internet is littered with demos of Excel functions and the contribution from the community is epic and very very good. 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.
Contact to access independent help with your workplace data literacy.

Related Posts

See All

During lockdown when we were constrained to our 5 km radius I was walking daily to keep sane. Occasionally I’d walk alone but more often than not I would walk with good company. On one such walk, my f