HLOOKUP

Using the Excel HLOOKUP Function

Summary

The HLOOKUP function is one of Excel's lookup and reference functions and is similar to the VLOOKUP function. HLOOKUP is used to find a value in the top row of a table and return a value in the same column from a row specified by the user. This function is used when you have comparison values located in a row across the top of a table of data. The "H" in HLOOKUP stands for horizontal.

If your comparison values are located in a column that is left of the data you want to find, then use the VLOOKUP function.


Syntax

=HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])

Syntax Breakdown

Lookup Value
Required. The value you are searching for in the first row of a table. This argument can be a value, a reference, or a text string.

Table Array
Required. A table containing the data that is looked up. Use a reference to a range or range name for this argument. The values in the first row can be text, numbers, or logical values.

Text values are not case sensitive. Uppercase and lowercase text are equivalent.

The values in the first row of table_array must be sorted in ascending order (i.e. -1, 0, 1 and A-Z) if the range_lookup argument is TRUE. If your data is not properly sorted the function may not return the correct value. If the range_lookup argument is FALSE, the data does not need to be sorted.

Row Index Num
Required. The number of the row in table_array from which the matching value will be found. Setting row_index_num to 1 will return the first row value in table_array, a row_index_num of 2 returns the second row value in table_array, and so on.

If the row_index_num is less than 1, the function returns the #VALUE! error. If the value for row_index_num is greater than the number of rows in the table, then the #REF! error is returned.

Range Lookup
Optional. A logical value indicating if you want an exact or approximate match.

Enter TRUE, or leave the argument blank, if you would like an approximate match. This is the default setting. The function will match the next largest value in the table that is less than lookup_value. Even if range_lookup is set to TRUE, the function will return an exact match if one exists.

Enter FALSE if you want an exact match. If you are using FALSE, the values in the first row of the table do not need to be sorted in any specific order. If you are searching for an exact match and one isn't found, the function returns the #N/A error.


Usage Notes

The HLOOKUP function looks up and retrieves data from a specific row in a table. The function supports wildcard characters.

The "H" in HLOOKUP stands for horizontal. This function is used when your data is organized into horizontal rows. If your data is organized vertically, use the VLOOKUP function.

HLOOKUP Matching Modes
HLOOKUP can be set to search for an exact or approximate match. This is controlled by the range_lookup argument, whose default setting is TRUE. Setting the argument to FALSE will force an exact match, whereas TRUE is for approximate matching.

If you have a unique ID as a lookup_value, using FALSE for an exact match will be the best course of action:

If an exact match isn't necessary, or may not be possible, use TRUE for an approximation. In this scenario HLOOKUP will return the best match for a given value, but it doesn't have to be exact. If you supply a value that is larger than the values in table_array, the function will drop back to the next largest value.

It is necessary to sort your data in ascending order by lookup_value when using an approximate match or the function may return an incorrect value.

Using Wildcards
The HLOOKUP function supports the use of wildcard characters - question mark (?) and asterisk (*) - in the lookup_value argument. To match any single character use the question mark and to match any sequence of characters use the asterisk.

To find an actual question mark or asterisk, enter the tilde (~) in front of the character.

Excel HLOOKUP with wildcards