函数题：查找组别

09，10年那时候答题人还蛮多的

看不清楚哎

耗尽毕生心血用了135个字符做出一半来
=INDEX($A$2:$A$15,MATCH(--(CODE(LEFT(F2))&MID(F2,2,3)),IF(LEFT(F2)=LEFT($C$2:$C$15),--(CODE(LEFT($C$2:$C$15))&MID($C$2:$C$15,2,3)),"")))

我只会用VBA的在旁边看看，函数太难了

我试了下还真行，里面有个小技巧，类似x014和a451 是可以比较大小的这样

这下没有错了！好了

我写了两条公式跑过来看，结果要一条公式完成，我回去在想想

129个字符
G2 =INDEX(A:A,INDEX(FREQUENCY(--(FIND(LEFT(C$2:C$15),"ABDCX") & RIGHT(C$2:C$15,3)),--(FIND(LEFT(F2),"ABDCX") & RIGHT(F2,3))-1),0)+2)
87个字符
H2 =INDEX(B:B,MAX((IF(LEN(B$2:B$15)>0,ROW($2:$15),4^8)<=MATCH(G2,A$1:A$15,))*ROW($2:$15)))
数组公式

=LOOKUP(MATCH(RIGHT($E3,3),IF(LEFT($E3)=LEFT($A$2:$A$15),MID($C$2:$C$15,2,3),0)),MATCH(A$2:A$15,A$2:A$15,),A$2:A$15)

G2 =INDEX(A:A,LOOKUP(1,0/(OFFSET(A$2,,,MATCH(LEFT($F2),LEFT($A$2:$A$15),)+SUM((LEFT($F2)=LEFT($A$2:$A$15))*(--RIGHT($C$2:$C$15,3)<=--RIGHT($F2,3)-1)),1) <>""),ROW($2:$15)))
169字符，还是数组公式