Excel’s VLOOKUP function is probably second only to pivot tables in popularity for management reporting and accounting users. Simon Hurst looks into its potential applications.
There are many situations when you might need to find a match for a value in an Excel cell elsewhere in your workbook. Usually you need to locate the matching value cell and then return the value from a different cell in the same row or column. Lookups can also be a more elegant and flexible alternative to nested IF() functions. Intensive use of complex lookups is also an indicator that it might be worthwhile ensuring that a spreadsheet, rather than a database, is the best tool for the job.
Which lookup functions are available in Excel?
There are three actual lookup functions:
- LOOKUP() – mainly included for backward compatibility.
- VLOOKUP() checks a column of data for a value and returns a value from a different column in the row in which the match is found.
- HLOOKUP() checks a row of data for a value and returns a value from a different row in the column in which the match is found.
Because it’s more usual to arrange data in columns rather than rows, VLOOKUP() tends to be much more commonly used than HLOOKUP() so the FAQ will concentrate on VLOOKUP() – if you are using HLOOKUP() instead it just requires the mental gymnastics of moving your brain through 90 degrees.
Are there alternatives to the lookup functions?
There are two Excel functions that, when used together, can achieve similar results to the lookup functions and indeed do things that neither VLOOKUP() nor HLOOKUP() can cope with. MATCH() finds the position of a cell that matches a value and INDEX() allows us to use the value in a cell that we identify by its position.
David H Ringstrom’s recent piece on SUMPRODUCT shows how this function can be used to apply multiple criteria within a summing formula rather than using VLOOKUP.
How does a simple lookup work?
In this example, we want to specify the name of a county and find the number of points they have scored:
Our VLOOKUP() function uses 4 arguments:
- Lookup value – the value we want to find
- Lookup table – the block of cells containing the value to find and the value to return. The column containing the items we want to match our lookup value against must be the leftmost column
- The column – the number of the column containing our result – the leftmost column is column 1. In our example, we want the 9th column containing the ‘Pts’ values
- The type of match – where we want an exact match we have to enter FALSE as our last argument.
VLOOKUP() finds a match for our term ‘Sussex’ in cell A7 in the leftmost column of our table A2:I10. Staying in the same row it moves across to the ninth column to return the value of 118 from cell I7
Why do I have to worry about the fourth argument – VLOOKUP() seems to give the same result without it?
Perhaps surprisingly, the default for the fourth argument which will be used if no argument is specified, is TRUE rather than FALSE. With the default of TRUE, VLOOKUP() performs an approximate match. Approximate matches don’t just find a “close enough” value, they work in a very specific way. First of all, for VLOOKUP() to work reliably with an approximate match, the leftmost column of the table must be sorted in ascending order. VLOOKUP() will then match our lookup value with an exact match or with the largest’ value that is less than our lookup value.
In this example we are using the stamp duty thresholds and trying to find the rate applicable to our particular property value. The cell containing the property value has been given the name PropertyVal:
The largest value less than 480,000 is 250,001 so we return the value in the second column of our table on row 3: 3%
To return to our earlier example, failing to specify an exact match would lead to a lookup value of ‘Surrey’ matching ‘Nottinghamshire’ rather than returning #N/A.
How do I handle errors when the function needs an exact match and there isn’t one?
When a lookup function or MATCH() fails to find a match it returns a #N/A error. If you want to return a blank or zero instead then you can use the ISNA() function together with IF(). For example:
It’s worth noting that, as far as MATCH() and the lookup functions are concerned, a text 4 isn’t the same as a number 4 and one won’t match the other.
Is it better to use VLOOKUP() rather than nested IF() functions?
For our stamp duty example, we could have used a series of nested IF() functions instead:
As well as creating a much less complicated and more understandable formula, it would be much easier for our VLOOKUP() version to adjust to the introduction of more levels. We would just need to change the table range in our function or, even better, if we were using an Excel 2007/2010 table or an Excel 2003 list, it would adjust automatically.
When might MATCH() and INDEX() be needed rather than VLOOKUP() or HLOOKUP()?
As well as exact and “largest value less than” approximate matches, MATCH() has a third argument which allows it to cope with “smallest value greater than” approximate matches. So, rather than just TRUE and FALSE, MATCH() has three match type values:
- 0 Exact
- 1 Less than
- -1 Greater than
Because MATCH() just finds the position of an item in a one dimensional list, it can be used to find both a row and a column position. This means that, combined with INDEX(), it can find the cell at the intersection of two values. In this simple example we are finding the value for sales for Asia in Feb:
Because MATCH() can be used to find both rows and columns by finding a match, rather than just specifying the ‘number’ of the column or row, it can adjust more automatically to tables of data subject to changes in the number or positions of columns (VLOOKUP()) or rows (HLOOKUP()).
VLOOKUP() is limited to finding a match in the leftmost column and returning a value from a column to the right. Because MATCH() just works with positions, it can look left or right and up or down.
How do I find out more about lookup functions?
For further information and ideas have a look at:
- Vlookup not working - data format problem
- Excel compendium 2007 - lookup
- Content tagged as ‘vlookup’
About the author
Simon Hurst is a former chairman of the ICAEW IT Faculty and runs The Knowledge Base, a consultancy dedicated to helping accountants make effective use of technology. He is a regular contributor to AccountingWEB's ExcelZone and the author of '100 Time-saving Tips for Microsoft Office'. For more information, visit The Knowledge Base website.