Multiple IF statement - Alternative method

[mrsushi]

HI, I have an IF statement which cannot add more if statements because it uses more levels of nesting than are allowed in the current file format.

Is there an alternative way of writing the below formula?

Many thanks

=IF(G3="ORDINARY SHARE","YES",IF(G3="AUTH UNIT TRUST-CAPITAL UNITS","YES",IF(G3="RIGHTS","YES",IF(G3="AUTH UNIT TST-INC(CGT EXEMPT)","YES",IF(G3="ICAV Capital Units","YES",IF(G3="ADR","YES",IF(G3="REIT","YES",IF(G3="OEIC","YES","NO"))))))))

 

[Fluff]

How about

=IF(OR(G3={"ORDINARY SHARE","AUTH UNIT TRUST-CAPITAL UNITS","RIGHTS","AUTH UNIT TST-INC(CGT EXEMPT)","ICAV Capital Units","ADR","REIT","OEIC"}),"YES","NO")

 

[chipsworld]

You could try an OR statement...

If(OR(g3-"xxx",G3="xxxx", etc, etc),"YES","NO")

 

[mrsushi]

You could try an OR statement...

If(OR(g3-"xxx",G3="xxxx", etc, etc),"YES","NO")

Thank you Chipsworld.

 

[mrsushi]

How about

=IF(OR(G3={"ORDINARY SHARE","AUTH UNIT TRUST-CAPITAL UNITS","RIGHTS","AUTH UNIT TST-INC(CGT EXEMPT)","ICAV Capital Units","ADR","REIT","OEIC"}),"YES","NO")

Hi Fluff,

Thank you for the posting. I think its missing a parenthesis....can you advise please?

 

[RoryA]

No, it's correct as posted.

 

[chipsworld]

Fluff,
Can Or, And statements be stacked the same in VBA? Contained in brackets like you did above? That would surely save me some typing! Lol

 

[Fluff]

Depends on what exactly you mean, but as that has nothing to do with this thread you would need to start your own.

 

[mrsushi]

Thank you Chipsworld.

If I wanted a change only one of the criteria ie N8="Manual" to produce "Manually Overwritten" instead of the " "Ladder used", how would the below be changed please?

=IF(OR(N8="JPM EM-BIG Price", N8="High Yield",N8="BarCap-GCF",N8="Manual",N8="Bloomberg BVAL(BVIC)"),N8&" Ladder used","")

 

[Fluff]

How about

=IF(OR(N8={"JPM EM-BIG Price","High Yield","BarCap-GCF","Bloomberg BVAL(BVIC)"}),N8&" Ladder used",IF(N8="Manual","Manually Overwritten",""))