HLOOKUP

See also A-Z Spreadsheet Function List

Description Searches the top row of a table for a value and returns the contents of a cell in that table that corresponds to the location of the search value.

Syntax HLOOKUP(search_item, search_range, row_index)

search_item is a value, text string, or reference to a cell containing a value that is matched against data in the top row of search_range.

search_range is a reference to the range (table) to be searched. The cells in the first row of search_range can contain numbers, text, or logical values. The contents of the first row must be in ascending order (e.g., -2, -1, 0, 2...A through Z, False, True). Text searches are not case-sensitive.

row_index is the row in search_range from which the matching value is returned.

row_index can be a number from 1 to the number of rows in search_range.

If row_index is less than 1, #VALUE! is returned.

When row_index is greater than the number of rows in the table, #REF! is returned.

Remarks HLOOKUP compares the information in the top row of search_range to the supplied search_item. When a match is found, information located in the same column and supplied row (row_index) is returned.

         If search_item cannot be found in the top row of search_range, the largest value that is less than search_item is used. When search_item is less than the smallest value in the first row of the search_range, #REF! is returned.

See Also INDEX , LOOKUP , MATCH , and VLOOKUP functions

Examples In the preceding spreadsheet:

         HLOOKUP(“Northeast”, B1:E5, 3) returns 22.63

         HLOOKUP(“Pacific”, B1:E5, 7) returns #REF!