One of the most efficient and simplest methods of referencing an existing database or collection of data is through the VLOOKUP, HLOOKUP, or LOOKUP functions. The VLOOKUP and HLOOKUP functions are similar and therefore have the same strengths and weaknesses. The LOOKUP function has two forms (vector and matrix) and should be used in different circumstances. I will cover the SEARCH functions in the next article.

VLOOKUP and HLOOKUP
The VLOOKUP function can be used when a particular name is referenced in the first column of a data set, to extract information from a specific number of columns. For example, you might have the names of all your salespeople in the first column, and you’d like to see how many sales that person made on the 9th of last month. The formula would be something like the following:

=VLOOKUP(“John”,A1:M20,9), where the range A1:M20 contains all the data.
In this case, VLOOKUP will look for “John” in the first column of cells covered by the range A1:M20, which is your range for all names and data, with the information you want in the ninth column INCLUDING the first column (which includes the various names of the sales force). The VLOOKUP function looks for the name in A1:A20 and will then go through the columns you have dictated to retrieve your data. If there is no exact match, Excel will return the value immediately below the one you are looking for. So if you don’t have “John”, but there is a “Joe” and a “Josh”, Excel will retrieve the value of “Joe” for your input.

Similar in construction to the VLOOKUP function, the HLOOKUP function is a way of finding information based on rows rather than columns. This would be useful in a case where you have a series of dates at the top (such as monthly or quarterly data) and the desired information is related to total sales for a particular period. One could configure the following to find such data:

=HLOOKUP(“Q1 2008”, D1:Q20,3), where the range A1:M20 contains all the data.
In this case, HLOOKUP will look for the time period “Q1 2008” in the first row of the range D1:Q20 and return the information 3 rows down, including the first row, which is where the relevant information you are looking for is found. In this case, HLOOKUP looks for “Q1 2008” in D1:Q1 and returns the value the number of rows away that you specified in the formula. As in the case of VLOOKUP, if there is no exact match, the results will be from the row immediately below what you are trying to find.

Note that these two functions assume that the order of the first column (VLOOKUP) or row (HLOOKUP) is ascending. If that is not the case, none of these functions will work correctly. To avoid this, we can configure the function to find the exact match we need by adding an additional command in the formula bar. Using our salesperson example again, let’s assume the names are in some other order than ascending, such as descending by total sales last year or by date of birth. We would do the following to adjust to such an order:

=VLOOKUP(“John”,A1:M20,9,false).
The inclusion of “false” at the end of the formula means that Excel must find the exact match targeted. If there is no exact match, the formula will return #N/A. You would use this same format for HLOOKUP when you want to find the exact match.

One major drawback of the VLOOKUP function is that if you insert or delete a column, the function will not compensate. In other words, if you delete a column and the formula you created references column number nine, it will still reference number 9, which would now be the next column. You would have to go back to the VLOOKUP formula and change the column reference number. The same effect occurs if you change the number of rows when you use the HLOOKUP function. You need to be aware of that as you start to modify the data range you’re targeting, and make sure that the formula correctly provides the information you need.

In summary, the VLOOKUP and HLOOKUP functions are good for spreadsheets that are unlikely to change construction (insert or delete columns or rows). It’s a good basic formula for less dynamic spreadsheets, and is easily implemented by newcomers to financial modeling.