CHOOSE function

Returns a parameter with the given numeric position. CHOOSE(3, 10, 20, 30, 40)CHOOSE(3; 10; 20; 30; 40) returns 30, because 30 is the third value parameter (and fourth parameter overall).

If the first parameter is 2, the second value parameter (and third parameter overall) is returned. If the first parameter is 3, the third value parameter (and fourth parameter overall) is returned, etc.

Multiple results

The first parameter is typically a number, but it can also be an array of numbers, which enables multiple results to be returned. If an array of numbers is provided, an array of results are returned.

This formula returns { 20, 40, 30 }{ 20; 40; 30 }, because the second, fourth and third value parameters are selected, in that order:

CHOOSE({ 2, 4, 3 }, 10, 20, 30, 40)CHOOSE({ 2; 4; 3 }; 10; 20; 30; 40)

CHOOSE and drop-down fields

CHOOSE is especially useful when combined with drop-down fields. Such fields provide a property, Index, which returns 1 if the first item is selected, 2 if the second item is selected, etc. If NumberDropDownField1.IndexNumberDropDownField1,Index is given as the first parameter to CHOOSE, and the number of remaining parameters match the number of items in the drop-down field, CHOOSE can be used to return data related to the selected item of the drop-down field.

Let's say that a text drop-down field, named ProductSelector, contains the items Product A, Product B and Product C. This formula is then associated with a number field presenting the price of the selected product:

CHOOSE(ProductSelector.Index, 19, 29, 49)CHOOSE(ProductSelector,Index; 19; 29; 49)

The number field displays $19 if Product A is selected, $29 if Product B is selected and $49 if Product C is selected.

CHOOSE formulas can automatically be generated directly from spreadsheet tables, enabling CHOOSE invocations with hundreds or even thousands of parameters to be easily generated. This is done using an app we provide.

Examples

CHOOSE(3, 10, 20, 30, 40)CHOOSE(3; 10; 20; 30; 40)

Returns 30, the third value parameter (and fourth parameter overall).

CHOOSE(4, 10, 20, 30, 40)CHOOSE(4; 10; 20; 30; 40)

Returns 40, the fourth value parameter (and fifth parameter overall).

CHOOSE({ 3, 4 }, 10, 20, 30, 40)CHOOSE({ 3; 4 }; 10; 20; 30; 40)

Returns { 30, 40 }{ 30; 40 }, the third and fourth value parameters (corresponding to the fourth and fifth parameters overall), in a single array.

CHOOSE(ProductSelector.Index, 19, 29, 49)CHOOSE(ProductSelector,Index; 19; 29; 49)

Returns 19, 29 or 49, depending on what item has been selected in the drop-down field named ProductSelector.

CHOOSE({ NumberDropDownField1.Index, NumberDropDownField2.Index }, 19, 29, 49)CHOOSE({ NumberDropDownField1,Index; NumberDropDownField2,Index }; 19; 29; 49)

Returns an array containing two elements, which are either 19, 29 or 49, depending on the selections made in the drop-down fields NumberDropDownField1 and NumberDropDownField2. If the first item is selected in Field1 and the third item is selected in Field2, { 19, 49 }{ 19; 49 } is returned.