Multiple IF Formula! The MAGIC Version

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…

choose_match_1

 

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.

choose_match_2

 

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?

choose_match_3

 

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

choose_match_4

 

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.

No ratings yet.

Please rate this