Share this content
AIA

Excel FAQs: VLOOKUP functions

by
10th Aug 2011
Share this content

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.

Introduction

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:

Simple Excel Vlookup

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:

Stamp Duty rate VLOOKUP

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:

=IF(ISNA(VLOOKUP(A14,$A$2:$I$10,9,FALSE)),0,VLOOKUP(A14,$A$2:$I$10,9,FALSE))

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:

VLOOOKUP versus nested IF formulae

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:

Excel MATCH function

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:

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.

Tags:

Replies (10)

Please login or register to join the discussion.

avatar
By CHIPD
15th Aug 2011 11:38

VLOOKUPs

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

Surely a lookup value of 'Surrey' (if failing to specify an exact match) would match 'Somerset' rather than 'Nottinghamshire' in the example given?

Thanks (0)
Simon Hurst
By Simon Hurst
15th Aug 2011 22:54

That would have been the case if the counties were sorted in ascending alphabetical order. I was trying to make the point that failing to specify an exact match when the relevant column was not sorted in ascending order could result in a less than obvious result - as in the choice of Nottinghamshire rather than Somerset in this case. Sorry if this was not as clear as it should have been.

Thanks (0)
avatar
By AndrewSearle
16th Aug 2011 09:00

VLOOKUP

Thanks for the article. I have used LOOKUP a lot over the years but never VLOOKUP. It makes things a great deal easier for complex analysis. Just one query - can the column number be a variable?

Thanks (0)
Simon Hurst
By Simon Hurst
16th Aug 2011 09:46

Column reference as variable

Thanks Andrew - glad it was useful. Yes, the column number can be a reference to a cell value as long as it contains a valid column index number.

Thanks (0)
avatar
By derbyforester
16th Aug 2011 15:41

Column reference as a variable

Simon, thanks for a good article. I find both lookup functions very useful in producing reports that include monthly Actual/Budget values from other sheets in an excel file. By making the column number equivalent to a month (e.g. July is 7), entering the month in a specific cell and referencing this as the column number the same spreadsheet can be used to report month by month.

Thanks (0)
Simon Hurst
By Simon Hurst
16th Aug 2011 15:59

VLOOKUP() and OFFSET()

Thanks derbyforester for the kind comment and the suggestion. There's an old Excel Zone article that covers using OFFSET() in a similar way: https://www.accountingweb.co.uk/topic/excel/fun-excel-functions-1-offset-simon-hurst

Thanks (0)
avatar
By Kezza
18th Aug 2011 20:24

Vlookup Vs Index Match

Great article Simon.

I used to use Vlookups all the time however I discovered index and match about 4 years ago. I find this approach so much more flexible. As you mention, you don't have to rely on your list being the leftmost column but also if you insert or delete columns Index match still returns the correct values whereas vlookup would still have an absolute number as the offsett. Combine this with other functions and it becomes very powerful.

I do love the power of excel (I'm a bit of a geek who should get out more!).

Thanks (0)
avatar
By nickb367
19th Aug 2011 17:05

vlookp

Good article.

 

I tend to use the IF & ISERROR functions to return 0 for all error values within a lookup statement. I will certainly look at the "match" & "index" functions.

 

Cheers

Nick

Thanks (0)
Simon Hurst
By Simon Hurst
21st Aug 2011 08:47

IFERROR, ISNA

Thanks for the comments. It's also worth looking at ISNA() as a more specific alternative to ISERROR() for dealing with a failure to find a match and, if you have Excel 2007 or 2010, IFERROR() combines IF() and ISERROR() into a single function.

Thanks (0)
avatar
By Brend201
14th Jul 2015 15:34

An old one but a good one

I needed to do something today that I knew was suited to VLOOKUP but I didn't know how to get what I wanted.  This tutorial helped me get exactly what I needed within a few minutes.  And the ISNA function enhanced it nicely too.  Thanks Simon.

Thanks (0)