About this lesson
HLOOKUP: pulling one year from a five-year plan
HLOOKUP is VLOOKUP on its side. It finds a value in the top row of a table and returns the value a set number of rows down. =HLOOKUP(B7,B1:F4,2,FALSE) returns row 2 for the year in B7. The newer XLOOKUP function works across rows too, so it replaces HLOOKUP as well: =XLOOKUP(B7,B1:F1,B2:F2).
Using Google Sheets? Do this lesson in Sheets.
=HLOOKUP(B7,B1:F4,2,FALSE)What you'll practise
Pick a year from a plan laid out across the page with HLOOKUP, switch years from one cell, then do the same across a row with XLOOKUP.
4 steps · 40 points · the cells outlined in gold below
- HLOOKUP
- row index
- XLOOKUP across a row
The data
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ($m) | FY26 | FY27 | FY28 | FY29 | FY30 |
| 2 | Revenue | 412.6 | 447.9 | 481.3 | 522.8 | 559.4 |
| 3 | EBITDA | 61.8 | 69.4 | 76.1 | 84.7 | 91.2 |
| 4 | Capex | 23.4 | 25.1 | 27.9 | 29.6 | 31.8 |
| 5 | ||||||
| 6 | ||||||
| 7 | Year | FY28 | ||||
| 8 | Revenue ($m) | |||||
| 9 | EBITDA ($m) | |||||
| 10 | Capex ($m) |