arrays - 索引匹配不同日期和值之间的搜索

Question

我正在尝试针对 2 组标准完成查找。从附件中，我需要在 E 列中返回费用，其中搜索日期介于开始日期和结束日期 (Cols A&B) 之间，并且房产值(value)介于下限和上限之间 (Cols C&D)。我附上了一些截图，以帮助使这一点更清楚。

我的公式在单元格 K3(结果表)中:{=INDEX($E$2:$E$21,MATCH(1,($A$2:$A$21>=H3)*($B$2:$B$21<=H3)*($C$2:$C$21>=I3)*($D$2:$D$21<=I3)),0)}
但目前，这个公式返回表格中的底部数字(137.17 英镑)......而它应该返回 95 英镑 - 我不确定它为什么这样做。

原始数据



结果表



希望这是有道理的，并且有人会很友善地提供帮助。
谢谢，保罗

Answer 1

让我们分解一下您的 MATCH 的条件。

($A$2:$A$21>=H3) * ($B$2:$B$21<=H3) * ($C$2:$C$21>=I3) * ($D$2:$D$21<=I3)

第一个条件:开始日期在目标日期之内或之后

第二个条件:结束日期在目标日期之前或之前

第三个条件:最小值小于或等于目标值

第四个条件:最大值大于或等于目标值。

我们可以将其简化为 2 个限制:

Start_Date ≥ Target_Date ≥ End_Date
Min_Value ≥ Target_Value ≥ Max_Value

您可能可以在这里看到问题，但让我们插入一些数字。首先，结果表中的目标值:

Start_Date ≥ {2018-06-18} ≥ End_Date
Min_Value ≥ 80000 ≥ Max_Value

现在，来自原始数据第 3 行的数字:

{2018-05-21} ≥ {2018-06-18} ≥ {2020-06-02}
50001 ≥ 80000 ≥ 100000

希望您现在注意到您获得了 >=和 <=混合!没有日期同时在 2020 年 6 月之后和 2018 年 5 月之前，也没有小于 50,000 和大于 100,000 的数字。

如果我们交换这些，你会得到这个:

=INDEX($E$2:$E$21,MATCH(1,($A$2:$A$21<=H3)*($B$2:$B$21>=H3)*($C$2:$C$21<=I3)*($D$2:$D$21>=I3)),0)

当然，这仍然行不通，因为您的 INDEX MATCH写错了。你有这个:

=INDEX(Range, MATCH(1, Condition), 0)

但是你应该有这个:

=INDEX(Range, Match(1, Condition, 0))

像这样:

=INDEX($E$2:$E$21, MATCH(1, ($A$2:$A$21<=H3)*($B$2:$B$21>=H3)*($C$2:$C$21<=I3)*($D$2:$D$21>=I3), 0))

理想情况下，我建议更改 50001 之类的内容。至 50000 , 只需使用 <而不是 <= (否则， 50000.5 会出错!)