About this lesson
SWITCH and CHOOSE: division names and a scenario picker in Google Sheets
In Google Sheets, SWITCH maps a code to a label, with an optional default at the end: =SWITCH(A8,"RX","Pharmacy","DX","Diagnostics","Unmapped"). CHOOSE returns the nth item in a list, so =CHOOSE(B1,B2,B3,B4) picks base, upside or downside growth from a scenario number in B1.
Using Excel? Do this lesson in Excel.
=SWITCH(A8,"RX","Pharmacy","DX","Diagnostics","HC","Home care","AH","Allied health","Unmapped")What you'll practise
Turn division codes into names with SWITCH, then pick base, upside or downside growth with CHOOSE and flip the whole forecast from one cell.
4 steps · 40 points · the cells outlined in gold below
- SWITCH
- CHOOSE
- scenario switch
- absolute references
The data
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Scenario | 1 | 1 base, 2 upside, 3 downside | |
| 2 | Base growth | 3.5% | ||
| 3 | Upside growth | 6% | ||
| 4 | Downside growth | -1.5% | ||
| 5 | Growth used | |||
| 6 | ||||
| 7 | Code | Division | FY26 revenue ($m) | FY27 ($m) |
| 8 | RX | 412.6 | ||
| 9 | DX | 268.3 | ||
| 10 | HC | 151.9 | ||
| 11 | AH | 88.2 | ||
| 12 | VT | 37.4 |