从下到上搜索数组

Question

我有一个表数组$E$14:$G$851。E 列仅包含唯一标识符，例如 a/b/c/d 等，F 列包含数百个小于或等于 20 的数字，G 列包含我需要返回的值。

我需要一个公式来搜索 E 列中的唯一标识符，然后搜索向上F 列并找出小于 F 列结果的第一个值，然后使用该结果返回 G 列中的相邻值。

因此，如果我搜索“g”，公式将搜索向上F 列中第一个小于 16 的数字。这将是“d”，即 12。然后公式将从 G 列返回 25010。

我已经搜索这个问题的答案好几个星期了，但还没有找到任何 excel 从下到上搜索数组的例子。我强烈希望不要更改数据布局（例如翻转它）。

Answer 1

您可以使用以下数组公式来获取它（在单元格中输入后按 CTRL+SHIFT+ENTER：

=INDEX(C:C,MAX(IF(OFFSET($B$1,0,0,MATCH(E1,A:A,0)-1)<VLOOKUP(E1,A:B,2,FALSE),ROW(OFFSET(B1,0,0,MATCH(E1,A:A,0)-1)),"")))

怎么运行的：

• OFFSET($B$1,0,0,MATCH(E1,A:A,0)-1)- 获取正在搜索的 ID 的 B 列的引用
• IF(OFFSET(...)<VLOOKUP(E1,A:B,2,FALSE),ROW(OFFSET(...),"")- 返回一个数组，其中包含 B 中值小于搜索行的行号，其他行号为空白
• MAX(IF(...))- 选择最大值（最后一行 B 中的值满足条件
• =INDEX(C:C,MAX(...))- 选择 C ​​列中的单元格

Answer 2

例子中，结果所在的列是 G，但正确的行是哪一行呢？如果我们能找到该行，那么INDIRECT("G"&row)就会得到结果。

一种解决方案是添加一列指针作为 H 列。每个指针都是行，INDIRECT并且VLOOKUP可以找到它。单元C2格有公式。

=INDIRECT("G"&VLOOKUP(B2,$E$1:$H$9,4,FALSE))

因此，现在向后搜索简化为VLOOKUP查找指向结果的指针。如何生成 H 列？我还没有找到仅使用 Excel 公式的方法，但这里有一些想法。

如果 F 列很少变化，那么只需复制粘贴几行 perl 或 python 即可。我使用了 perl。如果 F 列更新更频繁，那么 VBA 可能会起作用。或者，perl 可以从主 Excel 文件中读取数据并生成第二个工作簿以用作外部源。

指针避免了翻转数据或更改数据布局的需要。棘手的向后搜索只需通过 perl 或 VBA 一次性完成，并且VLOOKUP快速、简单且易于测试。