If Statement Alternative [SOLVED]

[skelly90]

Hi


I've created the below If Statement which shows an employees sickness entitlement based on their length of service with a company. At the moment it works perfectly but I have more arguments I would like to add to it (around 6-10) and I'm aware that an if statement won't work with more than 8 arguement in it. Is there any other way to get the same results whilst including the additional arguments?


=IF(E2="EMA - M",LOOKUP(K2,'RAD Formulae'!$B$16:$C$22),IF(E2="EMA - S",LOOKUP(K2,'RAD Formulae'!$B$25:$C$30),IF(E2="DBP",LOOKUP(K2,'RAD Formulae'!$B$43:$C$45),IF(E2="TTS",LOOKUP(K2,'RAD Formulae'!$B$49:$C$51),IF(E2="N/C",LOOKUP(K2,'RAD Formulae'!$B$56:$C$58),IF(E2="G4",LOOKUP(K2,'RAD Formulae'!$B$62:$C$63)))))))


Column E = Condition cell

Colimn K = Length of service

 

[NARAYANK991]

Hi ,


The formula you have at present , can be rewritten as :


=LOOKUP(K2,CHOOSE(MATCH(E2,Condition_Types,0),'RAD Formulae'!$B$16:$C$22,'RAD Formulae'!$B$25:$C$30,'RAD Formulae'!$B$43:$C$45,'RAD Formulae'!$B$49:$C$51,'RAD Formulae'!$B$56:$C$58,'RAD Formulae'!$B$62:$C$63))


All you have to do is set up a list of all the Condition_Types ; this will be a single-column range containing the following :

[pre]

EMA - M
EMA - S
DBP
TTS
N/C
G4

[/pre]
You can add many more to this ; just add the corresponding LOOKUP ranges in the above formula.


Narayan

 

[skelly90]

Hi,

Sorry I couldn't get back to you yesterday.

Works perfectly, You've saved me alot of hassle; THANK YOU!!