Cellwise Start free

Logic function

Excel SWITCH function

SWITCH checks one value against a list of possible matches and returns the result paired with the first match. It is a tidy replacement for a long nested IF that keeps testing the same cell, such as turning region codes into names. Add a final default argument for anything that does not match.

=SWITCH(expression, value1, result1, [default_or_value2], ...)

Arguments

expression
The value to check, usually a cell.
value1
The first value to compare with.
result1
What to return when the expression equals value1.
default_or_value2
More value and result pairs, up to 126. A final argument with no partner is the default when nothing matches.

SWITCH examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

ABCD
1OrderRegion codeShip dateScore
2SO-71NNov 2, 202691
3SO-72ENov 7, 202684
4SO-73WNov 4, 202677
5SO-74SNov 8, 202668
6SO-75XNov 5, 202695

Download this sheet as an Excel practice file (.xlsx)

  1. =SWITCH(B2,"N","North","S","South","E","East","W","West","Unknown")

    Result: North

    B2 is N, so the result is North. Any code not in the list would return the default, Unknown.

  2. =SWITCH(B6,"N","North","S","South","E","East","W","West")

    Result: #N/A

    B6 holds X, which matches nothing, and there is no default, so SWITCH returns #N/A.

  3. =SWITCH(WEEKDAY(C3),1,"Weekend",7,"Weekend","Weekday")

    Result: Weekend

    November 7, 2026 is a Saturday, WEEKDAY 7, so the order shipped at the weekend.

  4. =SWITCH(TRUE,D3>=90,"A",D3>=80,"B",D3>=70,"C","D")

    Result: B

    SWITCH(TRUE,...) returns the result for the first test that is TRUE: 84 is a B. IFS says the same thing more plainly.

How to use SWITCH

  1. Click the cell for the result.
  2. Type =SWITCH( and the cell to check.
  3. Add pairs of value, result: "N","North".
  4. End with a default value for anything else, then press Enter.

Common SWITCH mistakes

No default

Without a final default, an unmatched value returns #N/A. Add one, even if it is just "Check".

Expecting greater-than tests

SWITCH only tests for equal values. For bands such as scores of 80 and above, use IFS, or SWITCH(TRUE,...), or a lookup table.

A number against text

SWITCH compares types strictly: the number 1 does not match the text "1". Convert with VALUE or TEXT first.

SWITCH FAQ

What is the difference between SWITCH and IFS?

SWITCH compares one value against exact matches and can have a default. IFS takes any tests, such as B2>=80, and returns #N/A unless you end with TRUE and a fallback.

Is SWITCH case-sensitive?

No. "n" matches "N". For a case-sensitive match use IF with EXACT.

Which versions of Excel have SWITCH?

Excel 2019, Excel 2021, Microsoft 365 and Excel for the web. In Excel 2016 and earlier it returns #NAME?.

Guides that use SWITCH

Related functions

Updated