Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
=if(PG='M'
or PG='P others'
or PG='NE'
or PG='PE'
or PG='HC'
or PG='EC'
or PG='FOOD'
or PG='BIO'
or PG='DIS
or PG='SB'
or PG='Ca'
or PG='PH
or PG='PRO'
or PG='SEYO',dual('Others',26),
if(PG='CP',dual('CPS',12),
if(PG='PC',dual('PCS',10),
if(PG='VC',dual('VCS',11),
if(PG='ALL',null(),PG)))))
Check the references given here: Why is using Pick and Match functions combination thought to be faster than nested IF statements?
May be try this:
=If(Match(PG, 'M', 'P others', 'NE', 'PE', 'HC', 'EC', 'FOOD', 'BIO', 'DIS, 'SB', 'Ca', 'PH', 'PRO', 'SEYO', Dual('Others',26), Pick(Match(PG, 'CP', 'PC', 'VC', 'ALL') + 1, PG, Dual('CPS',12), Dual('PCS', 10), Dual('VCS',11), Null()))
or
If(Match(PG, 'M', 'P others', 'NE', 'PE', 'HC', 'EC', 'FOOD', 'BIO', 'DIS, 'SB', 'Ca', 'PH', 'PRO', 'SEYO', Dual('Others',26), Pick(Match(PG, 'CP', 'PC', 'VC', 'ALL') + 1, PG, Dual('CPS',12), Dual('PCS', 10), Dual('VCS',11), Null())) as NewPG
why you use +1
For when it doesn't meet the criteria, the match will give 0 and then adding 1 to will give the value associated with PG. and then all the values within Match will increment by 1 for pick to do its job.
If(Match(PG, 'M', 'P others', 'NE', 'PE', 'HC', 'EC', 'FOOD', 'BIO', 'DIS, 'SB', 'Ca', 'PH', 'PRO', 'SEYO', Dual('Others',26), Pick(Match(PG, 'CP', 'PC', 'VC', 'ALL') + 1, PG, Dual('CPS',12), Dual('PCS', 10), Dual('VCS',11), Null())) as NewPG