About this lesson
XMATCH: finding positions in a telco's plan line-up
XMATCH returns the position of a value in a list and matches exactly unless told otherwise: =XMATCH(B10,A2:A8). It's the newer version of MATCH in Microsoft 365 and Excel 2021. It can find the next smaller value without a sorted list, search from the bottom, and give INDEX a row and a column: =INDEX(D2:E8,XMATCH(B10,A2:A8),XMATCH(B12,D1:E1)).
Using Google Sheets? Do this lesson in Sheets.
=XMATCH(B10,A2:A8)What you'll practise
Find a plan's row and a quarter's column with XMATCH and hand both to INDEX, pick the dearest plan under a budget from unsorted prices, and find the newest Home plan by searching from the bottom.
4 steps · 40 points · the cells outlined in gold below
- XMATCH
- INDEX with XMATCH
- approximate match on unsorted data
- search from the bottom
The data
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Plan | Type | Price ($) | Q3 FY26 | Q4 FY26 |
| 2 | Mobile Lite | Mobile | 25 | 412.6 | 398.1 |
| 3 | Home 50 | Home | 75 | 288.3 | 281.7 |
| 4 | Mobile Plus | Mobile | 45 | 236.9 | 251.4 |
| 5 | Business Pro | Business | 95 | 64.2 | 67.8 |
| 6 | Home 100 | Home | 90 | 173.5 | 186.2 |
| 7 | Mobile Max | Mobile | 65 | 98.4 | 117.3 |
| 8 | Home 250 | Home | 110 | 41.7 | 52.9 |
| 9 | |||||
| 10 | Plan | Mobile Plus | |||
| 11 | Row | ||||
| 12 | Quarter | Q4 FY26 | |||
| 13 | Column | ||||
| 14 | Subscribers (k) | ||||
| 15 | |||||
| 16 | Budget ($) | 70 | |||
| 17 | Dearest plan | ||||
| 18 | Newest Home plan |