About this lesson
MATCH: finding a store's position and size band
MATCH returns the position of a value in a range: =MATCH(B11,A2:A9,0) gives the row within A2:A9 where B11 is found. Use 0 for an exact match. 1, the default, is an approximate match that needs the range sorted smallest to largest. XMATCH, a newer version of MATCH in Microsoft 365 and Excel 2021, defaults to exact match instead.
Using Google Sheets? Do this lesson in Sheets.
=MATCH(B11,A2:A9,0)What you'll practise
Find where a store sits in a list with MATCH's exact match, feed the position to INDEX, then use approximate match to put the store in a size band.
4 steps · 40 points · the cells outlined in gold below
- MATCH
- exact vs approximate match
- INDEX with MATCH
- XMATCH
The data
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Store | Revenue ($k) | Band from ($k) | Size band | |
| 2 | Geelong | 4286 | 0 | Small | |
| 3 | Ballarat | 2941 | 1500 | Medium | |
| 4 | Bendigo | 1873 | 3000 | Large | |
| 5 | Wagga Wagga | 3512 | 5000 | Flagship | |
| 6 | Toowoomba | 5604 | |||
| 7 | Launceston | 1327 | |||
| 8 | Cairns | 3068 | |||
| 9 | Albury | 2215 | |||
| 10 | |||||
| 11 | Store | Cairns | |||
| 12 | Position | ||||
| 13 | Revenue ($k) | ||||
| 14 | Band no. | ||||
| 15 | Size band |