Blog Posts Process Management

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.

Need an answer about BlackLine?

Thank you for your enquiry. We’ll be in touch shortly.
Send us another message

The post The VLOOKUP nightmare: Stop transaction matching with spreadsheets appeared first on

Leave a Comment

Get the BPI Web Feed

Using the HTML code below, you can display this Business Process Incubator page content with the current filter and sorting inside your web site for FREE.

Copy/Paste this code in your website html code:

<iframe src="" frameborder="0" scrolling="auto" width="100%" height="700">

Customizing your BPI Web Feed

You can click on the Get the BPI Web Feed link on any of our page to create the best possible feed for your site. Here are a few tips to customize your BPI Web Feed.

Customizing the Content Filter
On any page, you can add filter criteria using the MORE FILTERS interface:

Customizing the Content Filter

Customizing the Content Sorting
Clicking on the sorting options will also change the way your BPI Web Feed will be ordered on your site:

Get the BPI Web Feed

Some integration examples