提问人：Ryrytheguyguy 提问时间：8/16/2023 最后编辑：teylynRyrytheguyguy 更新时间：8/16/2023 访问量：102
基于另一个单元格中的日期是否小于另一个表格的查找功能
Lookup function based on if a date in another cell is less than the other table
问：
我有两张桌子，我正在尝试组合。表 1（左图）包含库存转移日期以及组合的 customerID 和 Part#。表 2（右图）包含发票日期、单价以及组合的 CustomerID 和 Part#。我想在第一个表中进行查找，以提供每个 CustomerID 和 Part# 组合在转移日期之前的最后发票价格（发票日期<转移日期）。我不知如何解决这个问题，而不砍掉我所有的几十万行数据。任何帮助都是值得赞赏的。先谢谢你。
我已经尝试了带有嵌套 if 语句的索引匹配变体，但没有任何效果。
答：
0赞
MartyMcfly0033
8/16/2023
#1
这是一个 2 步过程，需要您正确排序您正在查找信息的表格。由于 Match 函数只能查找 1 个项目，因此我们将使用 Offset 函数来动态定义范围。
第 1 步按 CustomerID 和 Part# 升序对表 2 中的数据进行排序，然后按发票日期升序排序。
步骤 2生成公式。为了提高可读性，我将缩进并使用多行，然后提供完整的公式进行复制和粘贴。我是根据图片来计算公式的。我假设公式将位于单元格 C2 中。
INDEX(
OFFSET(
$F$2:$F$5,
MATCH(B2,$G$2:$G$5,0)-1,
0,
COUNTIFS($G$2:$G$5,B2)
),
MATCH(
A2,
OFFSET(
$E$2:$E$5,
MATCH(B2,$G$2:$G$5,0)-1
,0
,COUNTIFS($G$2:$G$5,B2)
),
1
)
)
折叠的 Excel 公式=INDEX(OFFSET($F$2:$F$5,MATCH(B2,$G$2:$G$5,0)-1,0,COUNTIFS($G$2:$G$5,B2)),MATCH(A2,OFFSET($E$2:$E$5,MATCH(B2,$G$2:$G$5,0)-1,0,COUNTIFS($G$2:$G$5,B2)),1))
解释
偏移允许您动态选择范围。在这种情况下，由于 Match 函数一次只能查找 1 个值，因此我们可以使用 Offset 来克服此限制。
在 Index 函数中，我们通过将 CustomerID 和 PartID 与 G 列中该组合的第一次出现进行匹配，动态选择 F 列中的范围子集。偏移功能的第 4 个参数允许您选择范围的“高度”。因此，我们使用 CountIF 来控制集合的大小。在 Match 函数中，我们执行相同的精确逻辑，但不是返回 F 列，而是返回 E 列。通过将此 Match 函数设置为 1，它将返回它找到的小于或等于查找值的最大值。
同样，此方法要求您正确排序数据。如果数据排序不正确，可能会得到错误的结果，甚至被错误地标记为未找到。
这是我的 excel 中的屏幕截图，显示了单元格 C2 中的公式，当前返回 1.5
2赞
Harun24hr
8/16/2023
#2
Microsoft-365 解决方案。
=TAKE(SORT(FILTER($E$2:$F$5,($E$2:$E$5<A2)*($G$2:$G$5=B2),"NA"),1,-1),1,-1)
1赞
VBasic2008
8/16/2023
#3
完全匹配或下一个更小的 （-1）
=LET(slData,F2:H5,sdCol,1,siCol,3,spCol,2,
dlData,A2:B15,ddCol,1,diCol,2,
dNA,"No Date",iNA,"No ID",
sl,SORT(slData,sdCol),
si,INDEX(sl,,siCol),
dd,INDEX(dlData,,ddCol),
di,INDEX(dlData,,diCol),
MAP(dd,di,LAMBDA(rd,ri,LET(
sfi,si=ri,
IF(SUM(N(sfi))=0,iNA,LET(
sf,FILTER(sl,sfi),
sm,XMATCH(rd,INDEX(sf,,sdCol),-1,2),
IF(ISNA(sm),dNA,INDEX(sf,sm,spCol))))))))
评论