Horizontal lookup. Searches the first row of a range for a key and returns the value of a given cell in the column found.
HLOOKUP(value, range, index, [sorted])
- value - The value you want to look up.
- range - The range where the lookup value is located.
- index - The row number in the range that contains the return value.
- sorted - [OPTIONAL] A logical value that indicates if the row to be looked up is sorted.
HLOOKUP(500,A1:Z10,2,FALSE) looks for an exact match of the number 500 in the range A1:Z10 . If an exact match is found, the function returns the value corresponding to row 2 of the matched column.
- If index is not between 1 and the number of rows in range , #VALUE! is returned.
- It is recommended to set sorted to FALSE. If set to FALSE, an exact match is returned. If there are multiple matching values, the content of the cell corresponding to the first value found is returned, and #N/A is returned if no such value is found.
- If sorted is TRUE or omitted, the nearest match (less than or equal to the search key) is returned. If all values in the search column are greater than the search key, #N/A is returned.
- If sorted is set to TRUE or omitted, and the first row of the range is not in sorted order, an incorrect value might be returned.
- When searching for numeric or date values, make sure that the first row in the range is not sorted by text values. For example, correctly sorted numbers should appear as (1, 2, 10, 100) rather than (1, 10, 100, 2) as they would be if they were sorted as strings. Using an incorrect sort type may cause incorrect values to be returned.
- HLOOKUP has much better performance with sorted ranges and sorted set to TRUE. Consider sorting the row being searched.
- You can also find matches using pattern strings that include wildcards. Currently, the use of asterisk (*) is supported.
- An asterisk matches any sequence of characters.
- To match an actual asterisk, type a tilde (~) before the character. For example: (~*).