If you’ve ever worked with large datasets in Excel, you’ve probably needed to extract specific values from a table. One of the functions that can help with this is HLOOKUP. It’s not as popular as VLOOKUP, but when the data is structured horizontally, it becomes incredibly useful. In this article, I’ll break down how HLOOKUP works in Excel, step by step, and provide some practical examples along the way.
What is HLOOKUP?
HLOOKUP stands for “Horizontal Lookup.” It searches for a value in the first row of a table and returns a corresponding value from a specified row below. If your data is structured with headers across the top and records beneath, HLOOKUP is a great function to use.
The Syntax of HLOOKUP
Here’s the general structure of the HLOOKUP function:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])Let’s break it down:
- lookup_value – The value you’re searching for in the first row of the table.
- table_array – The range of cells that contains the data. The first row must contain lookup values.
- row_index_num – The row number (relative to the table_array) from which to return the value.
- range_lookup – Optional. TRUE (default) finds an approximate match, while FALSE finds an exact match.
Basic HLOOKUP Example
Let’s say we have the following dataset:
| Product | Apple | Banana | Cherry |
|---|---|---|---|
| Price | $1.00 | $0.50 | $2.00 |
| Stock | 50 | 100 | 75 |
If I want to find the price of a Banana, I can use:
=HLOOKUP("Banana", A1:D3, 2, FALSE)This searches for “Banana” in the first row and returns the value from the second row under “Banana,” which is $0.50.
Using HLOOKUP with Approximate Match
While looking up exact values is common, sometimes we work with numerical ranges. Suppose we have the following grading system:
| Score | 0 | 60 | 70 | 80 | 90 |
|---|---|---|---|---|---|
| Grade | F | D | C | B | A |
If a student scores 75, what grade do they receive? Here’s the formula:
=HLOOKUP(75, A1:F2, 2, TRUE)Since 75 is not in the first row, HLOOKUP finds the closest lower value (70) and returns “C”.
Common Errors in HLOOKUP
Despite its usefulness, HLOOKUP can sometimes cause frustration. Here are some common errors and their solutions:
- #N/A – This happens when no match is found. Ensure the lookup value exists in the first row.
- #REF! – If the row_index_num exceeds the number of rows in the table array.
- #VALUE! – This occurs when row_index_num is less than 1 or a non-numeric value.
When to Use HLOOKUP Instead of VLOOKUP?
While VLOOKUP is often the go-to function, use HLOOKUP when:
- Your data has headers in the top row instead of the first column.
- You need to retrieve data from underneath the lookup values.
- You prefer a structured horizontal format over vertical.
Final Thoughts
Now you know how HLOOKUP works in Excel! Whether you’re retrieving prices, grades, or any other structured data, this function can save you lots of time. While it’s not as commonly used as VLOOKUP, it’s still an essential tool for Excel users working with horizontally structured data.
Other interesting article:
How VLOOKUP works in Excel? Best VLOOKUP examples