The CHOOSE option in Excel

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

Readers suggest an alternative to nested IF – the Choose() function

After David Goodmanson’s features on IF and nested IF’s we’ve received many messages from readers suggesting another alternative – CHOOSE()

The Choose function in Excel and VBA lets you choose from a list of options based on an integer value:

=Choose(2, “First Birthday”,”Second Birthday”, “Third Birthday”)

This example will return ‘Second Birthday’.


Choose()

The full syntax is:

CHOOSE(index_num,value1,value2,…)



  • Index_num can be any number or formula.

    • It can also be an array

  • Only values from 1 to 254 are permitted

    • Anything outside that range will get a #VALUE! error

  • If index_num has a fraction it is rounded down – eg 2.25, 2.5 and 2.9999 are all evaluated as 2 and return ‘value2’
  • The valueN parameters are usually values or strings

    • But they can be a cell range

Steve K has a good example:

The complex nested If example

=IF(B3=5, “Boom”, IF(B3=4, “Recovery”, IF(B3=3, “Turning Point”, 
  IF(B3=2, “Recession”, IF(B3=1, “Depression”)))))

can be simplified with choose

=Choose(B3,”Depression”, “Recession”, “Turning Point”, 
  “Recovery”, “Boom”)

 

Nigel B goes into some detail:

Regarding “NESTED IF’S AND ALTERNATIVES”, another alternative is CHOOSE(), which can be made to be quite versatile. E.g. in your example, cell C3 would have

=CHOOSE(B3,”Depression”,”Recession”,”Turning Point”,
  “Recovery”,”Boom”)

 

But this only works if the value, as in your example, is numeric and sequential (or at least evenly spaced). Note that the example assumes that it starts at 1, but, of course, it doesn’t need to, because you can use B3-1, for example, if B3 starts at 2. Nor does it need to be sequential – it only needs to be evenly spaced, because you can use B3/2, for example, if the values of B3 are 2,4,6,8,… etc.

However, if the value in B3 is not numeric, or is not evenly spaced, then you can replace the reference to B3 in the above example with

MATCH(B3,{,,…},0)

(note the curly brackets surrounding the fixed array of values). For example, if you want to have “Depression” for “Sad”, “Recession” for “Worried”, “Turning Point” for “Relieved”, “Recovery” for “Happy”, and “Boom” for “Ecstatic”, the formula would be

=CHOOSE(MATCH(B3,{“Sad”,”Worried”,”Relieved”,”Happy”,
  “Ecstatic”},0),”Depression”,”Recession”,”Turning Point”,
  “Recovery”,”Boom”) 

Alternatively, if you don’t want to consume spreadsheet space with your lookup table, and want to keep the pairs of translations together, you can always “hardwire” your array inside VLOOKUP:

=VLOOKUP(B3,{“Sad”,”Depression”;”Worried”,”Recession”;”Relieved”,
  “Turning Point”;”Happy”,”Recovery”;”Ecstatic”,”Boom”},2,0)

CHOOSE is a good choice when you have integer options starting with 1 – as in our simple example. As Nigel points out you can ‘shift’ the values so they start with 1 (though that might confuse later editors of the worksheet). What’s important is that the index values are evenly spaced.

But if your choices aren’t so orderly then CHOOSE is of less help.

Our main purpose was (and will be in follow on articles) to show the variety of options available in Excel to solve a single problem. There are trade-off’s between speed/efficiency of a worksheet and future readability. For example the VBA code alternatives we gave aren’t as fast as IF’s or Choose but can be much easier for later developers to read and update.  To say nothing of the ‘Mere Mortals’ or Excel beginners that the article is intended for.

As John K. said:

I have purchased ebooks and found them much better than others that simply state, “to do X check box Y.”  You explain the logic of why you would want to do or not do X.

subs profile e1563205311409 - The CHOOSE option in Excel
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address