After my post about postcodes earlier this week, a couple of people asked me what a ‘lookup table’ was in that context. I thought I’d write something quick about their use in the context of spreadsheets, because they’re such a timesaver.
You can probably divide the world into people who have no idea about VLOOKUP tables in spreadsheets, and those who use them every day. Until recently I was firmly in the former category, but now I’m part of the latter group.
VLOOKUP is a function in Microsoft Excel (with analogies in other spreadsheet programmes like Google Sheets and Open Office). As it’s name implies, it allows the user to automatically ‘look up’ a particular value from a list.
So in the context of my previous post, a VLOOKUP function can take (say) a list of post code prefixes, cross-reference each with another table, and instantly list the UK region where that postcode is situated.
I’ve been working with a great deal of membership and charitable donation data recently, and the VLOOKUP function is essential to that task. For example, it allows me to create a list of email addresses based on a list of membership numbers; or to link a series of direct debit donations to a list of individuals, based a common reference number.
Ever since I worked out how to use the VLOOKUP function, previously time consuming tasks now take seconds to complete. Of course it is always easy to search for a single name in a database and edit the record… but when one has to update hundreds or thousands of records, accessing each record individually would take too long.
Prior to my VLOOKUP revelation, there were many cases where I spent hours accsising records manually in order to list the information I required; Since that was a drag, I also spent far too much time sorting the data in complex ways in order to (eventually) attach crucial information to each row of the spreadsheet.
Some of you will be reading this with a chuckle, because I am stating something that seems rudimentary to you. Maybe you work in finance or computer science? However, the fact that it took me so long to work out how to use this simple function makes me think that there are many other people—many of them working in the strapped-for-cash, no-budget-for-training charitable sector—who would benefit from similar enlightenment. The number of potential people-hours lost to inefficient spreadsheet usage makes me shudder.