Excel HLOOKUP Function and Formula with Example

How to use the HLOOKUP Function and Formula in Excel

In this tutorial, you'll learn how to use the HLOOKUP function and formula in Excel

HLOOKUP() is a function to search for a value in the top row of a table and then retrieve associated data from the same column by a row number.

HLOOKUP Syntax

The syntax of the HLOOKUP() function is as follows:

=VLOOKUP(lookup_value, table_array, row_index_number, [range_lookup])

Return Value

The matched value from a table.

Arguments

  • lookup_value: The value to look up, located in the first row of the table
  • table_array: The table in which to look up data
  • row_index_numberThe row number from which to retrieve data
  • range_lookupOptionalTRUE or FALSE. FALSE for an exact match, and TRUE for an approximate match.

Excel HLOOKUP Examples

Demo data:

Table 1:

 ABCDE
1Item CodeItem NameQtyUnit Cost ($)Unit Price ($)
2Item111ASUS Desktop PC 11110300330
3Item112ASUS Desktop PC 11220150750
4Item113ASUS Desktop PC 11325210234
5Item114ASUS Desktop PC 1142010001100
6Item115ASUS Desktop PC 1153010001110
7Item116ASUS Desktop PC 11648250280
8Item117ASUS Desktop PC 11740270300

Table 2:

ABCD
1Item CodeItem111Item112Item113
2Item NameASUS Desktop PC 111ASUS Desktop PC 112ASUS Desktop PC 113
3Qty102025
4Unit Cost ($)300150210
5Unit Price ($)330750234

Difference between Table 1 and Table 2

In this example, you'll learn the difference between Table 1 and Table 2 when you use the HLOOKUP function.

The following example searches for Item Code located at the intersection of row number 5 and column A in Table 1:

=HLOOKUP("Item Code", A1:E8, 5, FALSE)

The above formula returns Item114.

However, HLOOKUP can't retrieve the other associated data of Item114, such as Item NameQtyUnit Cost, and Unit Price

To retrieve the associated data, you need to use the VLOOKUP function.

The following example searches for Item Code Item111 in the range B1: H5 in Table 2 and returns Item Name ASUS Desktop PC 111:

=HLOOKUP("Item111" ,B1:H5, 2, FALSE)

Similarly, you can get the Unit Price by changing row_index_number to 5.

HLOOKUP() can return the associated data of a lookup value in Table 2

Searching for a value in an array

You can use the HLOOKUP function to search for a value in an array, as the following example shows:

=HLOOKUP("B", {"A","B","C","D";10,20,30,40;230,240,250,260}, 2, TRUE)

There are three rows and four columns in the array specified above.

HLOOKUP() searches for letter B, located in the first row and second column.

The above formula returns 20.

Partial Matching (*)

HLOOKUP allows wildcard characters (*) and (?) in a lookup value.

You can perform a partial matching using HLOOKUP, as the following example shows:

=HLOOKUP("Item*", A1:E8, 8, FALSE)

HLOOKUP() searches for Item Code that begins with Item and ends with any sequence of characters. 

The above formula returns Item117.

Note that the wildcard character (*) matches any sequence of characters.

Another example of Partial Matching

Here is another example of a partial matching:

=HLOOKUP("??em*???e", A1:E8, 8, FALSE)

Here, we've used both (?) and (*) in the lookup value.

The above example returns Item117.

Note that the wildcard character (?) matches any single character.

In this tutorial, you've learned how to use the Excel HLOOKUP function and formula

HLOOKUP() searches for a value in the top row of a table or array and retrieve the associated data from the same column by a row number.


See also:
Excel ROUNDUP Function with Examples
Excel MIN Function with Examples
Excel COUNTA Function with Examples
Excel COUNTBLANK Function with Examples
Excel SUMIFS Function with Examples

Leave a Comment