Let’s take a look. Here’s the VLOOKUP commission example we’ve looked at previously. Let me quickly run through the example again to recap. To look up the correct commission value in this table with VLOOKUP, we give VLOOKUP the value to look up 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 that 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 in this case. 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 using the transpose feature. I just copy the table, bring up the Paste Special dialog, and select “Transpose.” Now we have a horizontal table that can build our HLOOKUP formula. 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). Next I need to provide a row index. In our table, the commission rates are in the 2nd row, so I need to use the number 2. Finally, I need to make sure HLOOKUP is using approximate match. When I press Enter and copy the formulas down, the HLOOKUP function gives us the same commission rates we calculated using VLOOKUP. Just like VLOOKUP, HLOOKUP is fully dynamic and returns the current information in the lookup table.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
