CHOOSE Function

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.


=CHOOSE (index_num, value1, [value2], ...)


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”
choose function example

The function is not able to manage ranges. If you add a range as a second parameter, Excel will return a #VALUE error.


The 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.

Istvan Vozar

Istvan is the co-founder of Visual Analytics Ltd. He writes blog posts and helps people to reach the top in Excel.