Thread: Nested IF Functions

I am using a formula for a sports result sheet. It displays whether a competitor is one of the teams in the top 4, and also their place within that team (which are made up of three people) (e.g the output could be "1st-1" "1st-2" "4th-3"etc.) The formula will be used for each of the 100 competitors.

Last year I had nine teams, which exceeded the maximum 7 nested IF functions allowed. I managed to get round this by using CONCATENATE. However this year I have 16 teams and when I try to add to my existing formula I reach the maximum character limit of 1024.

Grateful for any ideas. The foumala is below. It is not pretty, but it worked for nine teams (TrankA, TrankB etc. are defined names which give the overall position of the team)

=IF(AZ20="","",CONCATENATE(IF(AND(BF20>=1,BF20<=3,TrankA>=1,TrankA<=4),CHOOSE(TrankA,"1st","2nd","3r d","4th")&"-"&BF20,""),IF(AND(BH20>=1,BH20<=3,TrankB>=1,TrankB<=4),CHOOSE(TrankB,"1st","2nd","3rd","4th")&"-"&BH20,""),IF(AND(BJ20>=1,BJ20<=3,TrankC>=1,TrankC<=4),CHOOSE(TrankC,"1st","2nd","3rd","4th")&"-"&BJ20,""),IF(AND(BL20>=1,BL20<=3,TrankD>=1,TrankD<=4),CHOOSE(TrankD,"1st","2nd","3rd","4th")&"-"&BL20,""),IF(AND(BN20>=1,BN20<=3,TrankE>=1,TrankE<=4),CHOOSE(TrankE,"1st","2nd","3rd","4th")&"-"&BN20,""),IF(AND(BP20>=1,BP20<=3,TrankF>=1,TrankF<=4),CHOOSE(TrankF,"1st","2nd","3rd","4th")&"-"&BP20,""),IF(AND(BR20>=1,BR20<=3,TrankG>=1,TrankG<=4),CHOOSE(TrankG,"1st","2nd","3rd","4th")&"-"&BR20,""),IF(AND(BT20>=1,BT20<=3,TrankH>=1,TrankH<=4),CHOOSE(TrankH,"1st","2nd","3rd","4th")&"-"&BT20,""),IF(AND(BV20>=1,BV20<=3,TrankJ>=1,TrankJ<=4),CHOOSE(TrankJ,"1st","2nd","3rd","4th")&"-"&BV20,"")

I guess VBA may be the answer, but I know very little about that.