怎么用函数实现精准+范围查找?

qq58246082

怎么用函数实现精准+范围查找?

无关岁月

1. =LOOKUP(,VDB(,,C$2:C$13,B$2:B$13,L2)/(A$2:A$13=K2),D$2:D$13)

复制代码

quqiyuan

1. =LOOKUP(-L2,-IF(K2=$A$2:$A$13,$C$2:$C$13,1/0),$D$2:$D$13)

复制代码

qq58246082

无关岁月 发表于 2024-3-14 12:57

感谢老师，测试能用！

qq58246082

quqiyuan 发表于 2024-3-14 13:02

感谢老师，测试能用

袁勇

区间不是闭环，需要容错处理。

1. =IFERROR(LOOKUP(1,0/((A$2:A$13=K2)*(B$2:B$13<=L2)*($C2:$C13>=L2)),D$2:D$13),"")

复制代码

hxj119

=LOOKUP(1,0/(($B$2:$B$13<=L2)*($C$2:$C$13>=L2)*($A$2:$A$13=K2)),$D$2:$D$13)

Excel地板砖

1. =LOOKUP(-L2,-OFFSET($C$2,MATCH(K2,$A$2:$A$13,)-1,,COUNTIF($A$2:$A$13,K2),1),$D$2:$D$5)

复制代码

我的城市你来过

=CONCAT(TOCOL(IF(FREQUENCY($H2,FILTER($C$2:$C$13,$A$2:$A$13=$G2))=1,$D$2:$D$13,""),3))