VLOOKUP and HLOOKUP Functions in Microsoft Excel

One of the more efficient and simple methods to reference 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 array), and should be used under different circumstances. I will cover the LOOKUP functions in the next post.

VLOOKUP and HLOOKUP

The VLOOKUP function can be used when referencing a particular name in the first column of a set of data, to pull information a specific number of columns away. For example, you could have the names of all of your salespersons in the first column and you would like to see how many sales that person made on the ninth day of the past month. The formula would look something like the following:

=VLOOKUP("John",A1:M20,9), where the range A1:M20 contains all of the data.

In this case, VLOOKUP will look for "John" in the first column of the cells covered by the range A1:M20, which is your range for all of the names and data, with the information you want in the ninth column INCLUDING the first column (that includes the various names of the salesforce). The VLOOKUP function searches A1:A20 for the name and will then look over the columns you have dictated to retrieve your data. If there is no exact match, Excel will return the value that is immediately less than what you are seeking. So if you had no "John" but there was a "Joe" and a "Josh," Excel would retrieve the value of "Joe" for you input.

Similar in construct to the VLOOKUP function, the HLOOKUP function is a way of finding information based on rows instead of columns. This would be useful in a case that has a series of dates across the top (like monthly or quarterly data) and the desired information is related to total sales for a particular period. One could setup the following to find such data:

=HLOOKUP("Q1 2008", D1:Q20,3), where the range A1:M20 contains all of the data.

In this case, HLOOKUP will search 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 seeking is located. In this case, HLOOKUP searches D1:Q1 for "Q1 2008" and returns the value the number of rows away that you specified in the formula. As in the case of VLOOKUP, if there is not an exact match, the results will be from the row immediately less than what you are trying to find.

Note that in these two functions assume that the ordering of the first column (VLOOKUP) or row (HLOOKUP) is in alphabetical order. If that is not the case, neither of these functions will work properly. To get around this, we can setup the function to find the exact match we need by adding an additional command in the formula bar. Using our salesperson example again, let us assume that the names are in some other order than alphabetical, like descending based on last year's total sales or by birth date. We would do the following to adjust for such order:

=VLOOKUP("John",A1:M20,9,false).

The inclusion of the "false" at the end of the formula means that Excel must find the exact match you have targeted. If there is not 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 is referencing column number nine, it will still reference number 9, which would now be the next column over. 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 using the HLOOKUP function. You need to keep cognizant of that as you start to modify the data range you are targeting, and make sure that the formula is correctly providing the information you require.

In summary, VLOOKUP and HLOOKUP functions are good for spreadsheets that are likely not to shift in construction (inserting or deleting columns or rows). It is a good basic formula for less dynamic spreadsheets, and is easily implemented by newcomers to financial modeling.