根据对应条件返回对应值

迷人丶

需要用两个条件：条件1大于40，条件2小于70分，和G列数据对比，将G列的值满足该条件：40
求助文件.zip (8.26 KB, 下载次数: 138)

2021-11-11 11:56 上传

点击文件名下载附件
求助文件.zip

大逊

C2 公式下拉
=INDEX(Sheet2!C:C,SMALL(IF((Sheet2!$G$2:$G$10>40)*(Sheet2!$G$2:$G$10<70),ROW($G$2:$G$10)),ROW(A1)))

总动员

=IFERROR(INDEX(Sheet2!C:C,AGGREGATE(15,6,ROW($2:$10)/((Sheet2!$G$2:$G$10>40)*(Sheet2!$G$2:$G$10<70)),ROW(A1))),"")

这跟上一题不是一样的吗

惠三少

根据字面理解不知对不对？
=IF((Sheet2!G2>40)*(Sheet2!G2<70),Sheet2!$C2,"")
或
=INDEX(Sheet2!C:C,SMALL(IF((Sheet2!$G$2:$G$10>40)*(Sheet2!$G$2:$G$10<70),ROW($2:$10),99),ROW(A1)))&""