Working with spreadsheets can make accounting tasks much more efficient and reliable once we have learnt to use the required functionality.
However, in order to achieve that, we have to combine accounting knowledge with spreadsheet skills and for many of us, bring both areas together is a real challenge. In this article we’re going to work through how to use Excel’s VLOOKUP function.
When large amounts of information are kept in a spreadsheet a variety of accounting tasks can be performed. These may use different ranges of data, but as they are from the same source there is no need to duplicate the raw data.
Managing a large spreadsheet, so that we can find and use the information we need, can be challenging though and is best done if we can use functions such as LOOKUP to help us.
What is a LOOKUP function?
There are three LOOKUP functions in Excel, all of which look for a match with a value you supply:
LOOKUP – looks in a single row or column
Rarely used now as it has been superseded by the vertical and horizontal options that produce the same result more easily.
VLOOKUP – looks in multiple columns
As spreadsheets are more commonly organised by columns, VLOOKUP is used the most frequently out of the three functions.
HLOOKUP – looks in multiple rows
Works in the same way as the VLOOKUP but looks in rows instead of columns. The thought process and elements involved in writing both are similar but applied to data ranges running horizontally instead of vertically.
Using a LOOKUP for management accounting
Let’s think about how a VLOOKUP could be utilised in the context of management accounting.
Download an extract of the spreadsheet if you would like to have a go as you read through the article. Please note that some rows have been hidden in the images below.
GM Retail has a master spreadsheet that contains information about its inventory. The purchasing department has been asked to re-order a number of products and the manager of the warehouse has supplied the relevant product codes including 1675 and 4015. The VLOOKUP function can be used to identify information about each product.
The VLOOKUP needed in cell B2 in order for the supplier of product code 1675 to be shown is:
The VLOOKUP can be decoded as a combination of the annotation above and the following transcription:
Look in B1 and compare the data in it to all the data in the range A6 to H135, find a match in the leftmost column and then show the data contained in the cell in the 7th column on the same row. The match must be exact.
The brackets, commas and colon are all essential for the function to work and if they are omitted or used incorrectly then the formula is unlikely to be accepted or an error message will be returned. Excel will automatically add the correct basic ‘grammar’ though, if the function arguments tool is used.
Click into the cell you want the VLOKKUP in and then click on ‘fx’ just to the left of the formula bar. Then search for and/or select the VLOOKUP function:
The argument box for the VLOOKUP function has four boxes which splits the formula up into its four elements:
Lookup_value Enter the reference of the cell that contains the information you are supplying
Table_array Enter the range of columns and rows that you want checked for a match with the cell in box 1
Col_index_num Enter the number of the column that will contain the information you require, for example, column C will be number 3
Range_lookup Enter False if an exact match is required or True if a non-exact match can be allowed
Note: The VLOOKUP, with the required basic ‘grammar’, is produced in the formula bar as the information is entered into the argument box.
Using the function argument tool gives a structure to writing the formula for a VLOOKUP and is really helpful if, like me, you struggle to write formulas from scratch. However, there are a couple of issues to note about the VLOOKUP function because without be mindful of them, the formula won’t work correctly, even if we have used the function arguments tool to help write it.
Firstly, VLOOKUP looks in the leftmost column and return information to the right only.
Given the structure of the data in our inventory master sheet and the table array selected (all the columns and rows containing information) we can look up anything from a product code because A is the leftmost column and everything else is to its right:
However, if we wanted to look up information based on the re-order level, a VLOOKUP would only be able to return the supplier or re-order time, and the table array would have to be selected to start from column F:
Knowing which way the VLOOKUP looks, means that we are in a position to either alter the position of data in a spreadsheet in order to have the right information in the leftmost column or we can select the table array appropriately.
Secondly, VLOOKUP will default to matching approximately unless told otherwise.
The final element of the formula instructs Excel to either allow a non-exact (TRUE) match or requires an exact (FALSE) match. Unless you state that a FALSE (exact) match is required, Excel will return an approximate match if it cannot find an exact match. If this last section of the formula is omitted, it will still work as if the word ‘TRUE’ had been included.
If we want to know the cost of a product we need an exact match:
However, if we apply a mark-up percentage based on the first digit of the product code we would need to allow a non-exact match:
Notice that the table array has had to be changed to make J the leftmost column.
Lastly, we need to be aware that if a non-exact (TRUE) match is used, either specified or by default, then the information in the spreadsheet most be sorted by the leftmost column either alphabetically or numerically in ascending order. If the raw data isn’t sorted in this way then the VLOOKUP is likely to return an incorrect value.
See if you can use the VLOOKUP function to find out the supplier, unit cost and correct mark-up percentage for product code 4015, then click on this link to check your answers.
Look out for other articles in this Excel ‘How to….’ series.
Gill Myers is a self-employed accounts consultant. She has taught AAT qualifications since 2005 and written numerous articles and e-learning resources.