Overview of VLOOKUP & HLOOKUP

Many of us are familiar with the VLOOKUP function. The VLOOKUP and HLOOKUP formula in Excel allow you look up a value in one column or row and return a corresponding value from a different column or row. VLOOKUP is a vertical lookup and HLOOKUP is a horizontal lookup. We are going to focus on the HLOOKUP function

The syntax for HLOOKUP

HLOOKUP (value that is being looked for, lookup table name or range, number of the rows in the table containing the relevant data you wish to return, true or false)

Setting up the data

Let’s set up some data to work from.

Leave Cell A1 Blank

In Cells B1 to M1, enter the months Jan to Dec, one month in each cell

In Cells A2 to A5, enter the names Mary, Sally, Amber, Emily

Highlight cells B2:M5 and enter the formula =RANDBETWEEN(500,1000)

Press Ctrl and Enter to populate all the cells

Efficiency tricks when entering formula

Highlighting all the cells and entering the formula to one cells and pressing Ctrl and Enter to populate all the cells is a time saving or efficiency trick that is over looked by many(as we did in step 4 about).   Getting use to entering formula with this and other efficiency tricks will save you a heap of time in the long run.

RANDBETWEEN

RANDBETWEEN looks for a bottom and top value. If you cannot guess what it does, let me tell you.   It will pull random numbers between the bottom and top values. It is an excellent function in MS Excel to give you some sample data.

Now we have a set of data that looks Figure 1: However you will have different values in the cells as you have used the RANDBETWEEN function

Figure 1

 

HLOOKUP

We are going to now use the HLOOKUP function to return the value of Ambers Sales for May.

 

Let’s go back to the syntax: HLOOKUP (value that is being looked for, lookup table name or range, number of the rows in the table containing the relevant data you wish to return, true or false)

First we want the value that is being looked for, going horizontal across the columns. May is text so is placed between “”. The formula now looks like =HLOOKUP(“May”,

 

Second we want the lookup table name or range. For this I want you to highlight columns A to M.   By highlighting the entire columns insures that if you add rows to the data they are included in the formula. The formula now looks like =HLOOKUP(“May”,A:M,

 

Now we need to state the number of rows down we want to pull the data from. Amber is the 4^th row down. And finally the formula requires you to define if you want an exact match or an approx. match. Select true for an exact match. The formula now looks like =HLOOKUP(“May”,A:M,4,FALSE)

 

So what will happen is Excel will start in cell A1, as this is the beginning of the table selected and will move across the columns horizontally until it finds May. It will then count down 4 rows and return the value.

 

But let’s throw in a curve ball. Let’s say you get a new employee and they have been inserted to the list. We will name this employee Linda. Linda has been placed in the list above Mary, which means that Mary is no longer in cell A2, but A3 and Amber is now in cell A5 instead of A4. The HLOOKUP formula now needs to be changed because it is not returning Ambers Sales but Sally’s. This is because Sally has now moved down to row 4.

 

We will now update the Data Table. Select row 2, right click and select Insert. This will now insert a row by moving all other rows down one. In cell A2 enter Linda. As with entering the data above, in cells A3:M3 use the RANDBETWEEN function and efficient formula entering to populate the cells. Your data should look something like figure 2.

 

Figure 2

Now go to your HLOOKUP formula and activate the cell by pressing F2. This brings the cell into edit more. You can now change the 4 to 5 and leave the reminder of the formula the same. The formula should now look like =HLOOKUP(“May”,A:M,5,FALSE) Figure 3

Figure 3

 

 

But this is not dynamic, I hear you say! Well you are correct. This is a manual way of changing the formula. To make the HLOOKUP or even the VLOOKUP dynamic we will need to combine it with another function. Welcome to the MATCH Function.

MATCH

The Match function returns a relative position in a list.   From the data we set up, if we were to highlight cells A1 to M1 we can see that cell A1 is blank, but this is position 1, cell B2 is position 2, and it is relative to the selected cells.

MATCH(Lookup Value, Lookup array, match type)

The lookup value is the value that you wish to look up, the lookup array is the cells which you want to find the lookup value and the match type is either an exact match or an approximate match.

Looking to our data set, we want to find the sales for Amber in May by using the HLOOKUP function, however we need it dynamic so we can enter more employees to the data set and will still return the correct value.   We previously had =HLOOKUP(“May”,A:M,5,FALSE).   Our row index number in this formula is 5, however this is the value that we want to change if we add more employees.   So we can use the match function.   Our lookup value will be “Amber” and our Lookup array will be cells A:A(this will take the entire column. We need to select the entire column as if we add more employees the range will expand and this covers the entire range). Finally our match type will be Exact.

Our Match formula will now look like this =MATCH(“Amber”,A:A,0) see figure 4 and this will return a value of 5.   We can now use this in or nest this in our HLOOKUP.

Figure 4

Our new dynamic HLOOKUP formula now looks like this

=HLOOKUP(“May”,A:M, MATCH(“Amber”,A:A,0),FALSE)

 

Now if we insert more rows of employees to the data set, and the position of Amber moves in the list, we do not have to adjust our formula because we have built a dynamic HLOOKUP.