根据其中一项内容对整行数据进行提取

小柒S

求各位大神帮助，想根据科目把相关科目的行内容单独提取出来，谢谢~~

原内容：


想要的结果：

hugeinfo

=FILTER(HSTACK(LOOKUP(ROW(2:7),ROW(2:7)/(A2:A7<>""),A2:A7),LOOKUP(ROW(2:7),ROW(2:7)/(B2:B7<>""),B2:B7),C2:D7),C2:C7=H1)

无关岁月

1. =LET(_x,TRANSPOSE(SCAN(0,TRANSPOSE(A2:D7),LAMBDA(_a,_b,IF(_b=0,_a,_b)))),FILTER(_x,INDEX(_x,,3)=H1))

复制代码

hxj119

=LET(a,HSTACK(SCAN(,A2:A7,LAMBDA(x,y,IF(y="",x,y))),SCAN(,B2:B7,LAMBDA(m,n,IF(n="",m,n))),C2:D7),FILTER(a,C2:C7=H1))

comeet

1. =LET(t,DROP(REDUCE("",A1:D1,LAMBDA(m,n,HSTACK(m,SCAN("",OFFSET(n,1,,6),LAMBDA(x,y,IF(y<>"",y,x)))))),,1),FILTER(t,CHOOSECOLS(t,3)=H1))

复制代码

一杯绿茶来了

=FILTER(SCAN("",A2:D7,LAMBDA(x,y,IF(y<>"",y,OFFSET(y,2-ROW(y),)))),C2:C7=H1)

沈默00

=LET(s,LAMBDA(x,y,IF(y="",x,y)),t,HSTACK(SCAN(0,A2:A7,s),SCAN(0,B2:B7,s),C2:D7),FILTER(t,CHOOSECOLS(t,3)=G1))

袁勇

低版本：

1. =LOOKUP(1,0/(OFFSET(A$1,,,LOOKUP(1,0/FREQUENCY(ROW(A1),COUNTIF(OFFSET($C$1,,,ROW($1:10)),$H$1)),ROW($1:10)))<>0),A$1:A11)

复制代码

mjm04

G4单元格

=LET(_姓名,SCAN("",A2:A7,LAMBDA(a,b,IF(b<>"",b,a))),_年龄,SCAN("",B2:B7,LAMBDA(a,b,IF(b<>"",b,a))),_查询区域,HSTACK(_姓名,_年龄,C2:C7,D2:D7),FILTER(_查询区域,CHOOSECOLS(_查询区域,3)=H1))

咔咔乱坠

解1

1. =FILTER(TRANSPOSE(SCAN(,TRANSPOSE(A2:D7),LAMBDA(x,y,IF(y="",x,y)))),C2:C7=H1)

复制代码

解2，少了一个字，呵呵

1. =FILTER(MAP(A2:D7,LAMBDA(x,TAKE(TOCOL(OFFSET(x,,,-ROW(x)),3),-1))),C2:C7=H1)

复制代码