Practice free

SWITCH function

SWITCH compares one expression against a list of exact values and returns the result for the first match, or a default. It is cleaner than nested IFs when testing one value for equality.

Example

Read it as a sentence: check F1 against 0 and show zero if it matches, otherwise check for an empty text and show empty, which gives zero.

Practice this in the app

Syntax

SWITCH(expression, value1, result1, [value2, result2], ..., [default])

Arguments in [square brackets] are optional.

expression (required)
The value to compare.
value1 (required)
First value to match against.
result1 (required)
Result if expression equals value1.
value2, result2 (optional)
More match and result pairs.
default (optional)
Fallback if nothing matches; a lone final argument.

Returns: The result for the first matching value, or the default.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=SWITCH(2,1,"a",2,"b") b Match
=SWITCH(3,1,"a",2,"b","z") z Default
=SWITCH("2",2,"num","2","txt") txt Text vs number
=SWITCH({1,2},1,"a",2,"b") a Array spills
=SWITCH("A","a","lower") lower Case-insensitive?
=SWITCH(Data!F1,0,"zero","","empty") zero Blank value
=SWITCH(3,1,"a",2,"b") #N/A No match
=SWITCH(Data!F5,1,"a","z") #DIV/0! Error value

The Data sheet these formulas read

A cell that starts with = holds that formula.

Watch out for

  • Only exact equality; it cannot test ranges like >10.
  • Without a default and no match you get #N/A.
  • An odd trailing argument is treated as the default.

Where it works

Excel
SWITCH arrived in Excel 2019. Older versions do not have it.
Google Sheets
Same name, same behaviour for everyday use.

Part of Logical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .