Nested if statement with over 7 conditions - VBA?
 

Hi,

I'm trying to do the following nested if statement but i know that the
maximum of statements allowed are 7.
How can i do this in VBA?
I'm not sure what code should be used.

See statement below:


=IF(B3="Portfolio","EAA",IF(B3="SEP","EAB",IF(B3=" ADP","EAC",IF(B3="ELP","EAD",IF(B3="GM
Cluster","EAE",IF(B3="EOL","EAF",IF(B3="DEC","EAG" ,IF(B3="HPPS","EAH",IF(B3="PROT","EAI",IF(B3="LEAD
cluster","EAJ",IF(B3="DSVC","EAK",IF(B3="HRST","EA L",IF(B3="M&A","EAM",IF(B3="ASA","EAN",IF(B3="STMT ","EAO",IF(B3="STRAT
cluster","EAP",IF(B3="MDS","EAQ",IF(B3="BCFP","EAR ",IF(B3="ASFP","EAS",IF(B3="MKT
cluster","EAT",IF(B3="FSSM","EAU",IF(B3="HR segment","EAV",IF(B3="Advocacy
Mailings","EAW",IF(B3="Business Development
Activity","EAX",""))))))))))))))))))))))))

Thanks.

Nested if statement with over 7 conditions - VBA?
 

No need to resort to a macro, VLOOKUP is your savour.

Create a table arrary of 2 columns

Portfolio EAA
Sep EAB
Etc

The enter the formula
=VLOOKUP(B3,D1:E10,2,FALSE)

where the array you typped is in columns D & E down to row 10 (or les if you
have less conditions.

Mike

"Isa" wrote:

Hi,

I'm trying to do the following nested if statement but i know that the
maximum of statements allowed are 7.
How can i do this in VBA?
I'm not sure what code should be used.

See statement below:


=IF(B3="Portfolio","EAA",IF(B3="SEP","EAB",IF(B3=" ADP","EAC",IF(B3="ELP","EAD",IF(B3="GM
Cluster","EAE",IF(B3="EOL","EAF",IF(B3="DEC","EAG" ,IF(B3="HPPS","EAH",IF(B3="PROT","EAI",IF(B3="LEAD
cluster","EAJ",IF(B3="DSVC","EAK",IF(B3="HRST","EA L",IF(B3="M&A","EAM",IF(B3="ASA","EAN",IF(B3="STMT ","EAO",IF(B3="STRAT
cluster","EAP",IF(B3="MDS","EAQ",IF(B3="BCFP","EAR ",IF(B3="ASFP","EAS",IF(B3="MKT
cluster","EAT",IF(B3="FSSM","EAU",IF(B3="HR segment","EAV",IF(B3="Advocacy
Mailings","EAW",IF(B3="Business Development
Activity","EAX",""))))))))))))))))))))))))

Thanks.

Nested if statement with over 7 conditions - VBA?
 

Thanks!
It's working now.

"Mike" wrote:

No need to resort to a macro, VLOOKUP is your savour.

Create a table arrary of 2 columns

Portfolio EAA
Sep EAB
Etc

The enter the formula
=VLOOKUP(B3,D1:E10,2,FALSE)

where the array you typped is in columns D & E down to row 10 (or les if you
have less conditions.

Mike

"Isa" wrote:

Hi,

I'm trying to do the following nested if statement but i know that the
maximum of statements allowed are 7.
How can i do this in VBA?
I'm not sure what code should be used.

See statement below:


=IF(B3="Portfolio","EAA",IF(B3="SEP","EAB",IF(B3=" ADP","EAC",IF(B3="ELP","EAD",IF(B3="GM
Cluster","EAE",IF(B3="EOL","EAF",IF(B3="DEC","EAG" ,IF(B3="HPPS","EAH",IF(B3="PROT","EAI",IF(B3="LEAD
cluster","EAJ",IF(B3="DSVC","EAK",IF(B3="HRST","EA L",IF(B3="M&A","EAM",IF(B3="ASA","EAN",IF(B3="STMT ","EAO",IF(B3="STRAT
cluster","EAP",IF(B3="MDS","EAQ",IF(B3="BCFP","EAR ",IF(B3="ASFP","EAS",IF(B3="MKT
cluster","EAT",IF(B3="FSSM","EAU",IF(B3="HR segment","EAV",IF(B3="Advocacy
Mailings","EAW",IF(B3="Business Development
Activity","EAX",""))))))))))))))))))))))))

Thanks.