Exploring Excel's Functions, Part 5: The Power of Choice
- Using CHOOSE() with Dates
- More Power of Choice
- Going Beyond 29 Choices
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
The CHOOSE() function does just what its name implies—it chooses something based on data in the worksheet. Let’s look at the syntax:
CHOOSE(index, val1, val2,...., val29)
The index argument is a numeric value in the range 1–29, inclusive, or more often a reference to a worksheet cell containing a value in that range. The val arguments and what is chosen, and there can be up to 29 of them. The function simply returns the val item that corresponds to the index value. For example, the following function returns the text value "Three" (the third item in the list of values):
=CHOOSE(3, "One", "Two", "Three, "Four")
Be aware that if the value of index is greater than the number of val arguments, the function returns an error.
Using CHOOSE() with Dates
One way I have used the CHOOSE() function is to get the month, as a text value, from an Excel date. You must also use the MONTH() function, which returns the number value, 1–12, of the month for a date. For example, if cell A3 contains a date, the function returns 1 if the date is in January, 2 if in February, and so on:
=MONTH(A3)
To get the month name as text, we add CHOOSE() as follows:
=CHOOSE(MONTH(A3), "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
An example is shown in Figure 1.
)
Figure 1 Using the CHOOSE() function to extract month names