- 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.
How to use
The CHOOSE function returns a value from a list using a given position or index. The values provided to CHOOSE can be hard-coded constants or cell references. The first argument for the CHOOSE function is index_num. This is a number that refers to subsequent values by index or position. The next arguments, value1, value2, value3, etc. are the values from which to choose from. Choose can handle up to 254 values. However, CHOOSE will not retrieve an item from inside range or array constant provided as a value. For larger sets of data in a table or range, INDEX and MATCH is a better way to retrieve a value based on position.
The formulas below use CHOOSE to return the 2nd and 3rd values from a list:
CHOOSE(2,"red","blue","green") // returns "blue" CHOOSE(3,"red","blue","green") // returns "green"
Above, "blue" is the second value, and "green" is the third value. In the example shown in the screenshot, the formula in cell C5 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. CHOOSE can be used to provide a variable table to a function like VLOOKUP:
=VLOOKUP(value,CHOOSE(index_num,rng1,rng2),2,0) // variable table