HLOOKUP from a table laid out sideways
When the keys run along the top, look across instead of down.
Task:
Annual subscription prices are listed by year across row 1, with the price in row 2. In C5:C8 give the price each customer paid in the year they joined.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of HLOOKUP
0/3The same formula in the other shapes it takes at work.
- Basic HLOOKUP Function
Use HLOOKUP to find values in a horizontally arranged table.
CoreIntermediate - HLOOKUP from a table laid out sideways
When the keys run along the top, look across instead of down.
Beginner - HLOOKUP approximate match with two rate rows
Weight bands across the top, one row per service — pick the band and the row.
Advanced
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Year | 2021 | 2022 | 2023 | 2024 |
| 2 | Price | 120 | 132 | 145 | 159 |
| 3 | |||||
| 4 | Customer | Joined | Price paid | ||
| 5 | Garnet Ltd | 2023 | |||
| 6 | Hazel & Co | 2021 | |||
| 7 | Indigo plc | 2024 | |||
| 8 | Jade Inc | 2022 |
What this exercise teachesMay contain the answer
HLOOKUP is VLOOKUP turned on its side: the keys are in the first row and you count rows instead of columns. Tables like this — one column per year or per month — are common in budgets and price histories.