HLOOKUP

Use HLOOKUP and VLOOKUP to find values in large lists. Use HLOOKUP to search in rows and VLOOKUP to search in columns.

The function searches the top row of a table or array for a value that you specify. That is, it searches the column headings. If it finds the specified value it returns a value from a specified row in the same column.

To look for an exact match for the value that you enter, specify TRUE as the value of the range_lookup parameter. To use TRUE, your data must be sorted in ascending order.

Syntax

HLOOKUP(lookup_value, table_array,row_index_number[,range_lookup=TRUE])

Lookup_value can be a reference to a cell that contains the value to look up.

Table_array is a range of cells.

The row_index_number is the number of the row from which the value is returned.

Example

This table occupies A1:E5 and shows sales of 4 products in four regions:
* Product A Product B Product C Product D
Region 1 76 67 58 57
Region 2 59 71 52 55
Region 3 75 45 69 43
Region 4 12 44 17 28

Cell Y20 contains Product B.

This function returns 71, the value in the third row of the column for Product B:

=HLOOKUP(Y20,A1:E5,3,TRUE)
Note: A dummy, numerical, value is required in the cell marked with an asterisk (*), if you specify TRUE for the range_lookup parameter.