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.
CHOOSE Function in Excel (Table of Contents)
CHOOSE Function in Excel
The CHOOSE function is used to get a value from a data list based on the position selected.
CHOOSE Formula in Excel
Explanation of CHOOSE Excel Function
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 set of numbers, cell references, cell references as arrays, text, formulas, or functions, such as:
- A5, A7, A9
The maximum number of values that can be provided is 254. The number of values provided should be ≥ index_value i.e the value to choose. If the index_value is 2, then there should be at least two elements: value1 and value2. Consider the following choose formula in excel and their output.
CHOOSE(2,”Lion”,”Tiger”,”Cheetah”) will give “Tiger”.
Similarly, CHOOSE(3,”Lion”,”Tiger”,”Cheetah”) will give “Cheetah”.
CHOOSE(4, “Lion”, “Tiger”, “Cheetah”)
will give an error
CHOOSE in Excel provides an alternative to functions such as VLOOKUP function and multiple IF functions. Choose excel function is mostly conjugated with other Excel functions to automate various tasks. Some of the applications for CHOOSE function in excel are listed below.
To group the data into a defined set of classes.
To get the month/day from a date.
To lookup a value corresponding to its identifier.
How to Use CHOOSE Function in Excel
Let’s take a few CHOOSE excel examples, before using the Choose function in Excel workbook:
CHOOSE Function in Excel Example #1
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.
CHOOSE Function in Excel Example #2
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 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 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.
CHOOSE Function in Excel Example #3
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 ramdomized 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.
CHOOSE Function in Excel Example #4
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 a desired value
CHOOSE Function in Excel Example #5
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 choose 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 retreive the marks of the student using the ID/name by replacing C6:C12 with D6:D12. This will give the output as 56.
CHOOSE Function in Excel Example #6
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 About Choose Function in Excel
- 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 –