Objective: To extend the range of Excel problems that can be solved using Excel's lookup functions and to reduce the number of VLOOKUP() errors.
VLOOKUP() is a very popular function amongst accountants, with many seeing the ability to use it as a key indicator of advanced Excel wizardry. It is also a very controversial function: some claim that it is never the right lookup function to use, whilst others, me included, cite it as Excel's most error-prone function. Perhaps the key feature of VLOOKUP() that needs to be understood is its crucial fourth argument.
This short series will cover VLOOKUP() and the other lookup functions. In part 1 we will start with an introduction to lookup functions in general and a more detailed examination of how to use VLOOKUP() itself.
The lookup family
VLOOKUP() is one of a range of lookup functions, each of which tries to match a single value with a value in a range of other values.
- VLOOKUP() tries to find a matching value in the first column of a range of cells and returns the value from the same row of a designated column in the range.
- HLOOKUP() tries to find a matching value in the first row of a range of cells and returns the value from the same column of a designated row in the range.
- LOOKUP() is included for 'backward compatibility'. Excel help suggests the use of other lookup functions in preference. LOOKUP() has two 'forms'. The array form is very similar to using VLOOKUP() or HLOOKUP() but with much less flexibility. However, the vector form does something that the other two lookups don't do, it enables you to find a value in one row or column and return the value in the same position of another, non-contiguous, row or column. A vector is a one-dimensional list of cells – either part of a row or part of a column. LOOKUP() doesn't mind if one of its argument vectors is in a row and the other in a column, each is treated as a simple list of cells.
- MATCH() is similar to the other lookups in that it finds a matching value in a range of cells but, unlike the other lookups, it doesn't return a corresponding value but, instead, returns the position of the matching value. So, if the seventh value in a list is the first that is matched, the function will return the value 7. For this reason, MATCH() is normally combined with the INDEX() function which can return the value in the cell in a particular position within a list of values: e.g. the value of the seventh item.
An exact VLOOKUP()
We will be using VLOOKUP() rather than HLOOKUP() as tables are usually arranged vertically rather than horizontally, but HLOOKUP() works in exactly the same way apart from swapping rows and columns (think dancing and desire).
We'll start off by looking at the simplest use of VLOOKUP() to find an exact match in the first column of a two dimensional range of data, and to return the corresponding value from the row that contains the matched item, from a different column. In this example our client information is set up in an Excel Table called Clients and we want to find the discount rate applicable for each row of our expenses Table:
Our VLOOKUP() formula uses four arguments:
The first argument refers to the cell containing the value that we want to find in our clients Table. This is the value of the Client ID column of our expenses Table for the current row. In the case of the formula in cell E5 this will be the value 1.
The second argument refers to the range containing the value to be matched in the first column and the value to be returned in any column. Because our client data has been entered as an Excel Table, this just uses the Table name 'Clients'. If the client information was in a simple range of cells rather than an Excel Table, the cell references would need to be made absolute using the dollar signs so that it could be copied correctly to the other cells in the column.
The third argument defines which column to use to return our answer value. In this case the discount value is in column 3.
The fourth argument is, unfortunately, optional. It is one of two values. TRUE or FALSE (you can also use 1 instead of TRUE and 0 instead of FALSE). In our example, we have entered FALSE because we want our match to be exact. If an exact match cannot be found, we need to return an error value rather than picking the value for another client. If we had failed to enter a fourth argument, Excel will default to using TRUE which signifies an approximate match. We will define approximate in this context shortly but, for now, the important thing to note is that, unless the first column of our lookup table is sorted in ascending order, setting the fourth argument as TRUE, or leaving it out altogether, will result in our function matching what might seem to be a completely random value in our table, or failing to find a match at all.
Here we have changed our formula to omit the fourth argument, thereby defaulting to the user of TRUE and an approximate match and we have sorted our table using our discount column rather than our first, Client ID, column. As you can see no match is found for Client ID 1, despite it being clearly present in our client table, and Client ID 3 seems to have been matched against Client ID 2 in our lookup table:
Why you should always avoid using the approximate form of VLOOKUP()
You might come across advice to only ever use the exact form of VLOOKUP() and therefore to only ever create VLOOKUP() formulae where the fourth argument is set to FALSE. Indeed, it is probably a very good idea to make sure that you always do enter the fourth argument so that you never accidentally end up with an approximate match just because you have forgotten the fourth argument completely.
…and why you shouldn't
However, it is probably better to understand what 'approximate' really means when working with VLOOKUP() and the other lookup functions, because there are many practical situations where an approximate lookup is the simplest way to find the correct value. When working with financial data, one of the best examples of this is the use of an approximate VLOOKUP() function to find which band a value falls in:
Next time we will explain exactly what approximate means and go on to see why Excel users frequently come to blows over whether anyone should be allowed to use VLOOKUP() at all.
About Simon Hurst
Simon Hurst is the founder of technology training consultancy The Knowledge Base and is a past chairman of the ICAEW's IT Faculty.