HLOOKUP searches for a value in the first row of a table and returns a value from a specified row in the same column. It is the horizontal counterpart to VLOOKUP, ideal for data arranged in rows.
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])What does HLOOKUP do?
HLOOKUP stands for Horizontal Lookup. It searches for a value across the first row of a table and returns a value from a row you specify, in the same column where the match is found. Think of it as reading a table left to right: you give it a label, and it finds the matching column, then goes down to the row you asked for.
Most Excel tables are vertical, which is why VLOOKUP gets all the attention. But when your data is laid out horizontally—months across the top, product codes across the top, or any header row that runs left to right—HLOOKUP is the natural choice. It saves you from manually scanning across columns or restructuring your sheet.
Imagine you have a pricing table with product names across the first row and price tiers down the rows. A colleague asks for the wholesale price of a specific product. Instead of hunting through the sheet, you write a single HLOOKUP formula that finds the product column and returns the wholesale price from the correct row. That is exactly the kind of everyday lookup HLOOKUP handles well.
When to use HLOOKUP
- Looking up a value from a table where headers run across the first row (e.g., months, product codes, department names).
- Retrieving a specific row of data (like price, discount, or stock level) for a given column label.
- Building dynamic dashboards where the user selects a column header and the formula pulls the corresponding value.
- Comparing figures across a horizontal timeline, such as monthly sales or quarterly targets.
- Extracting a single data point from a wide reference table without rearranging it.
HLOOKUP syntax
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])| Argument | Required? | What to enter |
|---|---|---|
lookup_value | Required | The value you want to find in the first row of the table. It can be text, a number, or a cell reference. Example: "North" or A2. |
table_array | Required | The range containing the data. The first row of this range is searched for the lookup_value. Example: A1:F5. |
row_index_num | Required | The row number within table_array from which to return a value. Use 1 for the first row, 2 for the second, and so on. Example: 3 returns the value from the third row of the range. |
range_lookup | Optional | Optional. Use FALSE (or 0) for an exact match, or TRUE (or 1) for an approximate match. If omitted, Excel assumes TRUE. Example: FALSE. |
What it returns: HLOOKUP returns the value from the specified row in the column where the lookup_value is found in the first row of table_array. If no match is found and range_lookup is FALSE, it returns #N/A.
HLOOKUP examples
1 Example 1: Simple horizontal lookup for a product price
You have a small price list with product names across the first row and prices in the second row. The goal is to find the price of 'Widget'. The formula =HLOOKUP("Widget", A1:F2, 2, FALSE) searches the first row (A1:F1) for 'Widget', finds it in column C, then returns the value from row 2 of that column, which is 15.99. The FALSE ensures an exact match.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Product | Gadget | Widget | Gizmo | Doohickey | Thingamajig | |||
| 2 | Price | 12.5 | 15.99 | 9.75 | 22.3 | 7.99 | 15.99 | ||
| 3 |
2 Example 2: Lookup by month for sales figures
A sales table has months across the first row and two rows of data: sales and expenses. You want the sales figure for March. The formula =HLOOKUP("Mar", A1:F3, 2, FALSE) searches the first row for 'Mar', finds it in column D, and returns the value from row 2 (Sales) in that column, which is 1500. Using FALSE guarantees an exact match on the month label.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar | Apr | May | |||
| 2 | Sales | 1200 | 1350 | 1500 | 1600 | 1700 | 1500 | ||
| 3 | Expenses | 800 | 850 | 900 | 950 | 1000 | |||
| 4 |
3 Example 4: Approximate match for a discount tier
A discount table has order quantity thresholds across the first row and discount rates in the second row. You want the discount for an order of 120 units. The formula =HLOOKUP(120, A1:F2, 2, TRUE) uses approximate match (TRUE), which finds the largest value in the first row that is less than or equal to 120. The thresholds are 0, 50, 100, 200, 500, 1000, so 100 is the largest ≤120, and the corresponding discount is 0.05 (5%). Note that the first row must be sorted ascending for approximate match to work correctly.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Quantity | 0 | 50 | 100 | 200 | 500 | |||
| 2 | Discount | 0 | 0.02 | 0.05 | 0.1 | 0.15 | 0.05 | ||
| 3 |
4 Example 5: Realistic business use - quarterly revenue by region
A company tracks quarterly revenue for different regions. The first row contains quarter labels (Q1, Q2, Q3, Q4, Total), and each subsequent row is a region with its revenue figures. You want the Q3 revenue for the 'North' region. The formula =HLOOKUP("Q3", A1:F4, 2, FALSE) searches the first row for 'Q3', finds it in column D, and returns the value from row 2 (North) in that column, which is 15000. This is a typical business scenario where HLOOKUP pulls a specific metric from a wide summary table.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Quarter | Q1 | Q2 | Q3 | Q4 | Total | |||
| 2 | North | 12000 | 13500 | 15000 | 16000 | 56500 | 15000 | ||
| 3 | South | 11000 | 12500 | 14000 | 15500 | 53000 | |||
| 4 | East | 13000 | 14000 | 15500 | 16500 | 59000 | |||
| 5 |
Common HLOOKUP errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #N/A | The lookup_value is not found in the first row of table_array, or range_lookup is FALSE and there is no exact match. | Check for typos, extra spaces, or case differences. Ensure the lookup_value exists in the first row. Use TRIM or EXACT if needed, or switch to an approximate match if appropriate. |
| #REF! | row_index_num is greater than the number of rows in table_array, or less than 1. | Verify that row_index_num is between 1 and the total number of rows in table_array. Adjust the number accordingly. |
| #VALUE! | row_index_num is not a number (e.g., text) or range_lookup is not a valid logical value. | Ensure row_index_num is a positive integer and range_lookup is either TRUE/FALSE or 1/0. |
Tips for using HLOOKUP
- Use FALSE (or 0) for range_lookup when you need an exact match; this is the most common scenario and prevents unexpected approximate results.
- If you need to look down columns instead of across rows, use VLOOKUP or XLOOKUP.
- For approximate matches, ensure the first row of table_array is sorted in ascending order to avoid incorrect results.
- Use absolute references (e.g., $A$1:$F$4) for table_array if you plan to copy the formula to other cells.
- Consider using INDEX and MATCH for more flexibility, especially if the lookup row is not the first row of the table.
- Wrap HLOOKUP in IFERROR to display a friendly message instead of #N/A when a value is not found.
Frequently asked questions
What is the difference between HLOOKUP and VLOOKUP?
HLOOKUP searches horizontally across the first row of a table and returns a value from a specified row. VLOOKUP searches vertically down the first column and returns a value from a specified column. Use HLOOKUP when your headers run left to right; use VLOOKUP when they run top to bottom.
Can HLOOKUP return a value from a row other than the first?
Yes. The row_index_num argument lets you specify which row of the table_array to return. For example, row_index_num 3 returns the value from the third row of the range.
Why does HLOOKUP return #N/A?
#N/A means the lookup_value was not found in the first row of table_array. Check for spelling, extra spaces, or data type mismatches (e.g., text vs. number). Also ensure range_lookup is set to FALSE for an exact match.
Is HLOOKUP case-sensitive?
No, HLOOKUP is not case-sensitive. It treats 'Widget' and 'widget' as the same. If you need case-sensitive lookup, use INDEX and MATCH with EXACT.
Can I use HLOOKUP with wildcards?
Yes, when range_lookup is FALSE, you can use wildcards like ? and * in the lookup_value for text matches. For example, 'Wid*' would match 'Widget'.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. HLOOKUP reference