A to Z of Excel Functions: CHOOSE

The CHOOSE function is pretty cool.  It’s one of a few unusual functions within Excel that can be really useful when used in conjunction with others and can really add a lot of power to your spreadsheets.  It kind of falls into the category of conditional functions such as IF, AND and so on, but it has it’s own thing going on, and that’s what I think makes it cool.

Anyway, I’ll get on with it.  The CHOOSE function lets you choose an item from another list of items and display it as the result.  it looks like this:-

=CHOOSE(position, value 1, value 2….and so on)

The basic gist of CHOOSE is that the returned value of the function is the value in the position specified by the ‘position’ argument.  Let’s look at a really basic example:-

=CHOOSE(1,1,2,3,4)
=1

Here we have asked the function to return the first value it finds in the list of values 1, 2, 3 and 4.  Let’s try it again, but with that first argument set to 3 – what do you suppose the return value will be?

=CHOOSE(1,1,2,3,4)
=3

Yep.  You guessed it – really simple, yes?

Let’s have a look at another example to make sure we’re getting it:-

=CHOOSE(6,12,45,32,12,54,32,12)
=32

32 is the sixth value in the list of numbers starting with 12.

It’s that simple!  It can, however be made much more powerful, as the ‘position’ argument, and the values themselves can relate to cell references, which can give the function a lot more power.  Choose can even be used to return ranges which can be used in other functions such as SUM, COUNT or even VLOOKUP.  Check out the example below:-


In this example we have used the CHOOSE function to provide the range for a SUM function.  Depending on whether the user fills in 1, 2 or 3 as the position number, the CHOOSE function will return one of the ranges specified and this will be used as the argument for SUM.  Let’s look at this more closely and break the whole function down as it works.  Here we are wanting to add the values in the second column:-

=SUM(CHOOSE(2,A1:A3, B1:B3, C1:C3))
=SUM(B1:B3)
=9

Pretty cool, huh?  What makes it cooler is that the formula above can take the place of nested IF functions.  The function in the screenshot above, for example, does the same as:-

=IF(C6=1,SUM(A1:A3),IF(C6=2,SUM(B1:B3),IF(C6=3,SUM(C1:C3))))

As you can see, this has lots of potential for use in spreadsheets where you want to provide some element of decision-making functionality and, with a little imagination can be put to use to make some real formula magic happen.  I told you it was cool…  Keep coming back for more A to Z!

Leave a Reply

Your email address will not be published. Required fields are marked *