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_number: The row number from which to retrieve data
- range_lookup: Optional. TRUE or FALSE. FALSE for an exact match, and TRUE for an approximate match.
Excel HLOOKUP Examples
Demo data:
Table 1:
| A | B | C | D | E | |
| 1 | Item Code | Item Name | Qty | Unit Cost ($) | Unit Price ($) |
| 2 | Item111 | ASUS Desktop PC 111 | 10 | 300 | 330 |
| 3 | Item112 | ASUS Desktop PC 112 | 20 | 150 | 750 |
| 4 | Item113 | ASUS Desktop PC 113 | 25 | 210 | 234 |
| 5 | Item114 | ASUS Desktop PC 114 | 20 | 1000 | 1100 |
| 6 | Item115 | ASUS Desktop PC 115 | 30 | 1000 | 1110 |
| 7 | Item116 | ASUS Desktop PC 116 | 48 | 250 | 280 |
| 8 | Item117 | ASUS Desktop PC 117 | 40 | 270 | 300 |
Table 2:
| A | B | C | D | |
| 1 | Item Code | Item111 | Item112 | Item113 |
| 2 | Item Name | ASUS Desktop PC 111 | ASUS Desktop PC 112 | ASUS Desktop PC 113 |
| 3 | Qty | 10 | 20 | 25 |
| 4 | Unit Cost ($) | 300 | 150 | 210 |
| 5 | Unit Price ($) | 330 | 750 | 234 |
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 Name, Qty, Unit 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.