Learn Blog Pricing About Sign in

Learn

Blog

Pricing

About

Sign in

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

(accepts many)

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

otherResultValue

(accepts many)

The value which is returned if the otherTestValue parameter is equal to the originalTestValue parameter.

otherwiseValue

(optional)

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(Field1, 1, 10, 2, 20, 3, 30, 45)SWITCH(Field1; 1; 10; 2; 20; 3; 30; 45) returns 10 if Field1.ValueField1,Value 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(Field1 = 1, 10, Field1 = 2, 20, Field3 = 3, 30, 45)IF(Field1 = 1; 10; Field1 = 2; 20; Field3 = 3; 30; 45).

Examples

SWITCH(Field1, 1, 10, 2, 20)SWITCH(Field1; 1; 10; 2; 20)

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

SWITCH(Field1, 1, 10, 2, 20, 35)SWITCH(Field1; 1; 10; 2; 20; 35)

Returns 10 if Field1.ValueField1,Value is equal to 1, 20 if it is equal to 2 and 35 otherwise.

SWITCH(Field1, 1, { 1 }, 2, { 1, 2 }, SEQUENCE(3))SWITCH(Field1; 1; { 1 }; 2; { 1; 2 }; SEQUENCE(3))

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

SWITCH({ Field1, Field2 }, { 1, 2 }, 2, 3)SWITCH({ Field1; Field2 }; { 1; 2 }; 2; 3)

Returns 2 if Field1.ValueField1,Value is equal to 1 and Field2.ValueField2,Value is equal to 2. Otherwise, 3 is returned.

SWITCH({ Field1.Value, Field2.Visible }, { 1, TRUE }, 2, 3)SWITCH({ Field1,Value; Field2,Visible }; { 1; TRUE }; 2; 3)

Returns 2 if Field1.ValueField1,Value is equal to 1 and Field2Field2 is visible. Otherwise, 3 is returned. SWITCH({ Field1, Field2.Visible }, { 1, TRUE }, 2, 3)SWITCH({ Field1; Field2,Visible }; { 1; TRUE }; 2; 3) (which is not explicit about the fact that the Value property of Field1 should be used) cannot be used, as it only compares the logical values of the two arrays.