多条件引用外加型号关健字模糊查询 求助

蛋腚

先谢谢大神

满坛皆为吾师

E12下拉

1. =LOOKUP(,-SEARCH(B$2:B$7&"*"&C$2:C$7&"*"&D$2:D$7,B12&C12&D12),E$2:E$7)

复制代码

蛋腚

先谢谢大神，但下拉时把9月的也拉出来了，9月的没定价应该是显示空值才对？

solextrade

=LOOKUP(,0/(COUNTIF(C12,"*"&C$2:C$7&"*")*((D12=D$2:D$7)+(B12="空调"))),E$2:E$7)

蛋腚

solextrade 发表于 2019-10-12 14:54
=LOOKUP(,0/(COUNTIF(C12,"*"&C$2:C$7&"*")*((D12=D$2:D$7)+(B12="空调"))),E$2:E$7)

先谢谢大神，但下拉时把9月的也拉出来了，9月的没定价  应该是显示空值才对？

solextrade

用满版的公式吧

=IF(MONTH(A12)=8,LOOKUP(,-SEARCH(B$2:B$7&"*"&C$2:C$7&"*"&D$2:D$7,B12&C12&D12),E$2:E$7),"")

蛋腚

solextrade 发表于 2019-10-12 15:01
用满版的公式吧

=IF(MONTH(A12)=8,LOOKUP(,-SEARCH(B$2:B$7&"*"&C$2:C$7&"*"&D$2:D$7,B12&C12&D12),E$2: ...

完美解决，谢谢大神，灰常感谢，园满解决

我走我流

=IFERROR(VLOOKUP(MONTH(A12)&B12&IF(B12="空调","",D12)&LOOKUP(1,-FIND($C$2:$C$7,C12),$C$2:$C$7),IF({1,0},$A$2&$B$2:$B$7&$D$2:$D$7&$C$2:$C$7,$E$2:$E$7),2,),"")
数组下拉，满版的思路最简洁