Lessons Lesson 37

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

By Ali, Founder, Excel Lessons

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.

Read more about this

Where this comes up again

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 the top-row value, B1:E4 the table, 2 the Occupancy row, FALSE an exact match.

F2
Row A B C D E F 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. 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 the top-row value, B1:E4 the table, 2 the Occupancy row, FALSE an exact match.

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

  2. 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. 3

    In F3, look up January's rooms sold with row 3.

    Hint. Third row this time.

  4. 4

    The month can come from a cell instead of typed text. G4 holds a month. In F4, get that month's average rate, row 4.

    Hint. G4, no quotation marks.

  5. 5

    In F5, find March's room revenue with HLOOKUP. Shape: =HLOOKUP(lookup,table,row,FALSE)*HLOOKUP(lookup,table,row,FALSE). That is rooms sold times average rate.

    Hint. Row 3 lookup times row 4 lookup.