☰

如何让 VLOOKUP 返回*最后*匹配？

tag-icon • tag-icon microsoft-excel libreoffice-calc openoffice-calc vlookup lookup
如何让 VLOOKUP 返回*最后*匹配？

我习惯使用 VLOOKUP，但这次我遇到了一个挑战。我不想要第一个匹配的值，但是最后的。怎么样？（我正在使用 LibreOffice Calc，但 MS Excel 解决方案应该同样有用。）

原因是我有两个包含数千行的文本列，假设一个是交易收款人列表（亚马逊、Ebay、雇主、杂货店等），另一个是支出类别列表（工资、税金、家庭、租金等）。有些交易每次的支出类别并不相同，我想获取最近使用的类别。请注意，列表不按任何列排序（实际上是按日期排序），我不想更改排序顺序。

我所拥有的（不包括错误处理）是通常的“首次匹配”公式：

=VLOOKUP( 
[payee field] , [payee+category range] , [index of category column] , 
0 )

我见过解决方案像这样，但是我收到#DIV/0!错误：

=LOOKUP(2 , 1/( [payee range] = [search value] ) , [category range] )

解决方案可以是任何公式，不一定是 VLOOKUP。我还可以交换收款人/类别列。请不要更改排序列。

解决方案选择最频繁值，而不是最后一个！

答案1

您可以使用数组公式从最后一个匹配的记录中获取数据。

=INDEX(IF($A$1:$A$20="c",$B$1:$B$20),MAX(IF($A$1:$A$20="c",ROW($A$1:$A$20))))

Ctrl使用+ Shift+输入公式Enter。

其工作原理类似于INDEX/MATCH构造VLOOKUP，但使用条件MAX代替MATCH。

请注意，这假设您的表格从第 1 行开始。如果您的数据从不同的行开始，则需要ROW(...)通过减去顶行和 1 之间的差值来调整部分。

答案2

（这里回答的是，对于排序数据没有单独的问题。）

如果数据是排序后，您可以使用VLOOKUP参数（或range_lookup省略TRUE，因为它是默认值），它在 Excel 中正式描述为“搜索近似匹配”。

换句话说，对于排序后的数据：

  • 设置最后一个参数FALSE返回第一的价值，以及
  • 设置最后一个参数TRUE返回最后的价值。

这在很大程度上没有记录且晦涩难懂，但可以追溯到 VisiCalc (1979)，如今至少在 Microsoft Excel、LibreOffice Calc 和 Google Sheets 中仍然有效。这最终​​归因于LOOKUPVisiCalc 中的初始实现（以及随后的VLOOKUP和HLOOKUP），当时没有第四个参数。该值通过以下方式找到二分查找，使用包含左边界和排他右边界（一种常见且优雅的实现），从而导致这种行为。

从技术上讲，这意味着从候选区间开始搜索[0, n)，其中n是数组的长度，循环不变条件是A[imin] <= key && key < A[imax]（左边界<=目标，右边界从末尾开始，>目标；要验证，请检查之前端点的值，或检查之后的结果），然后依次二分并选择保留此不变量的一侧：通过排除，一侧将保留，直到您获得具有 1 个项的区间，[k, k+1)然后算法返回k。这不必是精确匹配（！）：它只是来自下方的最接近匹配。如果有重复匹配，则返回最后的匹配，因为它要求下一个值是更大而不是键（或数组的末尾）。如果有重复项，你需要一些行为，这是合理且易于实现的。

此行为在旧的 Microsoft 知识库文章（强调添加）中明确说明：“XL：如何返回数组中的第一个或最后一个匹配项”（Q214069)：

您可以使用 LOOKUP() 函数在已排序的数据数组中搜索某个值，并返回另一个数组中该位置所包含的相应值。如果查找值在数组中重复，它返回遇到的最后一个匹配项。此行为对于 VLOOKUP()、HLOOKUP() 和 LOOKUP() 函数均适用。

以下是一些电子表格的官方文档；其中都没有说明“最后匹配”行为，但它在 Google 表格文档中有所暗示：

  • 微软 Excel

    真的假设表中的第一列按数字或字母顺序排序，然后将搜索最接近的值。

  • Google 表格：

    如果is_sorted是TRUE或省略，最接近的匹配（小于或等于返回搜索关键字

答案3

如果搜索数组中的值是连续的（即，您要查找最大值，例如最新日期），则甚至不需要使用 INDIRECT 函数。尝试以下简单代码：

=MAX(IF($A$1:$A$20="c",$B$1:$B$20,)

再次使用 CTRL + SHIFT + ENTER 输入公式

答案4

我尝试了最常用的值。不确定它是否能在 libreOffice 中工作，但它似乎在 excel 中工作

=INDEX($B$2:$B$9,MATCH(MAX(--($A$2:$A$9=D2)*COUNTIFS($B$2:$B$9,$B$2:$B$9,$A$2:$A$9,D2)),--($A$2:$A$9=D2)*COUNTIFS($B$2:$B$9,$B$2:$B$9,$A$2:$A$9,D2),0))

A 列是收款人，B 列是类别，D2 是您要筛选的收款人。我不确定为什么上面的函数中要添加额外的换行符。

查找最后一个单元格的函数如下：

=INDIRECT("B" & MAX(--($A$2:$A$9=D2)*ROW($A$2:$A$9)))

间接让我指定我想要返回的列并直接找到行（所以我不需要减去标题行的数量。

这两个函数都需要使用Ctrl+Shift+回车

相关内容