Excel CHOOSE Function – How To Use

The CHOOSE function in MS Excel returns a value from a list of values based on an index number. The list of values can also be specified as cell references. Choose function can support up to 254 values.

Although on its own CHOOSE function may not look much valuable, but when combined with other functions, it can work wonders. The function has served as a handy fallback option since its introduction to Office in 2003.

In this tutorial, we are going to see the basics of the excel CHOOSE function, and then gradually, we will progress on to more complicated scenarios.

Syntax

The syntax of the CHOOSE function is as follows:

Arguments:

index_num – The index number or the position of a value in the list that is to be displayed.
value1 – The first value in the list.
value2 – The second value in the list. Entering this argument is optional.

Important Characteristics of CHOOSE Function in Excel

• CHOOSE function can only support up to 254 values, this implies that the 'index_num' specified in the function must be a number between 1 and 254.
• If 'index_num' is an array, the array as a whole is processed as opposed to finding a match value by value.
• If the 'index num' in the function is out of this range i.e., less than 1 or greater than the number of values, the function returns a #VALUE! error.
• If the 'value' arguments are not enclosed in quotation marks, the function returns a #NAME? error.
• If the 'index_num' is a fraction, it will be rounded to the lowest integer eg., 2¾ will be rounded down to 2 instead of 3.
• The arguments can be numbers, cell references, defined names, formulas, functions, or text.

Examples of CHOOSE Function

Let's have a look at a simple example of the CHOOSE function before going into more complex scenarios.

Example 1 – Plain Vanilla Version of CHOOSE Function

In this case, we will pass a list of 3 countries to the CHOOSE function as values, and then analyze the result by changing the 'index_num' argument.

So, our first formula would be:

In the above formula, since the 'index_num' parameter is set to 1 so the formula returns the first country (i.e. United States) out of the supplied list of values.

Similarly, by changing the 'index_num' parameter in the following formulas we can get different results:

Example 2 – CHOOSE Function with SUM Function

In this example, we will try to see how to perform selective summation in excel by using the SUM and CHOOSE functions together. The good old, and most basic, SUM function needs no introduction and it does plainly as said; totals a series of numbers.

Now we will take you through selective summation.

Here, we have data from 3 classes each class is further divided into sections. Each section has 20 to 25 students. Our objective is to calculate the total number of students in any one of the given class (say class 9th).

In order to do this, we can use the CHOOSE and SUM functions as follows:

Now let's dissect this formula –

In the above formula, the CHOOSE function is supplied with cell ranges – B2:B5,C2:C5,D2:D5

• Cells "B2:B5" have the count of students of sections A, B, C, and D of Class 8.
• Cells "C2:C5" have the count of students of sections A, B, C, and D of Class 9.
• Cells "D2:D5" have the count of sections A, B, C, and D of Class 10.

Next, the 'index_num' parameter is set to 2, which points to the range C2:C5. This means that the result of the CHOOSE function in our case would be C2:C5. This cell range is passed to the SUM function and it performs a summation on the numbers present in the given range and returns the final result.

To bring up the total of a different class, we only have to change the 'index_num' in the formula. If the 'index_num' parameter is set to 1, the CHOOSE function would return the range B2:B5 (i.e. Class 8th). Similarly, when the 'index_num' parameter is set to 3, the CHOOSE function would return the range D2:D5 (i.e. Class 10th)

Example 3 – CHOOSE as an Alternative to Nested IF Statements

We all know, the role of an IF function is to return a value based on the stated condition. IF functions can be nested within each other to develop formulas for handling complex logic.

Functionally CHOOSE function is very similar to nested IF statements. However, CHOOSE makes it super easy to combine multiple conditions into one formula instead of having multiple IF functions nested within one another.

Let's try to understand this with an example.

Here we have a list of students along with their scores. Based on the scores we have to populate their ratings against their names.

Scores less than 70 are rated "Poor", scores between 71 to 80 are rated as "Average", scores between 81 to 90 are rated as "Good" and a score of 91 or more is rated as "Excellent".

We can have a CHOOSE function to do this:

Let's try to understand how this formula works –

On analyzing the formula, we can see the only thing that looks different here is the 'index_num' parameter.

Here instead of a usual numeric value, we have an expression : (B2>=0) + (B2>=71) + (B2>=81) + (B2>=91)

Actually, this expression is being evaluated to a numeric value, let's understand how.

B2 cell has a value of 77. So, the expression becomes : (77>=0) + (77>=71) + (77>=81) + (77>=91)

Evaluating each condition inside the expression:

• The condition 77 is greater than equal to 0 is TRUE.
• The condition 77 is greater than equal to 71 is TRUE.
• The condition 77 is greater than equal to 81 is FALSE.
• The condition 77 is greater than equal to 1 is FALSE.

So, the expression becomes : TRUE + TRUE + FALSE + FALSE

Since, Excel treats TRUE as 1 and FALSE as 0 so the expression can further be simplified to : 1 + 1 + 0 + 0. Which is equal to 2, hence in the first case index_num is considered as 2.

And "Average" is the value that corresponds to index 2 and hence is displayed as the result.

If the same logic was to be written using the nested IF functions, the formula would have been:

As you can see, there is 1 IF statement for each rating which means four IF statements in this case. We definitely know which function we prefer here.

Example 4 – CHOOSE Function to Generate Random Data

By now we are well familiar with the CHOOSE function's ability to compute data. Did you know you can generate random data in Excel, with CHOOSE function?

To do this we can make use of CHOOSE function and the RANDBETWEEN function.

The RANDBETWEEN (random between) function in Excel returns an integer randomly from the specified range of numbers.

In order to generate random data you need to use the CHOOSE and RANDBETWEEN functions together, let's see this with an example:

Beginning the first semester of preschool, all students are to be assigned to one of 4 houses (Gryffindor, Hufflepuff, Ravenclaw, and Slytherin). Yes, I am a Harry Potter fan 🙂

Our objective is to randomly assign students a house in excel.

For this, we can make use of the formula –

In the formula, the houses are assigned numbers 1 – 4. Gryffindor corresponds to 1, Hufflepuff corresponds to 2, Ravenclaw corresponds to 3 while Slytherin corresponds to 4.

The RANDBETWEEN function generates a random number (from 1 to 4) for student Riley (in this case, the number was "1"). Next, the CHOOSE function picks the relevant "value" argument and returns the result in the form of a house name (Gryffindor).

This is how the rest of column B is populated; the formula assigns a house to each student in line with the number randomly generated.

Example 5 – CHOOSE to Get Day Name from a Date

The CHOOSE function can also be used to get day names (i.e. Monday, Tuesday, etc.) from a date.

I agree that there are multiple ways to do this, for instance, you can make use of the TEXT function and retrieve this very easily like –

Although these formulas give you the desired result but do not allow you to have control over the result, for example instead of 'Saturday' you only want to show 'Sa'. For all such scenarios, CHOOSE function can be quite handy.

The formula would be –

In the above formula, the WEEKDAY function computes the given date and returns a number (1-7) corresponding to the day of the week. With default settings, Sunday corresponds to 1 and Saturday to 7. This number is then passed to the CHOOSE function as 'index_num' and the CHOOSE function picks and returns the value corresponding to the 'index_num'

Example 6 – CHOOSE to Get Month Name from a Date

Just like the day name example above, the month name (January, February, etc.) can also be derived from a date with the help of CHOOSE function. Similar arguments hold true for converting date to month.

To do this we can make use of the following formula –

In the above formula, we have used the CHOOSE function along with the MONTH function. The MONTH function accepts a date and returns a number from (1 – 12) corresponding to the month of the year. With default settings, January corresponds to 1 and December to 12. This number is then passed to the CHOOSE function as 'index_num' and the CHOOSE function picks and returns the value corresponding to the 'index_num'.

Example 7 – CHOOSE for Left VLOOKUP

We all are aware of the VLOOKUP function, it searches a vertically arranged table meaning the data must be in columnar form.

A huge limitation of the VLOOKUP function is that it only works rightward. This means that the lookup column must be the left-most column in your data set. This restriction can be bypassed with the help of the CHOOSE function which can perform the search leftward.

Let’s understand this with an example:

We have a dataset with 'Student Names' and their 'Scores'. Our objective is to find the score of a particular student named "Rolando".

To make a simple VLOOKUP work, we would have to arrange the table with the "Student Name" column first and the "Score" column second because the function only works rightward.

However, by making use of the CHOOSE function along with the VLOOKUP we can get this working. Following is the formula –

Let's try to understand the above formula by breaking it into two parts – the inner CHOOSE function and the outer VLOOKUP function.

First, let's talk about the CHOOSE function because this is where most of the heavy weight lifting is being done. Till now we have only seen the examples of the CHOOSE function with a single index number as the first argument but here we are making use of an array constant containing two numbers {1,2}.

This is essentially equivalent to asking the CHOOSE function to return two values instead of 1. The next important part of the function is the sequence of ranges provided in the values parameter i.e. B2:B11, A2:A11. Notice that how cleverly we are providing the ranges in the revered order.

These two things enable the CHOOSE function to return an array with two columns where the first column corresponds to the "Student Names" while the second one corresponds to their "Scores" (i.e. swapping the original column positions)

Now let's talk about the second part – the VLOOKUP function. The VLOOKUP function is pretty straightforward, it searches for the value at D6 in the range provided by the CHOOSE function (the range formed by swapping the column positions), requests for the values in the 2nd column of the given range with an exact match.

And we get the expected result. Hurray!

That’ll be all from the CHOOSE function. Here’s hope we gave you a good idea on how to choose with the CHOOSE. We’ll be coming right back with another Excel-lent function soon. Ciao!