The Excel CHOOSE function allows you to select a value from a larger set of options by adding an index of the value you want to retrieve.
For example, the =CHOOSE(3,”apple”,”melon”,”banana”) will return “banana”, since banana is the 3rd value listed based on the index number. There are two restrictions: You can use only positive integer values as a first argument. Furthermore, the CHOOSE function supports selecting values (index numbers) between 1 and 254.
Syntax
=CHOOSE (index_num, value1, [value2], ...)Arguments
The function uses at least two required and optional arguments:
- index_num – The selected value, index_num, should be between 1 and 254.
- value1 – The first variable in the list you can choose.
- value2 – [optional] The second value to select.
CHOOSE function examples
Use the following formulas to return the first, second, and third values from a predefined list:
=CHOOSE(1, “apple”, “melon”, “banana”, “lemon”) //returns “apple”
=CHOOSE(2, “apple”, “melon”, “banana”, “lemon”) //returns “melon”
=CHOOSE(3, “apple”, “melon”, “banana”, “lemon”) //returns “lemon”
The function is not able to manage ranges. If you add a range as a second parameter, Excel will return a #VALUE error.
=CHOOSE(3,B1:B3) = #VALUEThe formula returns error, because the proper syntax is =CHOOSE(3,B1, B2, B3)
CHOOSE work great if the list is short. However, if you have a large data set, we recommend using the much faster and more versatile XLOOKUP function to get the selected value based on a position.
=XLOOKUP(300,A1:A300,B1:B300) will return the 300. record from column B.
Don’t forget the most important disadvantages of using the CHOOSE function: You have to use hardcoded values, like {“apple”, ”banana”, ”melon”}. You can fill the array manually using text values so that the formula can be long.
Workaround with CHOOSEROWS user-defined function
The CHOOSEROWS function handle ranges without any trouble. The first argument is an array, where you find the nth value.
=CHOOSEROWS(C3:C5,3) //returns "banana"
The result is the same, but the formula is simple. Alternatively, you can use an OFFSET-based formula.
