合拼单元格查找数据公式

huangrk

求助关于合拼单元格查找数据公式：附件黄色部分

yfan17

1. =SUMPRODUCT((LOOKUP(COLUMN($C$1:$Q$1),COLUMN($C$1:$Q$1)/($C$4:$Q$4<>""),$C$4:$Q$4)="星期二")*(LOOKUP(COLUMN($C$1:$Q$1),COLUMN($C$1:$Q$1)/($C$5:$Q$5<>""),$C$5:$Q$5)=1)*($C$6:$Q$6=C$6)*($B$7:$B$11="B")*($C$7:$Q$11))

复制代码

满坛皆为吾师

L1,

1. =TOROW(IF((MMULT({1,1},--(SCAN("",C4:Q5,LAMBDA(x,y,IF(y="",x,y)))={"星期二";1}))=2)*(B7:B11="B"),C7:Q11,a),3)

复制代码

L2,

1. =TOROW(IF((MMULT({1,1},--(SCAN("",C4:Q5,LAMBDA(x,y,IF(y="",x,y)))={"星期四";1}))=2)*(B7:B11="D"),C7:Q11,a),3)

复制代码

静听溪流

=INDEX($C$7:$Q$11,MATCH("B",$B$7:$B$11,),LARGE((RIGHT(PHONETIC(OFFSET($C$4,,,,COLUMN($A$1:$O$1))),3)="星期二")*(LOOKUP(COLUMN($A$1:$O$1),IF($C$5:$Q$5,COLUMN($A$1:$O$1)),$C$5:$Q$5)=1)*COLUMN($A$1:$O$1),4-COLUMN(A1)))

静听溪流

=INDEX($C$7:$Q$11,MATCH("D",$B$7:$B$11,),LARGE((RIGHT(PHONETIC(OFFSET($C$4,,,,COLUMN($A$1:$O$1))),3)="星期四")*(LOOKUP(COLUMN($A$1:$O$1),IF($C$5:$Q$5,COLUMN($A$1:$O$1)),$C$5:$Q$5)=1)*COLUMN($A$1:$O$1),4-COLUMN(A2)))

橒♂蝣

=FILTER(FILTER(C7:Q11,B7:B11=B8),(XLOOKUP(SEQUENCE(,COLUMNS(C4:Q4)),SEQUENCE(,COLUMNS(C4:Q4))/(XMATCH(C4:Q4,C4:Q4)<>2),C4:Q4,,-1)="星期二")*(XLOOKUP(SEQUENCE(,COLUMNS(C5:Q5)),SEQUENCE(,COLUMNS(C5:Q5))/(XMATCH(C5:Q5,C5:Q5)<>2),C5:Q5,,-1)=1))

quqiyuan

L1=OFFSET($B$6,MATCH("B",$B$7:$B$11,),MATCH("星期二1"&C$6,LOOKUP(COLUMN($A:$O),COLUMN($A:$O)/($C4:$Q4<>""),$C4:$Q4)&LOOKUP(COLUMN($A:$O),COLUMN($A:$O)/($C5:$Q5<>""),$C5:$Q5)&$C$6:$Q$6,))

L2=OFFSET($B$6,MATCH("D",$B$7:$B$11,),MATCH("星期四1"&C$6,LOOKUP(COLUMN($A:$O),COLUMN($A:$O)/($C4:$Q4<>""),$C4:$Q4)&LOOKUP(COLUMN($A:$O),COLUMN($A:$O)/($C5:$Q5<>""),$C5:$Q5)&$C$6:$Q$6,))
右拉

jivy1212

1. =OFFSET($A$6,MATCH("d",$B$7:$B$11,),MATCH(2,MMULT({1,1},N(MMULT(N($C$4:$Q$5={"星期四";1}),N(ROW($1:$15)<=COLUMN($A:$O)))>0)),)+COLUMN(A1))

复制代码

右拉,建议把条件写成单元格，这就可以右拉下拉

一剑惊心

表格的设计把题目公式的复杂程度从0.05直接拉伸到0.8