Searching Across Rows With HLOOKUP

HLOOKUP is the horizontal twin of VLOOKUP. The H stands for Horizontal: instead of searching down a column, it searches across the top row of a table, then returns a value from further down the same column.

Use HLOOKUP when your data is laid out sideways, with labels along a row instead of down a column.

HLOOKUP mirrors VLOOKUP, with one key change. Its arguments are:

• lookup_value - what you search for in the top row
• table_array - the range of cells
• row_index_num - which row to return from (counted from the top)
• [range_lookup] - TRUE for approximate, FALSE for exact

Notice the third argument is now a row index, not a column index, because the data runs down.

Picture a quarterly table where months run across row 1 in cells A1:D3:

• Row 1: Jan, Feb, Mar, Apr
• Row 2 (Sales): 100, 150, 120, 200
• Row 3 (Costs): 60, 70, 65, 90

The top row (months) is what HLOOKUP searches. The rows below hold the data we want to return.

To find the Sales figure for Mar, HLOOKUP searches the top row for "Mar", then returns the value from row 2 (Sales):

Reading it aloud: look up "Mar" across the top row of A1:D3, and return the value from the 2nd row, using an exact match. The result is 120.

The row_index_num is counted from the top of your table_array, not from row 1 of the sheet.

In our range A1:D3, the rows are numbered:

• Row 1 = months (the search row)
• Row 2 = Sales
• Row 3 = Costs

So to return Costs for a month, use row index 3. Index 1 simply returns the label you searched for.

As with VLOOKUP, you usually point at a cell rather than typing the label. If a month is chosen in F1, reference it directly.

Now changing F1 to a different month instantly updates the result. This makes HLOOKUP great for a small selector that reads a sideways summary table.

The two functions are the same idea, rotated 90 degrees:

• VLOOKUP: searches down the first column, third argument is a column index
• HLOOKUP: searches across the top row, third argument is a row index

Choose based on your layout. If your headers run down the side, use VLOOKUP. If they run across the top, use HLOOKUP.

The range_lookup argument behaves identically to VLOOKUP:

• FALSE = exact match; the top row need not be sorted; missing values give #N/A
• TRUE = approximate match; the top row must be sorted left to right ascending

For label lookups like month names, use FALSE. For a banded row of thresholds, TRUE works, but it is rare to lay tiers out horizontally.

If you copy an HLOOKUP across several cells, lock the table_array with dollar signs so it does not drift. Leave the lookup_value relative if it should shift with each column.

Here, copying right keeps the table fixed while the lookup label moves to the next cell. Same discipline as VLOOKUP, just in the horizontal direction.

Sideways tables show up more than you might expect:

• Financial models with years or months as columns
• Score sheets with rounds across the top
• Imported reports that put periods in a header row

Rather than transposing the whole table, HLOOKUP lets you query it as-is. That said, many people prefer to keep data vertical, since most functions favor that shape, then VLOOKUP or XLOOKUP fits naturally.

Just like VLOOKUP, hard-coding the row index can be fragile. If you insert a row, the index breaks. You can make it dynamic by finding the row position with MATCH.

Here MATCH locates which row the label "Costs" sits in, down the labels in column A, and feeds that number to HLOOKUP. Now the formula keeps working even if rows move around. This is a first taste of combining lookups, which you will explore more later.

Test your grasp of horizontal lookups.

You can now query sideways tables:

• HLOOKUP searches across the top row and returns from a row below
• The third argument is a row_index_num, counted from the top of the range
• FALSE and TRUE work just like VLOOKUP for exact vs approximate
• Lock the table with $ when copying across
• Use it when your headers run horizontally instead of vertically

Next you will learn why VLOOKUP sometimes fails and how to diagnose it.