HLOOKUP in Excel: how to use it
HLOOKUP looks for a value in the top row of a table and gives what is in the same column below it. Handy when your data runs across, such as months.
Try the first exercises for freeIn the lesson Looking up in a row with HLOOKUP
What does HLOOKUP do?
HLOOKUP works like VLOOKUP, but turned on its side. It looks up a value in the top row of a table and gives you what's in the same column, a number of rows further down.
You use it when your table is wide rather than long: the months, years or products are next to each other in the first row, with the figures below. The third argument is then a row number, counted from the top row of your table.
When do you use HLOOKUP?
- The revenue for a month from a table with the months next to each other: =HLOOKUP("March",A1:M3,2,FALSE).
- The rate for a year from a table with the years across the top: =HLOOKUP(2026,A1:F2,2,FALSE).
- The number of customers in a month from the same table, with a different row number: =HLOOKUP(B6,A1:M3,3,FALSE).
- A table of bands laid out side by side, with TRUE as the last argument: =HLOOKUP(B6,B1:D2,2,TRUE).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
Filled in: =HLOOKUP("March",A1:E3,2,FALSE)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
HLOOKUP | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | HLOOKUP |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
lookup_value | What you look for in the top row. | "March" |
table_array | The table, with the values you look for in the first row. | A1:E3 |
row_index_num | Which row of your table you want the answer from. Count from 1. | 2 |
[range_lookup] | Optional. FALSE for an exact match, TRUE for bands. | FALSE |
, | A comma separates the arguments from each other. | , |
" " | You type text in quotation marks, such as "Leeds". A number or a cell, such as 500 or A2, you type without them. | |
[ ] | Anything in square brackets can be left out. You never type the square brackets themselves. |
HLOOKUP step by step
G1 contains =HLOOKUP("March",A1:E3,2,FALSE). Looks for March in the top row and gives what is in row 2 below it: the revenue. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click G1 and type the function with a bracket.
=HLOOKUP( - Type what you're looking for in the top row: "March". Type a comma.
=HLOOKUP("March", - Select the whole table, from A1 to E3. The row you search in has to be the top row of your selection. Type a comma.
=HLOOKUP("March",A1:E3, - Count the rows from the top: Month is 1, Revenue is 2 and Customers is 3. You want the revenue, so type 2 and a comma.
=HLOOKUP("March",A1:E3,2, - Type FALSE for an exact match, close the bracket and press Enter: 15100.
=HLOOKUP("March",A1:E3,2,FALSE)
More examples
Another row
G1 contains =HLOOKUP("February",A1:E3,3,FALSE). With row 3 you get the number of customers in February.
The month from a cell
G2 contains =HLOOKUP(G1,A1:E3,2,FALSE). The month is in G1. Type January there, and you see the revenue for January straight away.
A table of bands side by side
F1 contains =HLOOKUP(75,B1:D2,2,TRUE). With TRUE, Excel looks in the top row for the largest value that isn't greater than 75: that's 50. The limits go from small to large.
HLOOKUP or VLOOKUP?
If the values you look for are in the first column, one below the other, use VLOOKUP. If they're side by side in the first row, such as months or years, use HLOOKUP. In Excel 2021 and 365, XLOOKUP does both.
Where it often goes wrong
- If you forget FALSE, Excel settles for something close, without warning you.
- If you add a row to your table, your row number is no longer right.
Practise this in the lesson Looking up in a row with HLOOKUP. The first exercises are free.
Frequently asked questions
Does HLOOKUP care about upper and lower case?
No: March and MARCH are the same for HLOOKUP.