Vergel’s Excel and Visual Basic Blog

The CHOOSE() Function

March 14th, 2007

The CHOOSE() function allows one to select 1 to 29 values based on an index number. For example,

=CHOOSE(index_num, value1, value2, …)

If value1 to value12 are months in a year, CHOOSE will return one of the months when index_num is a number between 1 and 12. If index_num is 1, CHOOSE will return the first value; if it is 2, CHOOSE will return the second value; and so on. If index_num is less than 1 or greater the number of values in the list, CHOOSE will return the #VALUE! error value. If index_num is a fraction, it will be rounded off to the lowest integer value.

Index_num can also be an array. In which case, every value is evaluated when CHOOSE is evaluated. The value arguments can be range references or single values. A formula like this:

=MAX(CHOOSE(3, A1:A4,B1:B4,C1:C4))

will evaluate to this:

=MAX(C1:C4)

and return the maximum value in C1:C4.

Some examples of using the CHOOSE() function:

Download the sample workbook here .

Posted in Excel, Functions | No Comments