Excel FAQs: VLOOKUP functions

Simon Hurst
Columnist

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?

The full article is available to registered AccountingWEB.co.uk 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.

Replies

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)
By shurst
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)
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)
By shurst
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)
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)
By shurst
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)
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)
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)
By shurst
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)
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)