Excel Choose function is a powerful tool designed to simplify the process of selecting specific values from a range of options. The function offers an efficient way to return a value from a list based on its index number. It works by providing the user with different values and asking them to select one, two, or more items depending on their preference. Excel then uses this selection as input for other formulas, which helps automate data processing tasks in a professional workspace. With Choose function, you can easily select values from pre-defined ranges like months of the year or days of the week, and then use those selected options in other computations. This makes it easier to compare data, create charts and graphs, or perform any number of advanced calculations required within business settings. Ultimately, the Excel Choose function provides an invaluable time-saving solution that contributes significantly to streamlining professional workflows and increasing productivity.
The Excel CHOOSE function uses its index number, the first argument of its syntax, to return a value from the list of value arguments. In addition, You can use the CHOOSE function to select one of up to 254 values based on the index number.
For example, =CHOOSE(3,”red”,”green”,”blue”) returns “blue”, since “blue” is the 3rd value listed after the index number. Another example, =CHOOSE(2,A1,A2,A3) returns the value in cell A2 since it is the 2nd value listed after the index number.
Syntax of Choose Function
|
1 |
=CHOOSE (index_num, value1, [value2], ...) |
Arguments
- index_num – Specifies which value to choose. However, this value accepts a number between 1 and 254, a formula, or a reference to a cell containing a number between 1 and 254.
- value1 – The first value from which to choose.
- value2, value2, etc. – [optional] The values from which to choose based on the index number. The arguments can be numbers, cell references, defined names, formulas, functions, or text.
Remarks
- If index_num is less than 1 or greater than the number of the last value in the list, the CHOOSE function returns #VALUE! error.
- If index_num is a fraction, it is truncated to the lowest integer before returning the value.
- The CHOOSE Function, however, does not retrieve values from a range or array constant.
Examples of Excel Choose Function
The below formula returns the 2nd value from its list:
|
1 |
=CHOOSE(2,"red","green","blue") // returns "green" |
The below formula returns #VALUE! Error since the index number is greater than the number of the last value in the list. In addition, it also returns #VALUE error if the index number is less than 1:
|
1 |
=CHOOSE(5,"red","green","blue") // returns #VALUE |
The below CHOOSE function returns #VALUE Error because the function does not retrieve values from a range or array constant:
|
1 |
=CHOOSE(2,A1:A3) // returns #VALUE |
For more details, please refer to Microsoft Documentation about the Choose Function.
For more Excel functions, please refer to our excel posts.