返回指定查找日期当天或之前匹配项的最新单价

Question

在Google Sheets中，我有一个按订单日期、项目和价格记录的订单列表。

每行有一个订单，但相同的项目在不同的日期下订单，并且价格因订单而异。

我正在尝试返回一个查找项的最新订单价格，该查询项仅限于在指定日期或之前进行的订单。

在下面的示例数据中，我的查找值（批次日期和项目）位于工作表“批处理日志”中，而查找范围位于“订单”中。

配料日志（表1）

批处理日期	项目	单价
2024年2月1日	沙	公式

订单（表2）

订单日期	项目	单价
2024年2月5日	沙	$2.50
2024年1月20日	沙	$2.00
2024年4月21日	石	$32.00
23年10月27日	木材	13.00美元
23年11月12日	沙	$5.00
2024年1月28日	木材	$12.00
2024年3月13日	石	$35.00

根据上述数据'批处理日志'！指挥与控制应返回的单价为$2.00

这是因为，在项目为“沙子”（第2、3和6行）的三个单独订单中，第2行的日期2024年1月20日是最新的，不超过查找日期。

网站上有一些类似的问题，但它们的差异太大了，我无法完全解决。

Answer 1

视频查询

=VLOOKUP（A2，排序（过滤器（订单！A:C，订单！B:B=B2）），3，1）

过滤器  分拣  视频查询

1. 过滤器订单项目所在行的工作表“沙子”并按日期排序。
2. 使用VLOOKUP匹配日期is_sorted=1它返回等于或小于的最近日期，并返回列索引3中的值。

索引和排序

=索引(分拣(命令！C2:C中，   命令！B2:B=B2,0，命令！A2:A≤A2,0，命令！答2:A，0），1）

索引  分拣

1. 单价列按以下列排序：
   1. 测试中每一行的1和0列订单对于项目=“沙子”
   2. 测试中的另一个类似列订单日期<=批次日期
   3. 最后一列是简单的订单日期
2. INDEX返回单价由SORT返回

SORTN公司

正如所指出的@第七章课程，INDEX和SORT的组合可以替换为单个函数SORTN。

=排序(命令！C2:C、1、0、，   命令！B2:B=B2,0，命令！A2:A≤A2,0，命令！答2:A，0）

SORTN公司

1. SORTN将要返回的行数设置为1并将重复处理设置为0（无重复项）。
2. 公式的其余部分与前一个公式相同，但不再需要INDEX。

查询

=查询(命令！A2:C，“选择C，其中B='”&B2&“'与A<=日期'“文本（E2，“yyyy-mm-dd”）&”'按DESC极限订购1“）

查询  文本

1. 查询范围命令！答2:C其中：
   1. 订单项=批处理项
   2. 订单日期<=批次日期
2. 结果排序依据订单日期下降。
3. 将结果限制为1行，这是最近的批量价格其日期和项目符合条件。