Hello all. In this section let’s explore CHOOSE function in excel. CHOOSE function helps to choose a value from list of values based on specified index number.
=choose(index_num, value1, [value2],..)
Let’s start off with a simple example. In the below sheet, without any base data, we just use choose function to return 3rd value within the value argument
In the above formula, the function picks the 3rd value and returns the same. Next, let’s create a list and use the same.
Here, we have added cell reference in place of values. The function again checks the 4th item in the values we entered, in our case A5 and returns brown.
In the below example, we have added values A2 to A8 – but not in the right order as the list. As per the list, 4th value is brown – however, we have added the values as A2,A4,A5,A3,A8,A7,A6. Now, the function will check the 4th place which is A3 and return Blue.
Point here is – irrespective of what we have in the list, the function just picks the order of the list argument we specify as values.
Using Array within Choose function
You might wonder why did we use values like A2,A3, etc instead of A2:A8. Choose function doesn’t retrieve an item within the array, instead it would just list out the array when the index_num is 1. Let’s look at the below example
In the example, we gave the value as a range (A4:A8) and index_num as “1” – the function returned all the values between A4 and A8. If we give “2” as the index_num, then choose function would return “#Value!” error. Remember – choose doesn’t retrieve any item
If the array has any empty cells, the function would return a “0”.
Points to Note
- Choose function can accommodate upto 254 values
- As choose function cannot retrieve an item from a range, Index function is a good alternate for large datasets.
I hope you like this section on the Choose function. Thanks for reading!