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

## 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) = #VALUE``

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. He helps people reach the top in Excel.