求助，有没有公式能快速计算的。

jiasiwen

求助，已知各班名单 以及 现有获奖名单。希望通过公式，能直接计算出各个班获奖的同学名单。求助大神，看有没有公式能快速算出来。谢谢!!

jerrike

1. =IFERROR(TOROW(VLOOKUP($A$2:$A$7,FILTER($C$2:$E$17,$C$1:$E$1=G2),1,),3),"没人获奖")

复制代码

=IFERROR(TOROW(VLOOKUP($A$2:$A$7,OFFSET($C$2:$C$17,,REGEXP(G2,"\d")-1),1,),3),"没人获奖")

lanjian229

=LET(s,CHOOSECOLS($C$1:$E$17,MATCH(G2,$C$1:$E$1,)),TOROW(FILTER(s,BYROW(N(s=TRANSPOSE($A$1:$A$7)),SUM),"无人获奖")))

cnmlgb9998

=LET(_a,FILTER($C$1:$E$17,$C$1:$E$1=G4),IFERROR(TOROW(XLOOKUP(A4:A9,_a,_a),3),"无人获奖"))

你是那块小饼干

=LET(T,C2:E17,DROP(REDUCE("",G2:G4,LAMBDA(X,Y,LET(_T,CHOOSECOLS(T,MATCH(Y,C1:E1,0)),IFNA(VSTACK(X,TOROW(FILTER(_T,BYROW(N(_T=TRANSPOSE(A2:A7)),SUM),"没人获奖"))),"")))),1))

hxj119

1. =LET(a,OFFSET($C$2:$C$17,,MATCH(G2,$C$1:$E$1,0)-1),b,$A$2:$A$7,TRANSPOSE(FILTER(a,COUNTIF(b,a),"")))

复制代码

cnmlgb9998

waternew

=INDEX($A$2:$A$7,SMALL(IF(COUNTIF(OFFSET($B$2,,MATCH($G2,$C$1:$E$1,),16),$A$2:$A$7),ROW($1:$6)),COLUMN(A:A)))

hcm19522

{=OFFSET($B$1,SMALL(IF(COUNTIF($A:$A,$C$2:$E$18)*($C$1:$E$1=$G2),ROW($1:$17),99),COLUMN(A1)),ROW(A1))&""

我要成为专家

新函数，最简单直接

1. =LET(s,FILTER($C$2:$E$17,$C$1:$E$1=G2),IFERROR(TOROW(FILTER(s,COUNTIF($A$2:$A$7,s))),"没人获奖"))

复制代码

老函数，稍长，逻辑复杂些

1. =IFERROR(INDIRECT(TEXT(SMALL(IF(COUNTIF($A$2:$A$7,$C$2:$E$17)*($C$1:$E$1=$G2),ROW($2:$17)*100+{3,4,5}),COLUMN(A1)),"r0c00"),),"没人获奖")

复制代码

1. =IFERROR(INDEX(OFFSET($B$2:$B$17,,ROW(A1)),SMALL(IF(COUNTIF($A$2:$A$7,OFFSET($B$2:$B$17,,ROW(A1))),ROW($1:$16)),COLUMN(A1))),"没人获奖")

复制代码