
收到粉丝提问:如图excel根据日期满足条件前两列条件会返回第三列数值,判断条件是不是在B列和C列中返回E列呢?

涉及到条件判断的,第一时间都会想到用IF函数来解决。
我们来捋捋思路,起始日期与截止日期是数据区间,确实可以通过IF函数嵌套公式来实现目标。我们来看一下,公式是怎么写的。
假设非要使用IF函数,公式是这样的:
=IF(AND(G2<DATE(2024,1,11),G2>=DATE(2023,12,30)),3588,IF(AND(G2<DATE(2024,1,21),G2>=DATE(2024,1,11)),3555,IF(AND(G2<DATE(2024,1,30),G2>=DATE(2024,1,21)),3573,IF(AND(G2<DATE(2024,2,11),G2>=DATE(2024,1,30)),3552,IF(AND(G2<DATE(2024,2,21),G2>=DATE(2024,2,11)),3552,IF(AND(G2<DATE(2024,2,28),G2>=DATE(2024,2,21)),3549,IF(AND(G2<DATE(2024,3,11),G2>=DATE(2024,2,28)),3485,IF(AND(G2<DATE(2024,3,21),G2>=DATE(2024,3,11)),3343,IF(AND(G2<DATE(2024,3,30),G2>=DATE(2024,3,21)),3317,IF(AND(G2<DATE(2024,4,11),G2>=DATE(2024,3,30)),3258,""))))))))))
这个函数公式很繁杂,数据量大时并不适用。还容易出现错误,不建议使用这个冗长的函数公式。我们可以使用下面的方法来返回价格。

方法一:INDEX结合MATCH
可以在H2单元格输入下面的公式:
=IFERROR(INDEX($E$2:$E$11,MATCH(1,INDEX(($G2>=$B$2:$B$11)*($G2<=$C$2:$C$11),0),0)),"无价格")
该公式通过MATCH函数查找G2出现日期区间的位置,借助INDEX函数返回$E$2:$E$11中的值(即价格),利用IFERROR返回没找到时的值。
即当在 G2 中的日期落在 $B$2:$B$11 范围内的起始日期与 $C$2:$C$11 范围内的截止日期之间时,从价格列($E$2:$E$11)中找到对应的价格并返回。
如果没有找到相应的价格(即 G2 中的日期不在任何给定日期区间内),公式将返回 “无价格”。

方法二:VLOOKUP函数
可以在H2单元格输入下面的公式:
=VLOOKUP(G2,IF({1,0},$B$2:$B$11,$E$2:$E$11),2,1)
该公式通过IF函数创建一个虚拟的数组,其中第一列是起始日期 $B$2:$B$11,第二列是对应的价格 $E$2:$E$11。
再使用 VLOOKUP 函数在虚拟数组的第一列中查找 G2 的值。
使用了 1(近似匹配),VLOOKUP 会返回与 G2 最接近的日期对应的价格,从而得到日期区间对应的价格,来达到想要的效果。

方法三:LOOKUP函数
可以在H2单元格输入下面的公式:
=LOOKUP(G2,$B$1:$B$11,$E$1:$E$11)
LOOKUP函数会在 $B$1:$B$11(日期) 范围内查找 G2(指定日期) 的值。如果找到了相等的值,它就直接返回 $E$1:$E$11(价格) 相应位置的值。
如果在 $B$1:$B$11(日期) 范围内没有找到 G2 的值,它会返回 $B$1:$B$11 (日期)中小于 G2 的最大值对应的 $E$1:$E$11 (价格)中的值,也就是最后一个小于 G2 的值所对应的结果。
需要注意的是,使用该函数时,要确保查找数组(即$B$1:$B$11)是升序排序的哦。

方法四:XLOOKUP函数
可以在H2单元格输入下面的公式:
=XLOOKUP(G2,$B$1:$B$11,$E$1:$E$11,"未找到",-1)
XLOOKUP函数会在 $B$1:$B$11 (日期)范围内查找 G2(指定日期) 的值。
如果找到了完全匹配的值,它就返回 $E$1:$E$11(价格) 中相应位置的值。
如果没有找到完全匹配的值,-1 模式会返回小于或等于 G2 的最大值对应的 $E$1:$E$11 中的值。
如果 G2 小于 $B$1:$B$11 中的最小值,或者没有找到匹配的值,则返回 "未找到"。从而得到日期区间对应的价格,来达到想要的效果。

使用以上分享的函数公式,我们可以在 Excel 中根据任意给定的日期查找并返回对应的价格。
根据自己的需求和习惯,选择适合自己的方法。若还有疑问,欢迎给小兔留言哦~
想了解更多精彩内容,快来关注