Excel如何查找并引用最接近日期对应的数据

Question

Excel如何查找并引用最接近日期对应的数据。具体如图，当表一中的日期和品种与表二中的日期、品种一致时，则表一中的价格直接取表二中对应日期和品种的价格（如：表一中2019/6/1的苹果价格直接取表二中2019/6/1的苹果价格56.61），如果表二中未找到与表一对应的日期、品种的价格，表一的价格则取表二中品种相同且日期往前数最接近的价格（如：表二中2019/6/2无哈密瓜的价格，表一中2019/6/2哈密瓜的价格则取表二中2019/6/1中哈密瓜的价格56.71）。谢谢各位大神帮忙！

Answer 1

如果两表内容在同一个工作表中

在C4中输入或复制粘贴下列公式

=IFERROR(LOOKUP(1,0/((E:E=A4)*(F:F=B4)),G:G),INDEX(G:G,MAX(IF((F$1:F$100=B4)*(E$1:E$100<A4),ROW($1:$100)))))

按三键CTRL+SHIFT+ENTER结束公式输入

下拉填充

如果是两个表，只要在单元格引用左侧加上工作表名称及半角叹号!即可。

=IFERROR(LOOKUP(1,0/((表二!E:E=A4)*(表二!F:F=B4)),表二!G:G),INDEX(表二!G:G,MAX(IF((表二!F$1:F$100=B4)*(表二!E$1:E$100<A4),ROW($1:$100)))))

上面公式单元格引用区域比较小，才100行。应根据实际情况进行调整。

追问

你好，这两个公式在数据量不多的时候都能得出正确的结果，但是我今天用在两张数据量很大的表上面之后，就总是出现#N/A这样的结果，不知道是为什么，能帮忙看一下吗？谢谢了

追答

上面公式单元格引用区域比较小，才100行。应根据实际情况进行调整。
比如将公式中的100调整为1000或2000。

Answer 2

C4 公式
=lookup(1,0/((F$4:F$1000=B4)*(E$4:E$1000<=A4)),G$4:G$1000)
结果为 小于等于 查找日期的 价格 ，E列日期许升序

Answer 3

LOOKUP函数即可
简单的全覆盖公式为：C4单元格输入公式：=LOOKUP(1,0/((F:F=B4)*(E:E<=A4)),G:G)
但是这个公式核算太慢了
若改用E：G实际有数据的区域，公式改为：
=IFERROR(LOOKUP(1,0/(($F$4:$F$18=B4)*($E$4:$E$18<=A4)),$G$4:$G$18),"")
就可以在E8:G18区域内查找相对应的日期最新产品价格了

Answer 4

如果表2日期是按顺序排列，那么可以
=IFERROR(LOOKUP(1,0/((E:E=A4)*(F:F=B4)),G:G),VLOOKUP(B4,F:G,2,0))
不对，vlookup引用第1个，不是最接近那1个，换下面这个
=LOOKUP(0,IF(F:F=B4,E:E-A4,""),G:G)
数组函数，shift+ctrl+enter三键一起按下。