HLOOKUP function
HLOOKUP searches for a value and reference to the cells under the selected area. This function verifies if the first row of an array contains a certain value. The function returns the value in a row of the array, which is named in the index, in the same column.
Syntax
HLOOKUP(search_criteria, array, index, sorted)
- search_criteria is the value searched for in the first row of the array. The search criteria can also include wildcard characters. To represent any single character, use ? (question mark) in the text that specifies the condition. To represent any number of consecutive characters, use * (asterisk). For example, use B* to match all entries that begin with B, and B??? to match all entries that begin with B and contain three other characters. To represent an actual question mark or asterisk, use ~ (tilde) to precede the ? or *.
- array is the reference.
- index is the number of the row in the array that contains the value to be returned. The first row has the number 1.
- sorted is an optional argument that indicates whether the first column in the array is sorted in ascending order. Enter the Boolean value FALSE if the first column is not sorted in ascending order. Sorted columns can be searched much faster and the function always returns a value, which can be between the lowest and highest value of the sorted list. In unsorted lists, the search value must be matched exactly. Otherwise, the function returns #N/A.
Example
A4 contains 52, A5 contains the name "Vegetable Soup," and so on, for 52 menu items.
Enter the following formula in B1:
=HLOOKUP(A1, A4:AZ5, 2)
After you enter a number in A1, B1 shows the corresponding text contained in the second row of reference A4:AZ5. Entering a nonexistent number causes the text to be displayed with the next number down. If you want a nonexistent number to generate an error message instead, enter FALSE as the last parameter in the formula.