关于班级定级的问题，求助

地主家李老板

关于班级定级的问题，求助
不想用IF嵌套，写太长头都痛，详见附件。

班级定级.zip (7.68 KB, 下载次数: 12)

2021-12-13 14:26 上传

点击文件名下载附件
班级定级.zip

糯米酉

=LOOKUP(1,0/FREQUENCY(1,(C$2:C$15=I2)*(LEFT(D$2:D$15&"0",FIND("-",D$2:D$15&"0-")-1)*1<K2)),E$2:E$15)

漫步冬季

=LOOKUP(,0/FREQUENCY(-K2,IF(I2=$C$2:$C$15,-TEXT(LEFT($D$2:$D$15,2),"0;;;"))),$E$2:$E$5)

数组公式
=LOOKUP(,0/FREQUENCY(-K2,-TEXT(LEFT($D$2:$D$15,2),"0;;;")/(I2=$C$2:$C$15)),$E$2:$E$5)

改良一下，非数组公式
=LOOKUP(,0/FREQUENCY(-K2,-LEFT($D$2:$D$15,2)/(I2=$C$2:$C$15)),$E$2:$E$5)

貌似这样就可以了。。。想复杂了

问路

=INDEX($E$2:$E$15,MATCH(1=1,IF($C$2:$C$15=I2,INT(SUBSTITUTE(0&$D$2:$D$15,"-",".")))<K2,))数组下拉，抄1个楼上大佬的思路

星朵朵

感谢各位大佬，本来是想着用LOOKUP+0/+条件相乘，但是有区间取数就卡住了，学习了