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","3rd","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.