About this lesson
INDEX and MATCH: lookups to the left and two-way lookups
INDEX and MATCH look up in any direction: =INDEX(A2:A8,MATCH(B10,E2:E8,0)) finds B10 in column E and returns column A, which VLOOKUP can't. With two MATCHes it's a two-way lookup: =INDEX(B2:D8,MATCH(B11,A2:A8,0),MATCH(B12,B1:D1,0)). For one-direction lookups, XLOOKUP, a newer function in Microsoft 365 and Excel 2021, has replaced it: =XLOOKUP(B10,E2:E8,A2:A8).
Using Google Sheets? Do this lesson in Sheets.
=INDEX(A2:A8,MATCH(B10,E2:E8,0))What you'll practise
Use INDEX and MATCH to look up a company from a ticker in the last column, pull one company's EBITDA for one year with a two-way lookup, then redo the first lookup with XLOOKUP.
4 steps · 40 points · the cells outlined in gold below
- INDEX and MATCH
- left lookup
- two-way lookup
- XLOOKUP
The data
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | EBITDA ($m) | FY25 | FY26 | FY27 | Ticker |
| 2 | Kimberley Cement | 148.6 | 162.3 | 171.9 | KMC |
| 3 | Tasman Ports | 362.1 | 388.4 | 409.7 | TSP |
| 4 | Otway Foods | 89.4 | 97.1 | 104.8 | OTW |
| 5 | Quokka Software | 58.2 | 74.6 | 93.5 | QSW |
| 6 | Mallee Grain | 92.7 | 88.9 | 95.3 | MLG |
| 7 | Coral Coast Resorts | 54.8 | 61.5 | 66.2 | CCR |
| 8 | Wombat Energy | 228.5 | 241.7 | 236.4 | WBE |
| 9 | |||||
| 10 | Ticker | OTW | |||
| 11 | Company | ||||
| 12 | Year | FY26 | |||
| 13 | EBITDA ($m) |