Wednesday, March 13, 2013
Fortunately, there is a preferable alternative choice to using these awkward IF functions. The CHOOSE function is often a better selection, and it is much more versatile! The CHOOSE function is straightforward and simple to use and is best when combined with other Excel functions. It quite simply returns a value from a list based on a given Position (Index Number).
Here is the Uncomplicated Syntax:
CHOOSE( Index Number, Value1, Value2, ... Value n )
Some Basic Examples:
=CHOOSE(3, “North”, “South”, “Central”, “East”, “West”) returns Central
It also works with ranges:
=Sum(Choose(2, A1:A30, B1:B30, C1:C30) returns the Sum of B1:B30
You can, of course, link it to the value in a cell, making it much more Flexible. For example, you could link it to A1 which contains the Index Number.
If A1 contains the number 4, then =Choose(A1, “North”, “South”, “Central”, “East”, “West”) would return East.
The function can handle up to 29 options, which makes it a great choice in many real-life situations. The CHOOSE function is one of the Undiscovered Treasures in Excel. Give it a try sometime…