- 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
More Power of Choice
The power of the CHOOSE() function increases dramatically when you realize that the value list is not limited to containing literal values (numbers or text), but can also be cell references, defined names, or formulas.
With cell references, the function returns the value in the referenced cell. For example, look at this use of CHOOSE():
=CHOOSE(2, C1, C2, C3, C4)
This line returns the value that is in cell C2. Defined names work the same way. If the cell address or range name refers to a multicell range, the value in the top left cell of the range is returned.
The CHOOSE() function can also be used as an argument to other functions. It is useful in this way because it can return range references that are then used as the argument for the containing function. Look at this example:
=AVERAGE(CHOOSE(A1, C20:C40, D20:D40))
If cell A1 contains the value 1, the CHOOSE() function returns the range reference C20:C40, and the AVERAGE() function calculates the average of the values in that range. If cell A1 contains the value 2, on the other hand, CHOOSE() returns the range reference D20:D40, and the average is calculated on those values.