带日期条件的匹配问题

liwenhao96

如图，A-D列是源数据，K列根据I列和J列进行匹配，逻辑：
1、查询日期应该在设置日期后、截止日期前才有结果
2、查询日期在设置日期后且在截止日期前，则根据名称去匹配金额
请高手帮忙补充黄色区域公式，谢谢！

良良

K2=IFERROR(LOOKUP(1,0/(ISNUMBER(VDB(,,D$2:D$7,C$2:C$7,I2))*(A$2:A$7=J2)),B$2:B$7),"")

meiya

K2单元格=IFERROR(LOOKUP(1,0/(($A$2:$A$7=$J2)*($C$2:$C$7<=$I2)*($D$2:$D$7>=$I2)),$B$2:$B$7),"无"),多条件查询

1786024734

1. =XLOOKUP(1,($I2>=$C$2:$C$7)*($I2<=$D$2:$D$7)*($A$2:$A$7=$J2),$B$2:$B$7,"无")
   
2. =FILTER($B$2:$B$7,($I2>=$C$2:$C$7)*($I2<=$D$2:$D$7)*($A$2:$A$7=$J2),"无")

复制代码

ericxzhou

=TEXT(SUM((I2>=$C$2:$C$7)*(I2<=$D$2:$D$7)*($A$2:$A$7=J2)*$B$2:$B$7),"[=0]无;0")