☰

增强型 Vlookup Excel 公式

tag-icon • tag-icon microsoft-excel vlookup
增强型 Vlookup Excel 公式

我正在寻找一个 excel 公式，可以用来自动填充下面屏幕截图中的 E2。目的是从 B 列中找出 Marc 的年龄，即 E2 应该是 20 岁。有什么想法吗？我试过 Match、VLookup 和许多其他函数，但都没有成功。

在此处输入图片描述

答案1

这几乎可行：

{=INDEX(B$2:B$3, MATCH(1, (SEARCH(D2, A$2:A$3)>0)*1, 0), 1)}

也就是说，在 A 范围的每个单元格中搜索 D2，对找到的每个单元格返回 1，精确匹配 1 以返回成功的行，然后使用返回的行索引 B 范围，并从该范围的第一列获取年龄。这假设匹配将仅返回一行。使用 Ctrl-Shift-Enter 获取表示数组模式的 {}。

由于我们正在寻找的名称可能是另一个名称的前缀，因此我们应该在找到该名称的位置后寻找一个单词分隔符（空格或字符串结尾）len（名称）个字符：

{=INDEX(B$2:B$3,
        MATCH(0,
              LEN(SUBSTITUTE(MID(A$2:A$3,LEN(D2)+SEARCH(D2,A$2:A$3),1),
                             " ",
                             "")),
              0),
        1)}

我们应该在开始也：

{=INDEX(B$2:B$3,MATCH(0,
   LEN(SUBSTITUTE(IFERROR(MID(A$2:A$3,SEARCH(D2,A$2:A$3)-1,1)," ")," ",""))+
   LEN(SUBSTITUTE(MID(A$2:A$3,LEN(D2)+SEARCH(D2,A$2:A$3),1),       " ","")),
   0),1)}

答案2

由于您也要求使用 VBA 答案。我已经设置了一个像您这样的示例：

在此处输入图片描述

使用以下宏我写道：

Public Sub do_stuff_and_things()

Dim i As Integer
Dim name_to_find As String
Dim age As String

name_to_find = Range("'Sheet1'!D2").Value


age = "not found"

i = 2

Do Until i = Range("'Sheet1'!A1").End(xlDown).Row + 1

 If InStr(Range("'Sheet1'!A" & i).Value, name_to_find) Then
 age = Range("'Sheet1'!B" & i).Value
 Exit Do
 End If

 i = i + 1
Loop


Range("'Sheet1'!E2").Value = age
End Sub

它只是使用 InStr 函数来查看字段是否包含您的搜索值。当然，这要求名称的输入方式与搜索列中的名称完全一致。

希望这可以帮助。

如果您需要任何帮助来实现这一点，请一定要告诉我。

答案3

您可以使用带通配符的 vlookup：

=VLOOKUP("*" & D2 & "*",$A$2:$B$3,2,FALSE)

其中 D2 是您想要查找的单元格（也可以硬编码为“Marc”），$A$2:$B$3 是您的范围，其中每列和行引用前面都包含 $，这将使范围成为静态的，因为如果您将此公式拖到列的下方，您可能不希望范围发生变化。

2 是您想要在范围内检索的列索引，false 表示这是一个近似值，当与通配符 * 结合使用时，将查找其中包含 Marc 的任何值。xyzMarc123 将返回一个值。

答案4

以下工作：

=VLOOKUP("*?", IFERROR( IF( SEARCH(C2, A2:A3), A2:B3, 0), ""), 2, FALSE)

并且仅使用当时可用的功能和能力。

唯一奇怪的是VLOOKUP(): 的查找值"*?"。我永远无法（包括在这里）让查找*成功使用裸字符。如果字符串中有前一个或后一个字符（例如，a*或*a），查找总是乐意这样做，但绝不会使用裸星号。

我还想说，我发现很奇怪，我见过的每个关于这个主题的帮助网站……不知何故……都没有在示例中使用星号。不过，好的一面是，我想说，也许他们都没有想到过这种用法的可能用途，或者不那么好的一面是，由于他们似乎都使用相同的示例集，或者使用与较大示例集不相符的缩写示例集，我实际上只看到过一个帮助说明，很可能是由第一个向 MVP 世界写“大家好……”的人写的。

无论如何，一旦在裸星号上添加某些内容，它就会像魔法一样起作用，因此?我将它添加到其中，进行看似相同的查找，但一个有效，另一个无效。

不过，我没有尝试过这种方法。如今（2022 年），也许类似的东西FILTER()可能更优雅，尽管公式编辑器中可能占用了同样多的空间。它肯定具有能够处理查找值存在多个实例的优势，因此允许更深入地查看数据。

相关内容