Here is a neat way to perform multiple IF statements without using a single IF. Due to Excel’s limitation to nest just 7 IF’s sometimes you have no choice but to use an alternative.
By using a combination of Excel’s CHOOSE and MATCH formulas you can create what can only be described as MAGIC!
I’ll show you a simple example and then let you go and create your own magic. I’ll break it down into the 2 parts and then join it together at the end…
You have a list of 8 names. 4 are men’s names and 4 are Women’s. You’d like to have a formula that calculates the gender based on the name…
First you need to create a MATCH formula like this..
=MATCH(A2,{"Dave","John","Mike","Bob","Sally","Jane","Lisa","Liz"},0)
As you can see in the picture below the match formula returns the matching position that your name appears in the array of names between the curly brackets.
Now you need a CHOOSE formula to take that number and return the value in the corresponding position after the first comma…
=CHOOSE(B2,"Man","Man","Man","Man","Woman","Woman","Woman","Woman")
You’ll see that the value in cell B2 is 1. And the First position after the comma is “Man”. So the CHOOSE chooses “Man” ! Good innit?
And by joining the 2 formulas together like so…
=CHOOSE(MATCH(A2,{"Dave","John","Mike","Bob","Sally","Jane","Lisa","Liz"},0),"Man","Man","Man","Man","Woman","Woman","Woman","Woman")
… means that in one magic step you have your multiple IF statement without any complicated IF’s
We have another post based on the fantastic CHOOSE function here. Please check it out and maybe even place a comment below if it’s been helpful.