CHOOSE Function in Excel
Choose function in excel is used to fetch a value from a given range of data when we provide it an index number and the starting point of values, there are two mandatory arguments for this function and the index number and the first value is mandatory while the other values are optional to mention.
index_num: the position of the object to choose from. It is a number between 1 and 254. This can be a numerical value, a cell reference, or a function giving a numerical value such as:
value1, [value2], [value3],… : the data list from which one of the item is selected. At least one value should be provided. These can be a set of numbers, cell references, cell references as arrays, text, formulas, or functions, such as:
- “Sunday”,” Monday”,” Tuesday”
- A5, A7, A9
How to Use CHOOSE Function in Excel? (with Examples)
Let’s take a few CHOOSE excel examples, before using the Choose function in Excel workbook:
Suppose you have 6 data points- 2,3,10,24,8,11 and you want to choose the 4th element, your function will be à CHOOSE(4, 2,3,10,24,8,11).
This will return the output as 3. In place of 4 as index_value, if you select A4, it will return 10. It is because A4 corresponds to 3 and the 3rd value in the dataset is A5 which is 10.
You may also choose from an array of values instead of just values. Suppose you have a list of colors, a list of flowers and a list of numbers in three columns.
From this, you may want to choose a third value and you use the Choose formula in excel as:
Here, the third value is a list of values (D4:D8 => 8,11,9,11,14,90). The output of the above syntax is also a list of values D4:D8.
However, in a single cell, it returns only a single value as an output from this list. This selection is not random and depends on the position of the cell you want your answer on. As in the above image, in F4 the output of CHOOSE(3, B4:B9,C4:C9,D4:D9) is 8 (=D4). In F5, the same input will give you 11 (=D5) as output and so on.
The above command can be combined with other commands such as sum, average, mean, etc. For example, SUM(CHOOSE(3, B4:B9,C4:C9,D4:D9)) will give the sum of the 3rd set of values (D4:D9) as shown below.
Sometimes, a random grouping of the data is required, such as in the case of clinical studies, machine learning as a test and train, etc. The CHOOSE function in Excel can also be used to group the data randomly. The below example explains how to group any data randomly into different classes.
Suppose you have a list of 20 subjects and you want to group the data into Class A, B, C, and D.
The syntax for randomly selecting the groups A,B,C, and D is given as:
In the above command, RANDBETWEEN(1,4) is an Excel function to randomly select a value between 1 to 4. Here, it is used as an index value. So, the index value will be randomized from 1 to 4. If the index value is 1, it will give A; if it is 2, it will return B and so on.
Similarly, you can classify the data into any number of classes by exploiting the RANDBETWEEN function of Excel.
CHOOSE Function can also be used to select the day/month from a given data. The below example explains how to extract and return the month from a date.
Suppose you have a list of dates in the 1st column A3:A14 as shown below,
and you want to extract the month for the 2nd value (A4 here). The choose formula in excel will be given as
The above syntax returns Feb.
CHOOSE Excel Function can be linked to other function such as VLOOKUP to obtain the desired value
Suppose you have a list of Student ID (B6:B12), their name (C6:C12) and marks (D6:D12) as shown below:
You can find the name of the student using the corresponding ID. The chosen formula in excel for doing this would be:
If the ID we want to look at is in F6, you can replace it with the cell reference as shown below.
The output for this is “Manish”.
The same can also be done to retrieve the marks of the student using the ID/name by replacing C6:C12 with D6:D12. This will give the output as 56.
Suppose you have three cases 1,2,3 in which the growth is different for each case. Now, you want that you select the case and you get the current value in return. The current amount will be Principal amount + (Principal amount*growth).
The choose formula in excel will be:
which returns 1,02,000 if the case is Case 1. The above command is a slight extension of the command used in Example 5.
Things to Remember
- The index_value can vary between 1 to 254.
- The number of values to choose from can also vary from 1 to 254
- The number of values provided should be ≥ index_value i.e the value to choose. If the index_value > the number of values provided to choose from, Excel gives an error #VALUE
- The index_value should correspond to a numerical value only, otherwise, it will give an error.
This has been a guide to CHOOSE Function in Excel. Here we discuss the CHOOSE Excel Formula and how to use it along with practical examples and downloadable excel templates. You can have a look at other articles on excel functions –