Matching codes with SWITCH: practice exercises
SWITCH, a default result, WEEKDAY. 4 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.
In C2, turn each region code into the region's name: N is North, S is South, E is East and W is West.
| A | B | C | |
|---|---|---|---|
| 1 | Employee | Region code | Region |
| 2 | Marek | E | |
| 3 | Hamza | W | |
| 4 | Kerry | S | |
| 5 | Chen | W | |
| 6 | Ollie | W | |
| 7 | Will | E | |
| 8 | Ben | S | |
| 9 | Tanya | S | |
| 10 | Liam | E | |
| 11 | Femi | N |
How it works
SWITCH(value, match1, result1, match2, result2, …, default) compares one value with a list of possible matches and gives the result paired with the first one that fits. The last argument, on its own with no pair, is what to show when nothing fits. Leave it out and you get #N/A.
Use SWITCH when you're matching exact values, such as codes. Use IFS when you need comparisons, such as >=70.
=SWITCH(A2,"N","North","E","East","Unknown") =SWITCH(WEEKDAY(A2),1,"Sun",7,"Sat","Weekday")
One value, many results
Codes are everywhere: N for North, 1 for High. SWITCH turns each code into what it stands for.
SWITCH(value, match1, result1, match2, result2, …, default)
- value: the cell to check, such as A2.
- Then pairs: a possible match, and the result to show for it.
- Last, on its own, the default: what to show when nothing matches.
Worked example
| A | B | |
|---|---|---|
| 1 | Code | Region |
| 2 | N | |
| 3 | E | |
| 4 | X |
=SWITCH(A2,in . Start with the cell to check: the code in A2.=SWITCH(A2,"N","North","E","East")in . Add the pairs: "N" gives North, "E" gives East. It works for N and E, but X matches nothing, so B4 shows #N/A.=SWITCH(A2,"N","North","E","East","Unknown")in . Add a default at the end, with no pair. Now X shows Unknown instead of an error.
SWITCH or IFS?
SWITCH only checks whether the value is equal to each match. For bands such as "70 or more", use IFS or a lookup table.
The value can be a formula too. WEEKDAY(A2) gives 1 for Sunday up to 7 for Saturday, so SWITCH(WEEKDAY(A2),1,"Sun",7,"Sat","Weekday") names the weekend days.
What you'll practise
- In C2, turn each region code into the region's name: N is North, S is South, E is East and W is West.
- Priority 1 is High, 2 is Medium and 3 is Low. Some tasks were given a number that isn't allowed. In C2, show the label, or Check for any other number.
- WEEKDAY(date) gives 1 for Sunday, 2 for Monday, and so on up to 7 for Saturday. In C2, use SWITCH and WEEKDAY to show Weekend for deliveries on a Saturday or Sunday and Weekday for the rest.
- The price per parcel for each service is in H1:H3. In D2, use SWITCH to pick the right price for the service in column C, and multiply it by the number of parcels.