The VLOOKUP nightmare: Stop transaction matching with spreadsheets

Blog: Professional advantage - BPM blog

As someone who talks to many people about their business problems, I hear a lot of stories about people’s absolute dependence and servitude to Excel. Don’t get me wrong, I think it’s one of the personal productivity applications that has probably changed the world many times over. It’s quick, flexible, almost endlessly tailorable and it’s all under your own control. Brilliant. Most people in business have a sneaky spreadsheet or ten lying around to look after something or other. Some people have thousands of them. But just because you can do something in a spreadsheet doesn’t mean you should.

Recently I seem to have run into a swarm of gripes about VLOOKUP, and specifically the challenges in the office of finance where two sets of transactions have to be matched off against each other to reconcile the accounts. This regular task appears everywhere, lots of companies do their bank rec this way; insurance companies might be doing their broker and policy holder matching; fund managers might be matching clients to fund earnings. NFPs might be linking donors to donations; superannuation companies might be matching deposits to customers. Retail might be matching point of sale to their EFT receipts; ad agencies are matching their media schedules with the media invoices. In fact, for advertising agencies it is more complex because they want to match the media monitoring inputs to the schedule and the invoice, a three-way match. The list goes on, in every corner of every business in every town there is an accountant (or many) slaving over a spreadsheet riddled with VLOOKUP’s (and the dreaded #NA error). And it doesn’t actually need to be this way.

The function of matching two or more sets of transactions against each other is not typically terribly difficult, it is just terribly tedious. And it’s a perfect example of something that a machine can do better and quicker than a human. Regular, high volume, data based, predictable and the rules can be codified.

So if I’m not going to match it by hand any more, how is it going to get done? Have a look at BlackLine Transaction Matching for a relaxing alternative to your VLOOKUP nightmares.

You can read more about Professional Advantage and BlackLine here.