SWITCH compares one expression with a list of cases and returns the result for the first match. Use it for a short code-to-label mapping or for choosing a calculation by category.
SWITCH function syntax
=SWITCH(expression, case1, value1, [case2, value2, ...], [default])- expression: the cell value or calculation to compare.
- case1 and value1: a possible match and its result.
- Additional case/result pairs add alternatives.
- default: an optional final unpaired result when no case matches. Without it, no match returns #N/A.
Set up the example data
Enter this dataset starting in A1. The first row contains headings. Keep the result area separate from the source table.
| Code |
|---|
| M |
| T |
| W |
| R |
| X |
Map a code to a readable name
Enter this formula in A9. M becomes Monday and T becomes Tuesday. Other codes return Unknown in this deliberately short mapping, making missing cases visible.
=SWITCH(A2,"M","Monday","T","Tuesday","Unknown")Result: Monday.
Extend the weekday mapping
Add each new case and result before the default. R represents Thursday in this example, avoiding the ambiguity of using T for both Tuesday and Thursday.
=SWITCH(A5,"M","Monday","T","Tuesday","W","Wednesday","R","Thursday","F","Friday","Unknown")Result: Thursday.
Translate numeric category codes
The numeric code 3 returns Books. CHOOSE instead selects by a position number, while SWITCH can match arbitrary codes without making them consecutive indexes.
=SWITCH(3,1,"Electronics",2,"Clothing",3,"Books",4,"Home","Other")Result: Books.
Switch on a calculated expression
MOD returns the remainder after dividing the integer 7 by 2. The remainder is 1, so SWITCH returns Odd. The expression can be a calculation rather than a direct reference.
=SWITCH(MOD(7,2),0,"Even",1,"Odd")Result: Odd.
Compare TRUE with threshold tests
For a score of 82, the >=90 case is FALSE and the >=80 case is TRUE. SWITCH matches TRUE and returns B. IFS is another way to write ordered conditions.
=SWITCH(TRUE,82>=90,"A",82>=80,"B",82>=70,"C","F")Result: B.
Choose a calculation for each region
Enter the region table at A20. West sales of 2000 receive the illustrative 8% multiplier, giving 160. East uses 10%, North 6%, and the default 5%.
| Region | Sales |
|---|---|
| East | 1000 |
| West | 2000 |
| South | 1500 |
=SWITCH(A22,"East",B22*0.1,"West",B22*0.08,"North",B22*0.06,B22*0.05)Result: 160.
Expose an unknown code without a default
X matches neither M nor T, so omitting the default returns #N/A. Add a default only when you have chosen how unknown cases should be treated.
=SWITCH(A6,"M","Monday","T","Tuesday")Result: #N/A.
Other Google Sheets articles you may also like