The CHOOSE formula. When IF Formulas get too complex

If you are new to the CHOOSE formula, it could change your life! It’s so simply to use and can be a better solution to really long IF formulas that have to check a condition before returning a result.

I came across it due to the limitations in excel on nesting IFs. You can nest 7 IF’s and that’s it.

The formula below is an example of nesting 7 IFs. Basically where the green bits are true do the red bit and where the green bit is not true, do another IF.

=IF(A1=1,"a",IF(A1=2,"b",IF(A1=3,"c",IF(A1=4,"d",IF(A1=5,"e",IF(A1=6,"f",IF(A1=7,"g",IF(A1=8,"h",""))))))))

This is just a series of simple IF formulas but it can get very complex very quickly if you want to perform other complex calculations based on your results. Paste it into a cell in excel (Not A1), change the value in A1 and see it it action.

Do It With Choose

Below is the CHOOSE formula to replicate the complicated IF above. Paste it into another cell in excel to see it work:

=CHOOSE(A1,"a","b","c","d","e","f","g","h")

In this formula the CHOOSE is taking your value in cell A1 (lets say its 5) and then returning the 5th result (the red bits, “e”).

Not bad hey? However the real power of CHOOSE is that you can put more than 7 results in it…

=CHOOSE(A1,"a","b","c","d","e","f","g","h","I","j","k","l","m","n","o","p")

… and you can return formulas too…

=CHOOSE(A1,VLOOKUP(A1,A1,1,0),NA(),SUMIF(A1,A1,A1))

One thing to note is that the first argument in the choose has to be a number so that it knows what position to go to. This means that you may need to create a table to match the choose position to the thing you are testing.

For example, if you want to do different things when your value is a,b or c, d,e or f or g,h or i you could have a table like the one below.

choose_formula

Then use a vlookup to find the CHOOSE position like so:

=CHOOSE(VLOOKUP(C1,A1:B10,2,0),"first formula","second formula","third formula")

In the illustration below your choose input is the in cell C1 (an “e”). The vlookup for “e” returns the number 2. The second result in your CHOOSE is “second formula”

choose_formula_2

Hey presto! Sounds tricky but experiment with it, have a play and see how it can work for you. CHOOSE has been a savior for us in particular circumstances so we hope it can help you out too.

No ratings yet.

Please rate this

0 comments on “The CHOOSE formula. When IF Formulas get too complexAdd yours →

Leave a Reply

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