Function guides ยท Lookup
HLOOKUP function
Finds a value in the top row of a table and returns a value from a row below it.
Syntax
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
| Argument | What it is |
|---|---|
lookup_value | The value to find in the first row of the table. |
table_array | The table. The lookup values must be in its top row. |
row_index_num | Which row of the table to return, counting from 1. |
range_lookup | FALSE for an exact match. TRUE or omitted for an approximate match on sorted data. |
What HLOOKUP does
HLOOKUP searches across the top row of a table for a value and returns an item from a row below it. The H stands for horizontal.
It is VLOOKUP turned on its side. Where VLOOKUP expects keys running down the left edge and answers in columns to the right, HLOOKUP expects keys across the top and answers in rows below.
Most data is arranged in columns, so HLOOKUP is used less. When a report has months or years as headers, it is the natural fit.
The same rules apply as for VLOOKUP: give it FALSE for an exact match, and lock the table range with dollar signs. For anything more flexible, XLOOKUP handles rows and columns with one function.
HLOOKUP examples
1. Sales for a named month
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Jan | Feb | Mar | |||
| 2 | West | 410 | 380 | 450 | 340 | |
| 3 | East | 295 | 340 | 310 |
The months run across row 1, so VLOOKUP cannot be used. HLOOKUP finds "Feb" in the top row of B1:D3 and returns the third row of the table in that column, the East figure (row 3 of the sheet).
2. Rate by quarter header
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Q1 | Q2 | Q3 | Q4 |
| 2 | 0.05 | 0.06 | 0.065 | 0.07 |
| 3 | ||||
| 4 | Q3 | 0.065 |
A small table with quarters across the top and a rate underneath. The lookup value comes from a cell so the same formula serves any quarter typed in A4.
3. Approximate match on horizontal bands
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 0 | 60 | 70 | 80 | 90 |
| 2 | F | D | C | B | A |
| 3 | |||||
| 4 | B |
With TRUE, HLOOKUP returns from the largest header that does not exceed the lookup value. The thresholds run across the top in ascending order. A score of 83 falls under the 80 header, so the grade is B.
Common HLOOKUP mistakes
- Leaving off FALSE. As with VLOOKUP, the default is approximate match. On unsorted headers it returns a wrong column silently.
- Counting rows from the sheet. row_index_num counts from the first row of the table range, so the top row is 1.
- Using HLOOKUP on vertical data. If the keys run down a column, use VLOOKUP or XLOOKUP. HLOOKUP looks only at the top row.
- Unlocked table range. Fill the formula across or down and the table slides. Use $B$1:$D$3.
- #N/A from a header that looks the same. A header such as "Feb " with a trailing space, or a month typed as a date, will not match the text "Feb".
HLOOKUP on Excel job tests
Months, quarters or years across the top of a table point to HLOOKUP; keys down the side point to VLOOKUP. Some tests set the two next to each other so that you must pick by layout.
"Look up the figure for the month in A10 from the table above": lock the table, use FALSE, and count rows from the top of the table range. A header typed as a real date rather than text will not match the text "Feb", so check what the headers actually are.
If the task targets Microsoft 365, XLOOKUP across a row is shorter. If it names HLOOKUP, use HLOOKUP.
Related functions
HLOOKUP questions
- What is the difference between VLOOKUP and HLOOKUP?
- VLOOKUP searches the first column and returns from a column. HLOOKUP searches the first row and returns from a row.
- Why does HLOOKUP return the wrong value?
- Usually the last argument was left out, so it did an approximate match, or the row number was counted wrongly.
- Can HLOOKUP look upward?
- No. It returns only from rows below the header row. Use INDEX and MATCH or XLOOKUP for other directions.
- Is HLOOKUP still used?
- Yes, mainly in older workbooks. XLOOKUP is the modern replacement.
Practice HLOOKUP
Try it in a working grid: the free lesson VLOOKUP: find a value by its key (module 11 of Everyday Excel) has checked HLOOKUP exercises.
Practice this function