Exceljet

Quick, clean, and to the point

How to use HLOOKUP

In this video, we'll look at how to use HLOOKUP. HLOOKUP works just like VLOOKUP, but instead of getting the value of an item from a certain column in a table, HLOOKUP gets a value from a certain row in a table.

Let's take a look.

Here's an the VLOOKUP commission example we've looked at previously. Let me quickly run through that example again to recap.

To lookup the correct commission value in this table with VLOOKUP, we give VLOOKUP the value to lookup and the table array to use. In this case, I won't use a named range, but I will convert the table to an absolute reference so I can copy the formula down.

Next, we give VLOOKUP the column number from which to get the commission rate, in this case 2. Finally, we need to specify an exact or approximate match. We definitely want an approximate match.

Now when I copy this down, we get the correct commission rates for each salesperson in the list.

Now let's perform exactly the same lookup using HLOOKUP. To do this, I need to first transpose the table from a vertical layout to a horizontal layout. This is easy to do with Paste Special transpose feature.

I just copy the table, bring up Paste Special, and select Transpose.

Now we have a horizontal table and can build the HLOOKUP formulas.

Again, HLOOKUP has identical functionality to VLOOKUP.

I need to supply the value to look up and the table array (which I'll again make an absolute reference). Now I need to provide a row index. In our table, the commission rates are in the 2nd row, so I need to use 2. Finally, I need to make sure HLOOKUP is using approximate match.

When I press enter and copy the formulas down, HLOOKUP gives us the same commission rates we got using VLOOKUP.

Just like VLOOKUP, HLOOKUP is fully dynamic and will return the current information in the lookup table.

Course 
Core Formula
Author 
Dave Bruns