SWITCH

SWITCH compares one expression against a list of possible values and returns the result that goes with the first exact match, with an optional default. It is the cleanest way to translate codes into labels, such as country codes into names or status numbers into words.

Syntax

=SWITCH(expression, case1, value1, [case2, value2, ...], [default])

• expression: The value or formula to test.
• case1: The first value to compare against expression.
• value1: The result when expression equals case1.
• case2, value2, ... [Optional]: More case/result pairs, checked in order.
• default [Optional]: A final argument with no case in front of it, returned when nothing matches. It must be the last argument.

SWITCH checks for exact equality only. It cannot test ranges or comparisons such as > 10; use IFS for that.

Basic example

Turning a status code in A2 into text:

=SWITCH(A2, 1, "Open", 2, "In progress", 3, "Closed", "Unknown")

If A2 is 2, the result is In progress. If it is 7, the result is Unknown, the default.

Without a default, no match returns #N/A:

=SWITCH(A2, 1, "Open", 2, "In progress", 3, "Closed")

Text cases

=SWITCH(B2, "US", "United States", "UK", "United Kingdom", "FR", "France", "Other")

Matching is not case-sensitive for text, so "us" matches "US".

The SWITCH(TRUE, ...) pattern

You can get range-style tests by switching on TRUE and using conditions as the cases. The first condition that evaluates to TRUE wins:

=SWITCH(TRUE, A1 >= 90, "A", A1 >= 80, "B", A1 >= 70, "C", "F")

This works like IFS. It's useful when you want to keep a SWITCH structure and a default value without needing TRUE, "default" at the end.

Practical patterns

Map weekday numbers to names

=SWITCH(WEEKDAY(A2), 1, "Sunday", 2, "Monday", 3, "Tuesday", 4, "Wednesday", 5, "Thursday", 6, "Friday", 7, "Saturday")

(For this one, TEXT(A2, "dddd") is shorter.)

Compute different values per case

The results can be calculations:

=SWITCH(C2, "Gold", B2 * 0.8, "Silver", B2 * 0.9, B2)

Gold customers pay 80%, Silver pay 90%, everyone else pays full price.

Fill a column

=ARRAYFORMULA(SWITCH(A2:A, 1, "Open", 2, "In progress", 3, "Closed", ""))

Compare to values in cells

Cases can be cell references too:

=SWITCH(A2, E1, F1, E2, F2, E3, F3, "None")

For longer lists, though, put the mapping in a table and use XLOOKUP.

Common errors

• #N/A: "No cases matched and no default provided". Add a final default value.
• Wrong number of arguments. After the expression, arguments come in case/value pairs, plus an optional single default at the end. An even count after the expression means there is no default. An odd count means the last one is the default.
• Number vs. text mismatch. SWITCH(A2, 1, ...) will not match the text "1". Make sure the types agree.
• Trying to use > or <. SWITCH(A1, >10, ...) is invalid. Use SWITCH(TRUE, A1 > 10, ...) or IFS.

SWITCH vs. IFS vs. nested IF vs. lookup table

• SWITCH: one value compared with several exact options. The most readable for code-to-label mapping.
• IFS: different conditions, including ranges and comparisons.
• IF: a single test, with nested IF for a few more.
• Lookup table (XLOOKUP or VLOOKUP): best when there are many cases or they change often, since you edit a table, not a formula.

Frequently asked questions

Is SWITCH case-sensitive? No, for text comparisons.

Can SWITCH match multiple values to one result? Not directly. Repeat the result for each case, or use IFS with OR.

Is there a limit to the number of cases? No practical limit, but a lookup table is better beyond about ten.

Related Functions

• IFS: Test several conditions in order.
• IF: One test, two outcomes.
• XLOOKUP: Look up values in a mapping table.
• VLOOKUP: Vertical lookup in a table.
• CHOOSE: Pick a value from a list by its index number.

Related Articles

• How to Use Logical Functions in Google Sheets: AND, OR, NOT
• 10 Essential Google Sheets Formulas Every User Should Know