Excel track · Exercise 35 of 86 Paid
HLOOKUP: look across a row
Pull a price out of a row table by naming the row it sits on.
Intermediate
Monthly price sheet
Read-only data · answer in B5
In B5, look up the month in B4 across the top row of B1:D3 and write that month's price.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar |
| 2 | Units | 120 | 180 | 200 |
| 3 | Price | 4.5 | 5 | 5.25 |
| 4 | Lookup | Feb | ||
| 5 | Price |
Reference answer
What it does
HLOOKUP runs across the first row until it meets the month, then drops to the row number you name. The table B1:D3 has months in row 1, units in row 2 and prices in row 3. Feb sits in column C, so the third row there holds 5.
The row number is the trap. A 2 would return 180, the units figure, and it would pass as a price until someone checked it against the sheet. Count rows from the top of the range rather than the top of the sheet.
Write FALSE for an exact match. These headers run in calendar order, not alphabetical order. Approximate match assumes the top row is sorted, so a near miss can settle on the neighbouring month.
Syntax
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])Worked example
On Monthly price sheet, =HLOOKUP(B4,B1:D3,3,FALSE) returns 5.
=HLOOKUP(B4,B1:D3,3,FALSE)Common errors
| Error | What it means and how to fix it |
|---|---|
#N/A | The month is not in the first row of B1:D3, which leaves no column to read down. |
#VALUE! | The row number is the word price rather than a number. |
#REF! | The row number reaches below row 3 of B1:D3, so the answer sits outside the range. |
At work
Shift rosters and price sheets with the months across the top, read sideways.