|
Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Workiva Support Center

CHOOSE

CHOOSE

  • Last updated on

Description

Use this function to return a value from a list based on a specified position or index number.  Supported in Chains. Can be used with CHILDREFS.

CHOOSE lets you extract one value from a list of up to 254 value arguments based on a given index number.

 

Syntax

CHOOSE(index_num, value_1, […, value_254])

 

Inputs

This function accepts the following arguments:

Name Required Description Valid input
index_num Yes Specifies which value argument to return.  A positive integer between 1 and 254, a reference to a cell containing a positive integer between 1 and 254, or a formula which results in either of these. It must also not be greater than the number of values being considered.
value_1 Yes The first value in the list of options. A cell reference, a cell range, a number, a text string, or a formula which results in any of these.  
value_n No Additional values in the list of options.  Cell reference, a cell range, a number, a text string, or a formula which results in any of these. You can include up to 254 value arguments.

 

Example

Sample data

 

A

B

1

1

 Apple

2

2

 Banana

3

3

 Cherry

4

4

 Date

Sample formulas

Use case Formula Explanation and Result
Return the specified value from the provided list of options. =CHOOSE(2,"Apple","Banana","Cherry","Date")

This formula works in the following manner:

  • CHOOSE(: This is the function itself.
  • 2: This is the index_num, specifying which value to return from the list that follows.
  • "Apple": This is `value1`.
  • "Banana": This is `value2`.
  • "Cherry": This is `value3`.
  • "Date": This is `value4`.
  • The function returns the 2nd value in the list.

Result: Banana.
Return the value of the specified cell from the set provided, based on the value provided in the specified cell. =CHOOSE(A1,B1,B2,B3,B4)

This formula works in the following manner:

  • CHOOSE(: This is the function itself.
  • A1: This is the index_num. The formula looks at `A1` and finds the value 1.
  • B1,B2,B3,B4: This is the list of values to choose from.
  • The formula returns the 1st value (`value1`) from the list, which is the value in `B1`.

Note that if the formula was =CHOOSE(A1,B4,B3,B2,B1), the result would be "Date" (B4, now the first cell in the set).

Result: Apple.
Return the third value from the provided list. =CHOOSE(3,10,20,30,40)

This formula works in the following manner:

  • CHOOSE(: This is the function itself.
  • 3: This is the index_num.
  • 10,20,30,40: This is the list of values.
  • The formula returns the 3rd value from the list.

Result: 30.
Return the fifth value from a list with only three options. =CHOOSE(5,"Red","Blue","Green")

This formula works in the following manner:

  • CHOOSE(: This is the function itself.
  • 5: This is the index_num.
  • "Red","Blue","Green": This is the list of values, which contains only 3 items.
  • Because the index_num (5) is outside the valid range of the list (1-3), the function returns an error.

Result: #VALUE!.

 

Notes

  • If index_num is less than 1 or greater than the number of values in the list, CHOOSE returns a #VALUE! error.
  • If index_num is a fraction, it is truncated to the nearest integer.
  • The CHOOSE function can be nested within other functions.
  • You can use CHOOSE to select one of several formulas to calculate.
  • CHOOSE does not support cell ranges. If you want to consider a range, use INDEX instead.

 

Tips

  • Use CHOOSE when you need to select a value or action based on a specific position in a list.
  • CHOOSE can be combined with other functions (for example, MATCH to create dynamic lookups, or in conjunction with other functions such as IF or INDEX to create more complex logical operations. It will not work with CONCATENATE.
  • CHOOSE is 1-indexed, meaning the first value corresponds to index 1, not 0.
  • For more complex conditional logic, consider using nested IF statements instead of CHOOSE.

 

Related functions

DescriptionUse this function to return a value from a list based on a specified position or index number.  Supported ...