Contents
Yesterday in the marathon 30 Excel functions in 30 days we figured out the details of our operating environment with the function INFO (INFORM) and found that she could no longer help us with memory issues. Neither ours, nor Excel’s memory!
On the fifth day of the marathon, we will study the function CHOOSE (CHOICE). This function belongs to the category References and arrays, it returns a value from a list of possible choices according to the numeric index. It is worth noting that in most cases it is better to choose another function, for example, INDEX (INDEX) and MATCH (MORE EXPOSED) or VLOOKUP (VPR). We’ll cover these features later in this marathon.
So, let’s turn to the information we have and examples on the function CHOOSE (CHOICE), let’s see it in action, and also note the weaknesses. If you have other tips and examples for this feature, please share them in the comments.
Function 05: CHOOSE
Function CHOOSE (SELECT) returns a value from a list, selecting it according to the numeric index.
How can you use the CHOOSE function?
Function CHOOSE (SELECT) can return the item in the list at a specific number, like this:
- By month number, return the fiscal quarter number.
- Based on the start date, calculate the date of the next Monday.
- By store number, show the amount of sales.
Syntax CHOOSE
Function CHOOSE (SELECT) has the following syntax:
CHOOSE(index_num,value1,value2,…)
ВЫБОР(номер_индекса;значение1;значение2;…)
- index_num (index_number) must be between 1 and 254 (or 1 through 29 in Excel 2003 and earlier).
- index_num (index_number) can be entered into a function as a number, a formula, or a reference to another cell.
- index_num (index_number) will be rounded down to the nearest integer.
- arguments value (value) can be numbers, cell references, named ranges, functions, or text.
Traps CHOOSE (CHOICE)
In Excel 2003 and earlier, the function CHOOSE (SELECT) supported only 29 arguments value (meaning).
It is much more convenient to search through a list on a worksheet than to enter all the elements in a formula. With functions VLOOKUP (VLOOKUP) or MATCH (MATCH) You can refer to lists of values located in Excel worksheets.
Example 1: Fiscal quarter by month number
Function CHOOSE (SELECT) works fine with simple lists of numbers as values. For example, if cell B2 contains the number of the month, the function CHOOSE (SELECT) can calculate which fiscal quarter it belongs to. In the following example, the fiscal year starts in July.
The formula lists 12 values corresponding to months 1 to 12. The fiscal year starts in July, so months 7, 8, and 9 fall into the first quarter. In the table below, you can see the fiscal quarter number under each month number.
In function CHOOSE (SELECT) Quarter number must be entered in the order in which they appear in the table. For example, in the list of function values CHOOSE (SELECT) in positions 7, 8 and 9 (July, August and September) should be number 1.
=CHOOSE(C2,3,3,3,4,4,4,1,1,1,2,2,2)
=ВЫБОР(C2;2;3;3;3;4;4;4;1;1;1;2;2;2)
Enter the number of the month in cell C2, and the function CHOOSE (SELECT) will calculate the fiscal quarter number in cell C3.
Example 2: Calculate the date of next Monday
Function CHOOSE (SELECT) can work in combination with the function WEEKDAY (DAYWEEK) to calculate future dates. For example, if you are a member of a club that meets every Monday evening, then by knowing today’s date, you can calculate the date for next Monday.
The figure below shows the serial numbers of each day of the week. Column H for each day of the week contains the number of days to add to the current date to get the next Monday. For example, you need to add only one day to Sunday. And if today is Monday, then there are still seven days until next Monday.
If the current date is in cell C2, then the formula in cell C3 uses the functions WEEKDAY (DAY) and CHOOSE (SELECT) to calculate the date of next Monday.
=C2+CHOOSE(WEEKDAY(C2),1,7,6,5,4,3,2)
=C2+ВЫБОР(ДЕНЬНЕД(C2);1;7;6;5;4;3;2)
Example 3: Show the amount of sales for the selected store
You can use the function CHOOSE (SELECT) in combination with other functions such as SUM (SUM). In this example, we will get the sales totals for a specific store by specifying its number in the function CHOOSE (SELECT) as an argument, as well as listing the data ranges for each store to calculate the totals.
In our example, the store number (101, 102, or 103) is entered in cell C2. To get an index value such as 1, 2, or 3 instead of 101, 102, or 103, use the formula: =C2-100.
The sales data for each store is in a separate column as shown below.
Inside a function SUM (SUM) function will be executed first CHOOSE (SELECT), which will return the desired summation range corresponding to the selected store.
=SUM(CHOOSE(C2-100,C7:C9,D7:D9,E7:E9))
=СУММ(ВЫБОР(C2-100;C7:C9;D7:D9;E7:E9))
This is an example of a situation where it is much more efficient to use other functions such as INDEX (INDEX) and MATCH (SEARCH). Later in our marathon, we will see how they work.