Be CHOOSY!

There are countless undiscovered treasures in Excel, so it pays to explore a bit and be Choosy.  For instance, Nested IF functions can be very powerful, but they have limitations (the maximum nested functions is 7 in older versions of Excel) and they can be a bit difficult and cumbersome.  That is why it is nice to have a Choice!

Having a Choice is almost always a Good Thing, and fortunately, there is a preferable alternative to using the occasionally awkward IF functions. The CHOOSE function is often a better selection, as it is considerably more versatile! The CHOOSE function is remarkably straightforward and simple to use, and is best when combined with other Excel functions. It quite humbly returns a value from a list based on a given Position (Index Number).

Here is the Unsophisticated but Valuable Syntax:

CHOOSE( Index Number, Value1, Value2, ... Value n )

Now for 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 Cell A1 which contains the Index Number.

If A1 contains the number 4, then =Choose(A1, “North”, “South”, “Central”, “East”, “West”) would quite obviously 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. Be Choosy and give it a try sometime!