SWITCH function

SWITCH (OriginalTestValue, FirstTestValue, FirstResultValue, OtherTestValue..., OtherResultValue..., OtherwiseValue?) SWITCH (OriginalTestValue; FirstTestValue; FirstResultValue; OtherTestValue...; OtherResultValue...; OtherwiseValue?)

OriginalTestValue

The original test value to check all other test values against.

FirstTestValue

A value to compare against the OriginalTestValue parameter. If they are equal, the FirstResultValue parameter is returned.

FirstResultValue

The value which is returned if the FirstTestValue parameter is equal to the OriginalTestValue parameter.

OtherTestValue

An additional value to compare against the OriginalTestValue parameter. If they are equal, the OtherResultValue parameter is returned.

OtherResultValue

The value which is returned if the OtherTestValue parameter is equal to the OriginalTestValue parameter.

OtherwiseValue

The value to return if no test values are equal to the OriginalTestValue parameter. If omitted, a blank value is returned.

Returns

The first result value whose corresponding test value matches the OriginalTestValue parameter. If there is no such value, the OtherwiseValue parameter is returned. If there is no such parameter, a blank value is returned instead.

Tests a given parameter for equality against a list of parameters and returns a parameter associated with the parameter which matched the first parameter. SWITCH (Value1, 1, 10, 2, 20, 3, 30, 45) SWITCH (Value1; 1; 10; 2; 20; 3; 30; 45) returns 10 if Value1 is equal to 1, 20 if it is equal to 2, 30 if it is equal to 3 and 45 otherwise.

SWITCH is a compact alternative to IF when you only need to test for equality against the same value. The formula above can also be written as IF (Value1 = 1, 10, Value1 = 2, 20, Value1 = 3, 30, 45) IF (Value1 = 1; 10; Value1 = 2; 20; Value1 = 3; 30; 45).

Like the = operator, SWITCH compares text strings in a case-insensitive manner. This formula returns 1:

SWITCH ("yes", "Yes", 1, 0)

SWITCH ("yes"; "Yes"; 1; 0)

SWITCH and arrays

Like the = operator, SWITCH compares arrays element by element. Applying SWITCH to an array of test values yields an array with the same size, where each element is the result value associated with the corresponding test value.

This formula returns the array { "a", "b" }{ "a"; "b" }, as the first array element matches 1 and the second array element does not:

SWITCH ({ 1, 2 }, 1, "a", "b")

SWITCH ({ 1; 2 }; 1; "a"; "b")

Examples

SWITCH (Value1, 1, 10, 2, 20)

SWITCH (Value1; 1; 10; 2; 20)

Returns 10 if Value1 is equal to 1, 20 if it is equal to 2 and a blank value otherwise.

SWITCH (Value1, 1, 10, 2, 20, 35)

SWITCH (Value1; 1; 10; 2; 20; 35)

Returns 10 if Value1 is equal to 1, 20 if it is equal to 2 and 35 otherwise.

SWITCH (Value1, 1, { 1 }, 2, { 1, 2 }, SEQUENCE (3))

SWITCH (Value1; 1; { 1 }; 2; { 1; 2 }; SEQUENCE (3))

Returns the array { 1 }{ 1 } if Value1 is equal to 1, { 1, 2 }{ 1; 2 } if it is equal to 2 and { 1, 2, 3 }{ 1; 2; 3 } otherwise.

SWITCH ({ 1, 2 }, 1, "a", "b")

SWITCH ({ 1; 2 }; 1; "a"; "b")

Returns the array { "a", "b" }{ "a"; "b" }, as the first array element matches 1 and the second array element does not.