CHOOSE

Description: Chooses a value or action to perform from a list of values, based on an index number. For example, if Value1 through Value7 are the days of the week, CHOOSE returns one of the days when a number between 1 and 7 is used as Index_num.

Syntax: CHOOSE(Index_num, Value1, Value2, ...)

Index_num specifies which value argument is selected. Index_num must be a number between 1 and 29, or a formula or reference to a cell containing a number between 1 and 29.

If Index_num is 1, CHOOSE returns Value1; if it is 2, CHOOSE returns Value2; and so on.

If Index_num is a fraction, it is truncated to the lowest integer before being used.

Value1, Value2,... are 1 to 29 value arguments from which CHOOSE selects a value or an action to perform based on Index_num. The arguments can be numbers, cell references, defined names, formulas, functions, or text.

Remarks:

If Index_num is an array, every value is evaluated when CHOOSE is evaluated.
The value arguments to CHOOSE can be range references as well as single values. For example, the formula:

SUM(CHOOSE(2,A1:A10,B1:B10,C1:C10))

evaluates to:

SUM(B1:B10)

which then returns a value based on the values in the range B1:B10.

The CHOOSE function is evaluated first, returning the reference B1:B10. The SUM function is then evaluated using B1:B10, the result of the CHOOSE function, as its argument.

Example:

CHOOSE(2, "1st", "2nd", "3rd", "Finished") = "2nd"
SUM(A1:CHOOSE(3, A10, A20, A30)) = SUM(A1:A30)
If A10 contains 4, then:

CHOOSE(A10, "Nails", "Screws", "Nuts", "Bolts") = "Bolts"

If SalesOld is a name defined to refer to the value 10,000, then:

CHOOSE(2, SalesNew, SalesOld, SalesBudget) = 10,000