=HLOOKUP("Mar",A1:E3,2,FALSE) looks for Mar in the first row of A1:E3 and returns the value from the second row of the same column. It is VLOOKUP turned on its side, for tables where the labels run across the top.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar | Apr |
| 2 | Sales | 4,200 | 3,900 | 4,800 | 5,100 |
| 3 | Costs | 2,600 | 2,500 | 2,900 | 3,000 |
| 4 | |||||
| 5 | Month | Mar | |||
| 6 | Sales | 4,800 |
B6 searches row 1 for Mar, finds it in column D, and returns row 2 of that column: 4,800. Pick Apr in B5 for 5,100, or change the 2 in the formula to 3 for the costs.
HLOOKUP syntax
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
lookup_value: what to find in the first row of the table.table_array: the table. HLOOKUP only searches its top row.row_index_num: which row to return, counting the top row as 1. A number larger than the table is tall gives #REF!; 0 gives #VALUE!.range_lookup:FALSEfor an exact match.TRUEor nothing for an approximate match on a sorted row.
The match ignores case (mar finds Mar), and with FALSE the lookup value can use the wildcards * and ?. A value not in the first row returns #N/A.
Approximate match across a row
With TRUE, HLOOKUP finds the largest header less than or equal to the lookup value. The first row must be sorted from left to right in ascending order.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Weight from (kg) | 0 | 2 | 5 | 10 |
| 2 | Cost | $4.50 | $6.00 | $9.50 | $14.00 |
| 3 | |||||
| 4 | Parcel (kg) | 7 | |||
| 5 | Cost | $9.50 |
7 kg is not a header. The largest header not above it is 5, so B5 returns 4.50 or to 12 for $14.00. The table in the formula is B1:E2, not A1:E2: it starts at the first weight so the text label in A1 is not part of the sorted row.
XLOOKUP across a row
In Excel 2021 and Microsoft 365, XLOOKUP replaces HLOOKUP. It takes the row to search and the row to return as two ranges, so there is no row number to count, and a return range several rows tall brings back the whole column.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar | Apr |
| 2 | Sales | 4,200 | 3,900 | 4,800 | 5,100 |
| 3 | Costs | 2,600 | 2,500 | 2,900 | 3,000 |
| 4 | Profit | 1,600 | 1,400 | 1,900 | 2,100 |
| 5 | |||||
| 6 | Month | Feb | |||
| 7 | Figures | 3,900 | |||
| 8 | 2,500 | ||||
| 9 | 1,400 |
One formula in B7 spills the three figures for Feb down B7:B9: 3,900, 2,500 and 1,400. If B8 or B9 held something, B7 would show #SPILL!. The XLOOKUP page covers its other options, such as a not-found message and the last match.
Turn the table instead: TRANSPOSE
Sometimes the better fix is a vertical copy of the table. =TRANSPOSE(A1:D3) returns the same cells with rows and columns swapped, and it stays linked to the original. VLOOKUP, FILTER and charts then work on it as usual.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar |
| 2 | Sales | 4,200 | 3,900 | 4,800 |
| 3 | Costs | 2,600 | 2,500 | 2,900 |
| 4 | ||||
| 5 | Month | Sales | Costs | |
| 6 | Jan | 4,200 | 2,600 | |
| 7 | Feb | 3,900 | 2,500 | |
| 8 | Mar | 4,800 | 2,900 |
A5 spills a 4 by 3 block: months down the side, Sales and Costs across the top. Change Feb's sales in C2 to 4100 and the copy follows. For a one-off copy without a formula, select the table, copy it, then use Home > Paste > Paste Special and tick Transpose.
Practice: costs of a month
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar | Apr |
| 2 | Sales | 4,200 | 3,900 | 4,800 | 5,100 |
| 3 | Costs | 2,600 | 2,500 | 2,900 | 3,000 |
| 4 | |||||
| 5 | Month | Apr | |||
| 6 | Costs |
Your turn: In B6, use HLOOKUP to return the costs of the month in B5.
Frequently Asked Questions
What is the difference between VLOOKUP and HLOOKUP?
VLOOKUP searches down the first column of a table and returns a value from a column to the right. HLOOKUP searches across the first row and returns a value from a row below. The arguments are the same, with a row index in place of the column index.
What is the row index in HLOOKUP?
The number of the row to return, counted from the first row of the table, which is row 1. In =HLOOKUP("Mar",A1:E3,3,FALSE), 3 means the third row of A1:E3. A number larger than the table is tall returns #REF!.
Can XLOOKUP replace HLOOKUP?
Yes. XLOOKUP works in either direction: =XLOOKUP("Mar",B1:E1,B2:E2) searches a row and returns from another row. It needs Excel 2021 or Microsoft 365.
Why does HLOOKUP return #N/A?
The lookup value is not in the first row of the table: a typo, an extra space, a number stored as text, or a value that sits in another row. With TRUE as the last argument, a value smaller than the first header also returns #N/A.
