### You might also be interested in

### Replies (10)

### Please login or register to join the discussion.

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?

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.

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?

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.

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.

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

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

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

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.

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.