Excel is the Swiss-army knife for finance departments. It is a great flexible tool that we can´t be without. Full stop. In finance and accounting, most of us depend on Excel.

But is it great for everything? What we have found is that some companies are using Excel and vlookups for accounts and bank reconciliation. Although it´s an instinct to turn to Excel for making the life easier, it´s not always the best approach.

Here we aim to outline how to work with vlookups but also to highlight why you shouldn´t use it for your reconciliation process.

 

How to enter vlookups in Excel

–  Vertical lookups returns searches for a value (unique identifier) in columns of information (lists) and returns data associated with that value.

– Vlookups are most useful when you are repeatedly retrieving the same information for different values, rather than just a one off.

Vlookups are usually executed by the user as follows:

=vlookup(lookup_value, table_array, col_index_num)

Lookup_value is the action to search for the unique identifier, for example an SKU code.

Table_array identifies the table where the value resides

Col_index_num is the column number where the values are located

 

 How Excel executes vlookups

Excel uses a binary search technique to look up data. When searching large amounts of data, the most efficient way for a program to retrieve results is to begin at the middle of the listed data. The program determines which direction the required value is likely to be in in relation to the middle value, and searches the data on that side, repeating the method and narrowing the search until the value is found

excel reconciliation, account reconciliation, data matching, reconciliation process

Do you see a problem?

Vlookup, while commonly used in Excel, is a database function. Using Excel in this manner causes all kinds of problems, and here we have a major one. Accountants do not base their work on arounds or approximates. Yet vlookup returns only an approximate match by default, resulting in potentially false results. An optional argument, [range_lookup] can be added to the vlookup formula to obtain exact matches. The binary search method requires data that is ordered. False results can be produced  if the data is not sorted prior to execution. Your business relies on details and accuracy, and a binary search technique is liable to cause catastrophic errors

Vlookups are merely one of the vast number of issues associated with using Excel and are part of the reason Excel spreadsheets are liable to between a 0.8-1.8% error rate. While this sounds small, for a company turning over £1,000,000 this equates to between £80,000-180,000 – no small sum. Find out more about the problems with spreadsheets in our previous blog complete with free infographic.

The strength of spreadsheets is also it´s biggest issue. The flexibility makes it possible for us to practically do anything with the tool. Some companies even run their full accounting in Excel, other use it as a CRM system. The list is long of how it can be used. A great principle is however not to rely on Excel for your business processes but to look at purpose build tools instead.

After all, you wouldn’t cut your bread with your Swiss-army knife, would you?