HLOOKUP

About this lesson

HLOOKUP searches the first row of a table for a value. Then it returns a value from the same column, in a row you choose.

By the end you can

look along a row with HLOOKUP the way VLOOKUP looks down a column.
recognise a sideways table and choose the right lookup.

Practises HLOOKUP VLOOKUP XLOOKUP INDEX

The idea

HLOOKUP is VLOOKUP turned sideways, argument for argument. It exists because some tables really are laid out across the page: months, quarters, sizes, years. That is also why it is rare. Most data has one record per row, and when a table grows, it grows downwards.

If you want HLOOKUP on a table that could have been the other way round, the table is the problem, not the lookup. XLOOKUP removes the choice. It takes a lookup range and a return range. They can run either way.

The mistake to watch for

Counting the row number from the sheet instead of from the table. The third argument counts rows inside the range you gave it, heading row included. So a table that starts on row 1 and one that starts on row 3 need different numbers for the same data. Count from the top of the range.

Where this comes up again

XLOOKUP: Looking Left, Not Found and Next Larger Advanced Lookups
Two-Way Lookup: INDEX with Two MATCHes Advanced Lookups
Choosing a Lookup: Putting It Together Advanced Lookups
Approximate Match: Bands and Brackets Advanced Lookups

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

HLOOKUP is VLOOKUP turned sideways. It searches the first row of a table and returns a row below it.

In `F2`, type `=HLOOKUP("Mar",B1:E4,2,FALSE)`.

"Mar" is found along the top row. B1:E4 is the table. 2 is its second row (Occupancy). FALSE means exact match.

Row	A	B	C	D	E	G
1	Month	Jan	Feb	Mar	Apr
2	Occupancy %	62	58	71	84
3	Rooms sold	558	522	639	756
4	Avg rate	89.5	91	96.25	104	Feb
5
6
7
8

Every step

1

HLOOKUP is VLOOKUP turned sideways. It searches the first row of a table and returns a row below it. In F2, type =HLOOKUP("Mar",B1:E4,2,FALSE). "Mar" is found along the top row. B1:E4 is the table. 2 is its second row (Occupancy). FALSE means exact match.

Hint. "Mar", B1:E4, 2, FALSE.
2

Read =HLOOKUP("Apr",B1:E4,3,FALSE) and say what it returns. Rooms sold is the third row of the table.

Hint. April's column, third row.
3

In F3, bring back the rooms sold in January, typing the month into the formula.

Hint. Third row this time.
4

The month can come from a cell instead of being typed. That is the version to build. G4 holds a month. In F4, get that month's average rate, the fourth row.

Hint. G4, no quotation marks.
5

In F5, write a formula with HLOOKUP that works out March's room revenue. That is the rooms sold that month times that month's average rate. Two lookups in one formula.

Hint. Row 3 lookup times row 4 lookup.