CHOOSE
The CHOOSE function returns a value from the list based on a specified index.
This function allows you to dynamically select data based on conditions or values in other cells.
Syntax
CHOOSE(index, value1, [value2,...])
| Argument | Description | Permitted values |
|---|---|---|
| index | Number of the value to be selected from the list | Integer (1, 2, 3, and so forth) or a reference to a cell with a number |
| value1 | First value in the list can contain a number, a cell reference, or a text value | Any value (number, text, and so forth) |
| [value2, ...] | (optional) Additional values in the list | Any value (number, text, and so forth) |
Examples of use
Use with text
=CHOOSE(A1, "First", "Second", "Third")
If the value in cell A1 is 1, the function returns “First”; if 2, it returns ‘Second’; if 3, it returns “Third.” If the value in cell A1 is not in the range [1...3], the function will return the #VALUE! error.
Use with numbers
=CHOOSE(B1, 100, 200, 300, 400)
If B1 is 1, the function will return 100; if 2, it will return 200; if 3, it will return 300; if 4, it will return 400. If the value in cell B1 is not in the range [1...4], the function will return the #VALUE! error.
Combining with other functions
=CHOOSE(COUNT(A1:A5), "One value", "Two values", "Three values", "More than three values")
This formula counts the number of non-empty cells in the range A1:A5 and returns the corresponding message depending on the number of values.
Notes
- If the index argument is less than 1 or greater than the number of values listed, the function returns the #VALUE! error.
- The maximum number of values is 254.
- Values can be of different data types (text, numbers, dates, formulas).
- This function is an alternative to the IF function for multiple conditions with numerical indexes.