About this lesson
VLOOKUP exact match: pulling a company's numbers by ticker
VLOOKUP finds a value in the first column of a table and returns the value a set number of columns across: =VLOOKUP(B11,A2:E9,4,FALSE) returns column 4 for the ticker in B11. FALSE means exact match. XLOOKUP, a newer function in Microsoft 365 and Excel 2021, does the same job without counting columns: =XLOOKUP(B11,A2:A9,D2:D9).
Using Google Sheets? Do this lesson in Sheets.
=VLOOKUP(B11,A2:E9,2,FALSE)What you'll practise
Build a lookup panel that pulls a listed peer's market cap, EBITDA and net debt from its ticker, then rewrite one line with XLOOKUP, the newer function that has replaced VLOOKUP.
4 steps · 40 points · the cells outlined in gold below
- VLOOKUP
- exact match
- column index
- XLOOKUP
The data
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Ticker | Company | Mkt cap ($m) | EBITDA ($m) | Net debt ($m) |
| 2 | KMC | Kimberley Cement | 1,284.6 | 162.3 | 241.8 |
| 3 | TSP | Tasman Ports | 3,917.2 | 388.4 | 1,106.5 |
| 4 | OTW | Otway Foods | 846.9 | 97.1 | 58.3 |
| 5 | QSW | Quokka Software | 2,205.4 | 74.6 | -132.7 |
| 6 | MLG | Mallee Grain | 612.3 | 88.9 | 174.2 |
| 7 | CCR | Coral Coast Resorts | 498.7 | 61.5 | 203.9 |
| 8 | WBE | Wombat Energy | 1,733.8 | 241.7 | 512.4 |
| 9 | BNH | Banksia Health | 2,964.1 | 279.3 | 388.6 |
| 10 | |||||
| 11 | Ticker | KMC | |||
| 12 | Company | ||||
| 13 | Mkt cap ($m) | ||||
| 14 | EBITDA ($m) | ||||
| 15 | Net debt ($m) |