Documentation/Calc Functions/CHOOSE

    From The Document Foundation Wiki
    Other languages:


    Function name:

    CHOOSE

    Category:

    Spreadsheet

    Summary:

    Accepts a list of up to 30 values, returning one of them. The value to return is selected using an additional index argument.

    Syntax:

    CHOOSE(Index; Value 1 [; Value 2 [; ... [; Value 30]]])

    Returns:

    Returns one of the supplied values, selected using the given index. The type of value returned depends on the type of the indexed argument.

    Arguments:

    Index is an integer in the range 1 ≤ Index ≤ 30, or a reference to a cell containing such an integer, that determines which value is to be selected from the list and returned. If Index is 1, the function returns the first value in the list; if Index is 2, it returns the second value in the list; and so on.

    Value 1, Value 2, ..., Value 30 are values from which one is to be selected and returned. Each may be text, a number (real or integer), a date, a logical value, a reference, or a formula.

    • If Index is non-numeric, then CHOOSE reports a #VALUE! error.
    • If Index is a non-integer value, then CHOOSE truncates the value to an integer.
    • If, after truncation, Index is less than 1, or greater than the number of values supplied, then CHOOSE reports an invalid argument error (Err:502).
    • If more than 30 Value n arguments are supplied, then CHOOSE reports a parameter list error (Err:504).

    Additional details:

    • Providing that separators are present, values can be omitted from within the list. CHOOSE returns 0 if such a missing value is indexed. For example, the formula =CHOOSE(2; "str1"; ; "str3") returns 0.
    • CHOOSE can be used in an array formula. For example, select cells D1 to D6 and enter the array formula {=CHOOSE(1; A1:A6)} in the selected cells (using Ctrl + ⇧ Shift + ↵ Enter rather than just ↵ Enter). Cell D1 assumes the value in A1, cell D2 assumes the value in A2, and so on.
    • Arguments other than the one chosen are not calculated or evaluated for side effects. For example, the formula =CHOOSE(1; 2; SQRT(-1)) returns the value 2, but the formula =CHOOSE(2; 2; SQRT(-1)) gives an an invalid argument error (Err:502) showing that the SQRT operation is only attempted when that argument is indexed.

    Examples:

    Data for examples
    A B C
    1 2 1.234 2
    2 =PI() 2022-02-17 7
    3 =TRUE() 13
    4 =SQRT(64) 19
    Formula Description Returns
    =CHOOSE(1.34; "Base"; "Calc"; "Writer")

    Truncate the value 1.34 and return the first entry in the list of possible return values.

    Base
    =CHOOSE(C1; B1; B2; B3; B4)

    The function indexes the second entry in the list of possible return values. It is equivalent to =B2.

    2022-02-17
    =SUM(CHOOSE(1; C2:C4; B12))

    Return the sum of the numbers in the range C2:C4. It is possible to include a cell range as one of the values, but this needs to be in the correct context. =CHOOSE(1; C2:C4; B12) gives a #VALUE! error.

    39
    =CHOOSE(A1; B1; A2; B3; "Today"; "Yesterday"; "Tomorrow")

    The function indexes the second entry in the list of possible return values and returns the approximate value of π. If the value in cell A1 is changed to 4, then the text "Today" is returned instead.

    3.14159265358979

    Related LibreOffice functions:

    IF

    IFS

    SWITCH

    ODF standard:

    Section 6.14.3, part 2

    Related (or similar) Excel functions:

    CHOOSE