The SWITCH function

The SWITCH function is a logical function which adds a new piece of functionality to the family of IF functions. SWITCH allows for users to validate the output of one cell or formula, and use the output to select which argument needs to be calculated.


This is the format of the SWITCH function:

=SWITCH(Expression, Value 1, Output 1, Value 2, Output 2, etc…)

Here is an example of the SWITCH function:

=SWITCH( 1+1, 1, “That’s not right”, 2, “That’s right”, 3 “That’s not right”) = “That’s right” 
SWITCH returning the proper output based on the result of the initial evaluated input

SWITCH returning the proper output based on the result of the initial evaluated input

This function is valuable when the output of a cell, or range of cells, is integral to the output of a form or function. Any situation where there are more than 2 potential outcomes, given the result of an expression, benefits from the SWITCH function.

An example of a functional implementation of this would be if a portion of your duties involves the assessment of a clinical study. In the study you had four testing groups, A-D. A and B were given the drug being tested, C and D were given placebo pills. You want to assign a value of ‘Test’ or ‘Control’ to each patient’s profile.

=SWITCH(G3, “A”, “Test”, “B”, “Test”, “C”, “Control”, “D”, “Control”)