Excel Tip of the Week: VLOOKUP & HLOOKUP

Today’s topics are the two functions, VLOOKUP and HLOOKUP.

This post is a bit long, but TOTALLY WORTH IT. These functions are highly useful and widely used. I’ve even been asked in interviews specifically about my ability to use VLOOKUP and HLOOKUP. Also, toward the bottom of this post is the first ever OwenBloggers EXCEL CHALLANGE!

VLOOKUP and HLOOKUP are very similar, but VLOOKUP is a much more popular function.  This is because data is typically organized vertically in columns instead of horizontally in rows. Thus, I’ll first describe and walkthrough the VLOOKUP example. I’ll then touch on HLOOKUP, but it will essentially be the same as VLOOKUP with the word “column” replaced with the word “row.”

VLOOKUP works by searching for a value in the first column of a table array and returning a value in the same row from another column (which you specify) from the same table array. I’m not even sure that last sentence was English, but perhaps a metaphor will help: Pretend VLOOKUP is a really well trained pet that will go fetch a specific cell for you. A cell you tell it to fetch.

Good Boy!

 Before we jump into an example, here are the inputs:

Lookup value: This is the value VLOOKUP is looking for in the first column of your data.
Table array: This is the array of data the function will be looking in.
Col_index_num: This input tells VLOOKUP the number of columns to move to the right to arrive at your desired output.
[range lookup]: Notice the brackets which indicates this input is optional. This is a one or zero option. I always put a zero here. There are some advanced reasons why you might use one, but it is beyond the scope of this post.

Let’s look at an example. The set-up is that you have downloaded some information from your company’s accounting system that shows the sales for the month. You would like to see which salesman is responsible for each sale and also calculate the margin for each sale. The accounting system only gives you the item number and the sales price. You would like to put the salesman responsible for each sale into your table as well as the cost of each item. Using VLOOKUP makes this a very simple task:

1. Download the example spreadsheet here
2. Move your cursor into cell D6
3. Begin typing your formula by entering =VLOOKUP
4. Insert the proper inputs. Your formula should look like this: =VLOOKUP(C6,$I$6:$K$8,2,0) (Don’t forget the absolute cell references! They are super important!)
5. Drag or fill down the formula to complete the column.
6. Place your cursor in Cell E6
7. Begin typing your formula by entering =VLOOKUP
8. Insert the proper inputs. Your formula should look like this: =VLOOKUP(C6,$I$6:$K$8,3,0)
9. Drag or fill down the formula to complete the column.

Notice the only difference in the two formulas is the “Col_index_num.” When I wanted VLOOKUP to “fetch” the salesman I used the number 2 and when I wanted the cost of the item I used the number 3. This is because the salesmen were in the second column of the table and the costs were in the third column. I highly encourage you to study the inputs a couple times to make sure you understand the logic behind the inputs.

FIRST EVER EXCEL CHALLENGE!!!!! There is a way to fill in both columns with a single formula. If you can figure it out, put your formula in the comments along with your favorite candy bar. First person to get it right will receive their favorite candy bar courtesy of OwenBloggers.

Moving on to HLOOKUP, here are the inputs:

As you can see, they are the exact same inputs as VLOOKUP except instead of “col_index_num” it has “row_index_number”

Here are the formulas to plug into the example spreadsheet if you wanted to use HLOOKUP: =HLOOKUP(C6,$J$11:$L$13,2,0) would go in cell D6 and be copied to the rest of the column and  =HLOOKUP(C6,$J$11:$L$13,3,0) would go in cell E6 and be copied to the rest of the column. Notice how the formula is referencing the horizontally arranged data this time. Everything else is pretty much the same.

Practice makes perfect with these functions, so take the time to go through the examples and then start looking for ways to use these functions in your own spreadsheets.