Confident Cells

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.

Your turn

In C2, turn each region code into the region's name: N is North, S is South, E is East and W is West.

ABC
1EmployeeRegion codeRegion
2MarekE
3HamzaW
4KerryS
5ChenW
6OllieW
7WillE
8BenS
9TanyaS
10LiamE
11FemiN

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

AB
1CodeRegion
2N
3E
4X
  1. =SWITCH(A2, in . Start with the cell to check: the code in A2.
  2. =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.
  3. =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

  1. In C2, turn each region code into the region's name: N is North, S is South, E is East and W is West.
  2. 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.
  3. 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.
  4. 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.