Skip to main content
Garfield Stinvil/3 min read

HLOOKUP: Mastering Horizontal Data Searches

HLOOKUP Essentials

Horizontal Cousin of VLOOKUP

Searches across the top row, returns from a row below.

Four Arguments

lookup_value, table_array, row_index, [range_lookup].

Use FALSE for Exact Match

TRUE assumes sorted data and can return unexpected matches.

Modern Alternative: XLOOKUP

Replaces both VLOOKUP and HLOOKUP — works any direction.

Master Excel at Noble Desktop

Noble Desktop's Excel Bootcamp covers formulas, pivot tables, data analysis, and VBA.

This lesson is a preview from our Excel Bootcamp Online (includes software) and Excel Expert Certification Online (includes software & exam). Enroll in a course for detailed lessons, live instructor support, and project-based training.

HLOOKUP. HLOOKUP is exactly like VLOOKUP except for two things.

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 (for VLOOKUP). Instead I'll type in HL (for HLOOKUP).

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 Paste Special and choose transpose. When I click OK, 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 the first row of the table you're selecting.