Excel HLOOKUP Function
last modified April 4, 2025
The HLOOKUP function is a powerful lookup tool in Excel that searches
for a value in the top row of a table and returns a value from a specified row.
This tutorial provides a comprehensive guide to using the HLOOKUP
function with detailed examples. You'll learn basic syntax, practical
applications, and advanced techniques to master this essential Excel function.
HLOOKUP Function Basics
The HLOOKUP function performs a horizontal lookup by searching for
a value in the top row of a table and returning a value from a specified row.
It's ideal for data arranged horizontally.
| Component | Description |
|---|---|
| Function Name | HLOOKUP |
| Syntax | =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) |
| Arguments | 4 parameters (last optional) |
| Return Value | Matched value from specified row |
This table breaks down the essential components of the HLOOKUP
function. It shows the function name, syntax format, argument details, and
return value characteristics.
Basic HLOOKUP Example
This example demonstrates the simplest use of HLOOKUP to find a product price in a horizontal table.
| A | B | C | D |
|---|---|---|---|
| Product | Apple | Banana | Orange |
| Price | 1.25 | 0.75 | 1.10 |
| =HLOOKUP("Banana",A1:D2,2,FALSE) |
The table shows product names in the first row and prices in the second row. The HLOOKUP formula searches for "Banana" and returns its price from row 2.
=HLOOKUP("Banana",A1:D2,2,FALSE)
This formula searches for "Banana" in the top row of A1:D2 and returns the value from row 2 in the same column. The FALSE parameter ensures exact match. The result will be 0.75.
HLOOKUP with Cell Reference
A more practical use of HLOOKUP involves using cell references for the lookup value. This makes the formula dynamic and reusable.
| A | B | C | D |
|---|---|---|---|
| Month | Jan | Feb | Mar |
| Sales | 1200 | 1500 | 1800 |
| =HLOOKUP("Feb",A1:D2,2,FALSE) |
This table shows monthly sales data arranged horizontally. The HLOOKUP formula uses a cell reference to find sales for a specific month.
=HLOOKUP("Feb",A1:D2,2,FALSE)
The formula searches for "Feb" in the top row and returns the sales figure from row 2. The result is 1500. Using cell references makes the formula adaptable.
HLOOKUP with Approximate Match
HLOOKUP can perform approximate matches when the last argument is TRUE or omitted. This is useful for finding ranges or graded data.
| A | B | C | D |
|---|---|---|---|
| Score | 60 | 70 | 80 |
| Grade | D | C | B |
| =HLOOKUP(65,A1:D2,2,TRUE) |
This table shows score ranges and corresponding grades. The HLOOKUP formula finds the appropriate grade for a score of 65 using approximate matching.
=HLOOKUP(65,A1:D2,2,TRUE)
The formula searches for 65 in the top row and returns the grade from row 2. Since 65 falls between 60-70, it returns "D". The table must be sorted in ascending order for approximate matches to work correctly.
HLOOKUP with Wildcards
HLOOKUP supports wildcards (* and ?) for partial matches when using exact match mode (FALSE). This example demonstrates this feature.
| A | B | C | D |
|---|---|---|---|
| Code | APP123 | BAN456 | ORA789 |
| Category | Fruit | Fruit | Fruit |
| =HLOOKUP("BAN*",A1:D2,2,FALSE) |
This table shows product codes and categories. The HLOOKUP formula uses a wildcard to find a partial match in the code.
=HLOOKUP("BAN*",A1:D2,2,FALSE)
The formula searches for any code starting with "BAN" and returns the category from row 2. The asterisk (*) represents any sequence of characters. The result is "Fruit".
HLOOKUP with Multiple Rows
HLOOKUP can retrieve data from any row in the table array, not just the second row. This example shows how to access data from the third row.
| A | B | C | D |
|---|---|---|---|
| Employee | John | Sarah | Mike |
| Department | Sales | HR | IT |
| Salary | 5000 | 4500 | 5200 |
| =HLOOKUP("Sarah",A1:D3,3,FALSE) |
This table contains employee data with multiple rows of information. The HLOOKUP formula retrieves salary information from the third row.
=HLOOKUP("Sarah",A1:D3,3,FALSE)
The formula searches for "Sarah" in the top row and returns the salary from row 3. The result is 4500. The row_index_num parameter determines which row's data to return.
HLOOKUP Error Handling
HLOOKUP returns #N/A if the lookup value isn't found. This example shows how to handle such errors gracefully using IFERROR.
| A | B | C | D |
|---|---|---|---|
| Product | Widget | Gadget | Thingy |
| Price | 10.99 | 15.50 | 8.75 |
| =IFERROR(HLOOKUP("Doodad",A1:D2,2,FALSE),"Not Found") |
This table demonstrates error handling when a lookup value doesn't exist in the table. The IFERROR function provides a user-friendly message instead of #N/A.
=IFERROR(HLOOKUP("Doodad",A1:D2,2,FALSE),"Not Found")
The formula attempts to find "Doodad" in the table. When not found, instead of #N/A, it displays "Not Found". This makes spreadsheets more user-friendly when dealing with potential missing data.
The HLOOKUP function is essential for working with horizontally
structured data in Excel. From basic lookups to advanced techniques with
wildcards and error handling, HLOOKUP offers powerful data retrieval
capabilities. Mastering its various applications will significantly improve
your ability to work with horizontal data arrangements in spreadsheets.
Author
List all Excel Formulas.