Excel FAQs: VLOOKUP functions

Excel’s VLOOKUP function is probably second only to pivot tables in popularity for management reporting and accounting users. Simon Hurst uncovers some lesser known 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?

Further reading

Continued...

» Register now

The full article is available to registered AccountingWEB members only. To read the rest of this article you’ll need to login or register.

Registration is FREE and allows you to view all content, ask questions, comment and much more.

Comments

VLOOKUPs

CHIPD | | Permalink

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?

shurst's picture

That would have been the case

shurst | | Permalink

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.

AndrewSearle's picture

VLOOKUP

AndrewSearle | | Permalink

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?

shurst's picture

Column reference as variable

shurst | | Permalink

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.

Column reference as a variable

derbyforester | | Permalink

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.

shurst's picture

VLOOKUP() and OFFSET()

shurst | | Permalink

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

Vlookup Vs Index Match

Kezza | | Permalink

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!).

vlookp

nickb367 | | Permalink

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

shurst's picture

IFERROR, ISNA

shurst | | Permalink

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.