Excel Tip of the Week #96 - CHOOSE

Hello and welcome back to the Excel Tip of the Week.  This week, we have a Creator post in which we look at the pick-a-process function, CHOOSE.  This is a short Tip because it's long on examples, which you can find in the file attached at the bottom of this page.

What CHOOSE does: CHOOSE can have a range of different values based on an index number.  Each value could be fixed or could be a calculation.

How to write a CHOOSE formula:
     =CHOOSE(index number,value 1,value 2, …)
Index number – A reference to a cell contains the currently chosen option.  This could be a straight number, or a MATCH function could be used if a title is to be picked from a list.
Value 1 – The value or calculation that will be used if the index number is 1.
Value 2 – The value or calculation that will be used if the index is number is 2.
(and so on – each value entry must be listed separately)

Uses of this function:
 - Selecting between multiple processes
 - Choosing an input according to a scenario
 - Choosing e.g. a depreciation method to be used from a list of options
 - Note that CHOOSE can be used to choose between ranges, if the result is then used in an appropriate formula – e.g. =SUM(CHOOSE(2,A1:A10,B1:B10,C1:C10)) would add the values from the range B1:B10.

Again, do check out the attached file for some examples and a practise task.

Previous post 
TOTW index
Next post

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.

CHOOSE.xlsx
Anonymous
  • Even better! Thanks for that. It's funny, I can manage the most complex functions, VBA all kinds of whizzy stuff in Excel, but sometimes useful applications of the simpler functions escape me.

  • You're welcome Stephen!


    If you're looking to convert the way you discuss, try this:


    =MOD(MONTH(A1)-4,12)+1

  • Thanks for this David. I've never really thought about uses for this function, but this short article and the examples have prompted some thought.


    Passing ranges to other functions is not something I had realised was possible with CHOOSE. I can see that this would be really handy.


    An immediate use I can see for the function itself was prompted by your "quarter from date" example. I often convert months to financial year periods - we run April to March. =CHOOSE(MONTH(A1),10,11,12,1,2,3,4,5,6,7,8,9) is much simpler, and probably more efficient, than my usual IF statement approach.