HLOOKUP

Description

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

Parameter	Description
search_item	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	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 (for example, -2; -1; 0; 1; 2...A through Z, False, True). Text searches are not case-sensitive.
row_index	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, the error #VALUE! is returned. When row_index is greater than the number of rows in the table, the error #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, #N/A is returned.

Examples

The following examples use this worksheet.

This function returns 22.63:

HLOOKUP("Northeast", B1:E5, 3)

This function returns #REF!:

HLOOKUP("Pacific", B1:E5, 7)

Note:

If an Invalid formula syntax warning appears, check whether the comma is set as the list separator in your regional settings on the Windows Control Panel. If a different character is set as the list separator, use this character in the above examples in place of the comma. (Windows XP: Access the Windows Control Panel and select Regional and Language Options. On the Regional Options tab page, click Customize. On the Numbers tab page, check which character is the default list separator on your computer. Use this character in the above examples instead of the comma.

Also, see