google-sheets - 基于相应日期的匹配值

Question

我附上了电子表格。

我想根据实时表中提供的日期从查找表中查找客户编号。

同一个客户可能会出现不同的客户编号，因此我需要查找名称和日期（来自实时表）并在查找表中找到相应的客户编号，其中实时表中的日期介于查找表上的两个日期之间.

我希望这是有道理的。任何帮助表示赞赏。谢谢

标签： google-sheetsgoogle-sheets-formula

Answer 1

这可能会满足您的需求。

=IFERROR( 
  QUERY(SORT(FILTER(Lookup!A$2:D,Lookup!C$2:C=B2,Lookup!A$2:A<=A2),1,0), 
    "SELECT * WHERE COL4 >= DATE '"&TEXT(A2,"YYYY-MM-DD")&"' LIMIT 1",0),
  QUERY(SORT(FILTER(Lookup!A$2:D,Lookup!C$2:C=B2,Lookup!A$2:A<=A2),1,0),
    "SELECT * LIMIT 1",0) )

我已经在您的工作表中添加了一个选项卡 Live-GK，在 C2 中使用了这个公式。它必须被拖下来。可能还有另一种方法可以将其作为数组公式来完成，但我还没有弄清楚。

请注意，在我的选项卡上，我正在从 Lookup-GK 进行查找，因为我可以在那里添加更多测试数据。上面的公式可以按原样使用，粘贴到 Live 选项卡中的单元格 C2 中。

请注意，出于调试目的，我的选项卡的 H 列会返回所有列，而不仅仅是客户端 #，因此可以验证开始和结束日期。

让我知道这是否对您有帮助。

解释：

内部过滤器从 Lookup 选项卡中选择所有行，其中：i) 客户端名称（Lookup 中的 C 列）与 B 列（Live）中的客户端名称匹配，并且ii) 开始日期（Lookup 中的 A 列）小于大于或等于 Live 中的客户日期。

这些记录按日期降序排列。

然后查询选择结束日期（查找中的 D 列）大于 Live 中的客户日期的第一条记录。

如果查找记录没有结束日期，则会给出错误（空查询结果），因此如果出现错误，则运行第二个查询，但没有按结束日期过滤，选择没有结束日期但有适当开始日期的记录。

这些似乎适用于我使用的少数测试记录。如果客户日期重复，则返回第一个客户#。请参阅我的测试数据中的客户端 #1 和 #7。如 CalculusWhiz 所问，如果您的客户记录可能具有重叠的日期范围，则可能需要进行更多的错误处理。