数据引用查询

ericxzhou

高个子

狗剩呵呵哒 发表于 2023-7-18 12:19
左侧表格内的值，对照右边那张表，是先寻找小于等于的值，然后再寻找对应的分位值。
且V9单元格变成136 ...

小于的怎么找？

1. =TEXT(NPV(99,--TEXT(MMULT(N(N(OFFSET(C4,,RIGHT(P$2,2)-{2;1;0}))>=OFFSET(R$3,RIGHT(P$2,2),MATCH(C4,S$2:Z$2,),-3,4)),{1;1;1;1})*25,"[<90];9\0"))*10^6,"0-00-00")

复制代码

博爱风雨同舟

=TEXTJOIN("-",1,N(INDIRECT(TEXT(BYROW((TRANSPOSE(G4:I4)>=TAKE(IF(C4="中级教师",$W$4:$Z$9,$S$4:$V$9),-3))*300+COLUMN(S:V),LAMBDA(X,MAX(X))),"R0C00"),)))

橒♂蝣

=LET(X,XLOOKUP(FILTER(D4:I4,ISNUMBER(XMATCH($D$3:$I$3,$P$2))),OFFSET($R$3,XMATCH($P$2,$R$4:$R$9),XMATCH(C4,$S$2:$Z$2),,4),OFFSET($R$2,1,XMATCH(C4,$S$2:$Z$2),,4),,-1),
Y,XLOOKUP(FILTER(D4:I4,ISNUMBER(XMATCH($D$3:$I$3,$P$2-1))),OFFSET($R$3,XMATCH($P$2-1,$R$4:$R$9),XMATCH(C4,$S$2:$Z$2),,4),OFFSET($R$2,1,XMATCH(C4,$S$2:$Z$2),,4),,-1),
Z,XLOOKUP(FILTER(D4:I4,ISNUMBER(XMATCH($D$3:$I$3,$P$2-2))),OFFSET($R$3,XMATCH($P$2-2,$R$4:$R$9),XMATCH(C4,$S$2:$Z$2),,4),OFFSET($R$2,1,XMATCH(C4,$S$2:$Z$2),,4),,-1),
TEXTJOIN("-",1,Z,Y,X)