Formulas discussed: Lookup, Vlookup, Hlookup, Index, Match.
Just want the table to show you which formula to pick – click here. The workbook used throughout this post is available here.

This article and the attached spreadsheet will discuss the various methods of searching for a data point in an Excel table and return the value in a corresponding row or column, without having to manually look through the table yourself. Common examples include searching for a sales result for a particular period, or matching a data field to a person’s name.

Vlookup

Vlookup is used where the search term is in the left column of the table (a vertical search). It is probably the most common lookup function used, as most people find the syntax easy to follow (personally, I always found Lookup the easiest to follow). For a full discussion of the syntax, the Office website has a nice article, or there are some good YouTube videos as well, for example, this one.

Hlookup

Hlookup works in the same way as Vlookup, except that it searches for the search term in the top row of the table.

Lookup

Lookup works slightly differently, in that you don’t enter the full table in your formula, you only enter the search term, the search row or column, and the result row or column, being careful to ensure that the search and result fields start and end at the same location.

Weaknesses of Vlookup, Hlookup and Lookup

The major weakness of Vlookup, Hlookup and Lookup is that their reliability can not be guaranteed if the search data is unsorted. The example below (which is contained in the attached workbook for a more complete review) shows that depending on the order and the term being searched for, Vlookup, Hlookup and Lookup can return one of the correct answer, an incorrect answer, or no answer at all.

This is because each of these functions searches for an approximate match rather than an exact match. The start of this list is in ascending order, and these functions start at the top of the table and search for the last value that is either equal to or smaller than the search term. So “McDonald” produces an absolute match, “Brown” can’t be found because the first terms are already larger than it, and “Smith” returns the result for “Rogers” as following “Rogers” is “Thomson” which is larger than “Smith”.
The other common source of errors when using Hlookup and Vlookup (but not Lookup) occurs when a new row or column is inserted (or one is deleted) in between the search array and the result array. People will commonly manually count which column/row the result array is in, and enter that number as a hard-coded number in the formula (in the examples, column 2). If a new column/row is inserted, the 2 will not change automatically and incorrect data will be returned. This can be fixed by using a Match formula and searching for the correct column heading (in this case “Birthday”) or by using the column/row counter formulas Column, Columns, Row or Rows as appropriate.

An alternative – Using Index and Match

The simplest form Index function requires at least two arguments – an array containing the desired results (either a single column or row), and an argument specifying which item in the array to return, in the form:

=INDEX(array, item number)

By substituting a Match formula for “item number” you can search for the desired search term. Match is in the form:

=MATCH(search term, search array, control number)

Match has greater flexibility than using the previous lookup functions, as you can explicitly control whether you want an exact match or an approximate match through the “control number” (0 for exact match, 1 for an ascending list approximate match, -1 for a descending list approximate match). If the list is unordered as in the previous examples, then using 0 as the “control number” ensures you always get the correct result.

If you incorrectly tell it the list is ordered, incorrect results may be obtained (although not necessarily the same incorrect results as from Vlookup, Hlookup and Lookup).

Searching for a data point that doesn’t exist

Users may on occasions enter a search term that doesn’t exist. Take for example the following quarterly sales table with a date format that only shows a month and year. Each date is in fact the last day of a calendar quarter.

The spreadsheet developer has a choice of using Vlookup, Lookup or Index/Match to interrogate the data table. If the end user incorrectly puts in the first day of a month instead of the last day, or types “Sep 13” (which Excel automatically enters as the first day of the month and converts it to a date format), Vlookup and Lookup will return the result for the previous quarter.
Alternatively, using Index/Match with:
(i) an exact match, will return an error;
(ii) an ascending list, will return the same result as Vlookup and Lookup; or
(iii) a combination of Matches, can return the result for the quarter that the entered date occurs in.

The most important thing as the developer is to decide what you want the result to be if a user enters an incorrect date. Using Index/Match allows you to explicitly control whether that result would be an error, the previous quarter or the matching quarter, whatever your desired result is, whereas with the three lookup functions, Microsoft has already decided that for you.

2 dimensional searches

You may need to search for two terms, one occurring across a row, and the other down a column. For this example, we will use a condensed quarterly profit statement and we want to search for a particular statement line in a particular quarter.

This can be done through using a Vlookup with a Match, Hlookup with a Match, or Index with two Matches.
Vlookup – searches for the statement line, and uses a Match to control which column to return based on finding the date.
Hlookup – searches for the date, and uses Match to control which row to return based on finding the statement line.

Index – uses the syntax

=INDEX(array, row counter, column counter)

where “array” is the table without the column/row headings, “row counter” is a Match searching for the statement line and “column counter” is a Match searching for the date.

The important thing to note is that, as with previous examples, Vlookup does not necessarily return the correct results as the statement line names are not in alphabetical order. Hlookup does work as it is only searching for the dates, which are in order, and Index also works. For more control with potential missing dates, combine it with the formulas discussed in “Searching for a data point that doesn’t exist” above.

The really technical bit – calculation speed

This seems to be largely a matter of opinion than fact. As a junior modeller, I was told that Vlookup and Hlookup calculated faster and used less processing capability than Lookup, and that was why people used them (a habit I never got into).
According to Microsoft, Vlookup is around 5% faster than a corresponding Index/Match function. Bigger improvements are available from using a sorted list and searching for an approximate match (so that it stops looking as soon as the threshold is breached), rather than searching for an exact value (which looks at every cell in the range, even after it has found a correct answer).
Other sources provide data showing that Index/Match is actually faster than Vlookup. Performance can be improved further by using a single Match result across multiple Index formulas if you are seeking to return multiple fields from the same data source.

Conclusion

While personal preference will continue to dictate which formula different people use for data searches, I hope that you will consider trying out Index/Match if only for the greater control it gives you of how your results are displayed. To help you on your way, below is a ready-reckoner you can refer to to help you decide in which situation you should use each of the lookup functions.

Happy spreadsheeting!