Master HLOOKUP, Excel's powerful horizontal lookup function, to efficiently search values across rows. Learn how it differs from VLOOKUP and when to apply each.
Key Insights
- Understand that HLOOKUP mirrors VLOOKUP but searches horizontally, replacing the "column index number" with a "row index number" to locate the desired lookup value.
- Implement HLOOKUP by selecting your lookup value and defining your table array similar to VLOOKUP, but count rows down instead of columns across to locate your data (e.g., counting three rows down to find the level for ID K77).
- Apply the Excel "Transpose" function to switch table orientation, enabling the use of VLOOKUP instead of HLOOKUP when data headers and IDs are positioned incorrectly.
Note: These materials offer prospective students a preview of how our classes are structured. Students enrolled in this course will receive access to the full set of materials, including video lectures, project-based assignments, and instructor feedback.
HLOOKUP. HLOOKUP is exactly like VLOOKUP except for two things. One letter and one word.
The one letter that's different is the letter H and the one word that is different is row. In HLOOKUP, column index number gets replaced with row index number. That's because you're going to find the lookup value horizontally in a row.
So let's take a look at how this would work with this table. Now I'm going to click into the cell that's to the right of K77 and resist the urge to type VL. Instead I'll type in HL.
Then I can press TAB. The lookup value is going to be selected exactly the same way we would select the lookup value in a VLOOKUP. That's the cell to the left.
I'll enter a comma. The table array is going to be selected exactly the same way the table array would be selected when you're using a VLOOKUP. Okay, so now this is where it's different.
Instead of counting the column index number, we're going to count the row index number for the value level. That value is going to be found in the first column. So one, two, three.
I needed to count three rows down to find level. So the answer to that is three. I'll enter a comma just like VLOOKUP.
We'll choose false for an exact match. I'll press ENTER and I'm able to find the level for ID K77. And if I take a look at the answer, I'll see that is indeed the level for K77.
So this is a situation where someone was crazy enough to put all the ID numbers where the headers usually go and put the headers where the ID numbers usually go. In fact, I could use VLOOKUP instead, but I would have to do something first to this table to be able to use VLOOKUP. I usually ask the class, does anyone know what that is? And 100% of the time people always say transpose.
So I'll press control C. I'll go to another part of the sheet, bring up a special and choose transpose. When I click okay, this table should look very familiar to you. If you remember our VLOOKUP exercise, because this is the same exact table.
All we did was change the orientation in this exercise so we could use HLOOKUP instead. So in a nutshell, that is an HLOOKUP. You're going to look for the row index value inside of the first column of the table you're selecting.