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
| * | 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)