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.
“
