Selecting a range from many using the CHOOSE function

A while ago I wrote about the how to convert long NESTED IF formulas to a simple formula with the CHOOSE function. Today we are going to see what else we can achieve by using the CHOOSE function.

We can fetch a range from a selection of ranges

How do we do that?

Let's see by looking at one example:

Say that we have 4 ranges, {A1:A10}, {B1:B10}, {C1:C10} and {D1:D10}, and that depending on a condition or a selection by the user we want to use one of them, for example, we want to sum up the range.

=SUM(CHOOSE(2, A1:A10, B1:B10,C1:C10,D1:D10))

If we look at that formula it tells us that we will SUM the result of a CHOOSE formula.

Let's look at the CHOOSE block.

=CHOOSE(2, A1:A10, B1:B10,C1:C10,D1:D10)

The syntax for the CHOOSE function is:

CHOOSE(index_num,value1,value2,...)

• Index_num (required):  specifies which value argument is selected. Index_num must be a number between 1 and 254, or a formula or reference to a cell containing a number between 1 and 254.
• value1 (required): First value to choose. Arguments can be numbers, cell references, defined names, formulas, functions, or text.
• value2 (optional): From the second value are optional and up to the value 254.

This formula would return the range B1:B10 as the index_num is 2, which indicates that the formula is returning the value2, which in this case is a range.

Once we have understood this, we can them sum it up, or calculate the average, or the minimum/maximum value. 

=SUM(CHOOSE(2, A1:A10, B1:B10,C1:C10,D1:D10))

=AVERAGE(CHOOSE(2, A1:A10, B1:B10,C1:C10,D1:D10))

=MIN(CHOOSE(2, A1:A10, B1:B10,C1:C10,D1:D10))

=MAX(CHOOSE(2, A1:A10, B1:B10,C1:C10,D1:D10))

Download the example file

To download a file containing examples of the theme we have just covered click on the following link:

CHOOSE examples

Subscribe and get all entries via email

You can do it by providing your email into "Join the newsletter" box in the right sidebar.

Or follow me via Social Media

Facebook, Twitter and Google+