求多条件判断的公式

无影一线

条件时段范围,人员性质分别按要求判断属于哪种费用类别

zwzc

=IFERROR(FILTER(参数表人员!$E$2:$E$25,(参数表人员!$A$2:$A$25=A2)*(参数表人员!$B$2:$B$25<=C2)*(参数表人员!$C$2:$C$25>=C2)),"")

quqiyuan

=IFERROR(IF(VLOOKUP(A2,参数表人员!$A$2:$D$25,4,0)="常设人员","其他费用",IF(COUNTIFS(参数表人员!A:A,A2,参数表人员!B:B,"<="&C2,参数表人员!C:C,">="&C2)>0,"其他费用","")),"")

huaichuan1

=IF(SUMPRODUCT((参数表人员!$A$2:$A$25=A2)*(参数表人员!$D$2:$D$25="常设人员"))>0,"其他费用",IF(SUMPRODUCT((参数表人员!$A$2:$A$25=A2)*(参数表人员!$D$2:$D$25="常备人员")*(参数表人员!$B$2:$B$25<=C2)*(参数表人员!$C$2:$C$25>=C2))>0,"其他费用",""))
低版本 公式

gadxjyang

=IFERROR(IF(MATCH(1=1,BYROW(IF(参数表人员!A$2:A$25=A2,参数表人员!B$2:C$25,0)-C2,PRODUCT)<=0,),"其他费用"),"")

无影一线

zwzc 发表于 2024-11-28 12:44
=IFERROR(FILTER(参数表人员!$E$2:$E$25,(参数表人员!$A$2:$A$25=A2)*(参数表人员!$B$2:$B$25=C2)),"")

感谢,新版本函数确实好用

无影一线

quqiyuan 发表于 2024-11-28 12:50
=IFERROR(IF(VLOOKUP(A2,参数表人员!$A$2:$D$25,4,0)="常设人员","其他费用",IF(COUNTIFS(参数表人员!A:A,A ...

感谢提供数组公式方案

无影一线

huaichuan1 发表于 2024-11-28 13:13
=IF(SUMPRODUCT((参数表人员!$A$2:$A$25=A2)*(参数表人员!$D$2:$D$25="常设人员"))>0,"其他费用",IF(SUMPRO ...

感谢提供低版本公式