HLOOKUP: lookups across rows

1 / 6

This table runs sideways: months across row 1, sales in row 3. Find the sales for each month in A6:A10.

keyboard Typing formulas is easiest on a computer with a keyboard.

months

sales

sales go here

Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.

Study notes

How HLOOKUP works

What does HLOOKUP do?

HLOOKUP is VLOOKUP turned on its side. It searches the top row of a table, then returns a value from a row further down, in the same column.
=HLOOKUP(lookup_value, table_array, row_index_num, FALSE)
What are its four parts?
- lookup_value: what you are looking for, e.g. a month.
- table_array: the whole table, starting with the row you search.
- row_index_num: which row of the table to return. The top row is 1.
- FALSE: exact match.
When do you need it?

When a table runs sideways, with headings across the top: months, years or product names in row 1, and the figures underneath. If the headings run down column A, use VLOOKUP instead.
Is there a newer way?

XLOOKUP works in both directions. Give it the row to search and the row to return:
=XLOOKUP(A6,$B$1:$M$1,$B$3:$M$3)

Know someone fighting with a spreadsheet? Send them this page.