# 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:

Table 2:

#### 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.