Choose CHOOSE!

Whether you are at a restaurant or working on a spreadsheet, having Choices is almost always a Good Thing.   As we discussed in this blog a couple of years ago, it can be accomplished in Excel by using Nested IF Functions, but they have limitations (e.g. there is a maximum 7 functions allowed in most versions of Excel) and they are cumbersome.

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…