Lookup

HLOOKUP

Definition

HLOOKUP searches the first row of a table for a key and returns a value from another row in the same column.

Syntax

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Arguments

Argument	Meaning
`lookup_value`	The key to find, such as a label typed in quotes or held in a cell.
`table_array`	The block of cells to search. The key must be in its first row.
`row_index_num`	Which row of the table to return, counting the first row as 1.
`range_lookup`	Use FALSE for an exact match. TRUE or leaving it out allows approximate matching on sorted data.

Worked example

A shop stores month names in row 2 and sales in row 3, in A2:C3.

A	B	C
Jan	Feb	Mar
11	14	9

Formula: =HLOOKUP("Feb",A2:C3,2,FALSE)

Result: 14. Feb is the second column, and row 2 of the table holds the sales value 14.

Practice

Range A2:C3 holds red, blue, and green in row 2, and 5, 8, and 3 in row 3. In D2, write an exact-match formula that returns the value for blue.

In D2, write an exact-match HLOOKUP that returns the value for blue from A2:C3.
D2 should show the value for blue.

Step 1 of 2

Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.

Loading the sheet…

Related functions

VLOOKUP does the same job when the keys sit in a column. INDEX can return a cell once you know its position.

Common mistake

Counting row_index_num from the sheet row: the first row of table_array is 1, so HLOOKUP on A2:C3 with 3 looks for a third table row that is not there and returns #REF!.