Excel CHOOSE Function
The Excel CHOOSE function returns a value from a list using a given position or index. For example, CHOOSE(2,"red","blue","green") returns "blue", since blue is the 2nd value listed after the index number. The values provided to CHOOSE can include references.
- index_num - The value to choose. A number between 1 and 254.
- value1 - The first value from which to choose.
- value2 - [optional] The second value from which to choose.
The Excel CHOOSE function returns a value from a list using a given position or index. The values provided to CHOOSE can include references. However, CHOOSE will not retrieve an item from inside range or array constant provided as a value. Choose can handle up to 254 values but INDEX, or INDEX with MATCH are better approaches for large value sets.
The formulas below use CHOOSE to return the 2nd and 3rd values from a list:
Above, "blue" is the second value, and "green" is the third value. In the example shown in the screenshot, the formula in cell B5 is:
CHOOSE(B5,"red","blue","green") // returns "red"
=CHOOSE(2,A1:A3) // returns #VALUE
This happens because the index number is out of range. In this case, the required syntax is:
To retrieve the nth item from a range, use INDEX and MATCH.