网站搜索

忘记 Excel 中的 VLOOKUP:这就是我使用 XLOOKUP 的原因


我将使用直接单元格引用来举例说明我在本文中的观点,因为它们比结构化引用更清晰。我也不会明确谈论 HLOOKUP,因为除了它的方向之外,它的工作方式与 VLOOKUP 完全相同。

语法:XLOOKUP 和 VLOOKUP

在解释为什么我更喜欢 XLOOKUP 而不是 VLOOKUP 之前,我将向您展示它们的工作原理。

X查找

XLOOKUP 有六个参数:

=XLOOKUP(a,b,c,d,e,f)

在哪里

  • a(必需)是查找值,
  • b(必需)是查找数组,
  • c(必需)是返回数组,
  • d(可选)是在查找数组 (b) 中未找到查找值 (a) 时返回的文本,
  • e(可选)是匹配模式,并且
  • f(可选)是搜索模式。

在此示例中,我希望 Excel 根据单元格 H1 中的姓名查找员工 ID,并将结果返回到单元格 H2。

为此,我将在单元格 H2 中输入以下公式:

=XLOOKUP(H1,B2:B12,A2:A12,"Invalid name",0,1)

在本例中,单元格 H1 包含 Excel 需要查找的值(玛丽),B2 到 B12 是可以找到该值的位置(员工姓名),A2 到 A12 是从中提取相应结果的位置(员工 ID) ),如果在查找数组中找不到查找值,我希望 Excel 返回“无效名称”。

我选择了参数 ef 的默认值,因为我想要精确匹配,并且我希望 Excel 从查找数组的顶部进行搜索(更多信息请参见这些稍后)。

查找表

这是 VLOOKUP 语法,它有四个参数:

=VLOOKUP(a,b,c,d)

在哪里

  • a(必需)是查找值,
  • b(必需)是查找和返回数组,
  • c(必需)是列索引号,并且
  • d(可选)是匹配模式。

在此示例中,我希望 Excel 根据单元格 H4 中的员工 ID 查找国籍,并将结果返回到单元格 H5。

为此,我将在单元格 H5 中输入

=VLOOKUP(H4,A2:E12,5,FALSE)

因为单元格 H4 包含查找值(ID 3264),所以单元格 A2 到 E12 是 Excel 需要查找该值和相应返回值的位置,第五列(国家/地区)是在其中找到结果的数组,我想要完全匹配 (FALSE)。

无需计算列数

VLOOKUP 和 XLOOKUP 之间的一个关键区别是,前者要求我指定可以找到结果的列索引号,而后者则不需要。这是因为 VLOOKUP 将查找数组和返回数组组合到一个参数中,而 XLOOKUP 将它们定义在两个单独的参数中。

查找表

必须在 VLOOKUP 中指定列索引号可能会导致几个问题:

  • 很容易意外地错误计算列数,尤其是当您的查找数组有数百列时。
  • 添加或删除列可能会影响列索引号的准确性。
  • 查找数组必须位于最左列,返回数组必须位于右列。这限制了VLOOKUP的多功能性。

X查找

另一方面,XLOOKUP 包含查找数组作为单独的参数,这意味着您可以享受以下好处:

  • 不涉及计数!当您到达公式的该部分时,只需使用鼠标选择返回数组即可。这可以节省时间并提高准确性。
  • 由于返回数组位于指定的单元格范围内,因此在电子表格中删除或添加列不会影响您的 XLOOKUP 公式。
  • 返回数组可以位于查找数组的任一侧,这意味着 XLOOKUP 比 VLOOKUP 更通用。

更多近似匹配选项

VLOOKUP 和 XLOOKUP 都可以返回精确匹配(行中精确对应的值)和近似匹配(附近对应的值)。

查找表

更具体地说,VLOOKUP 的近似匹配(在公式中用 TRUE 表示)向下搜索查找数组,直到找到大于查找值的值。然后它返回从那里向上一行的值。

在下面的例子中,学生 D 的成绩为 65 分。 VLOOKUP 采用查找值 65,检查查找数组,找到第一个大于查找值的值(在本例中为 70),然后返回上一行的成绩(C 级)。

这存在两个缺点。首先,查找数组必须按升序列出。其次,我必须向查找数组添加一个 FAIL 行,因为我在 VLOOKUP 中没有选项来声明不匹配的参数。

X查找

另一方面,XLOOKUP 提供了 VLOOKUP 一维近似匹配的三种替代方案:

  • -1:如果没有完全匹配,则返回查找数组中的下一个最小值。
  • 1:如果没有完全匹配,则返回查找数组中的下一个最大值。
  • 2:这使用通配符来允许更灵活的查找。

还是以D同学为例。如果分数为 65,Excel 将看到查找数组中的下一个最小值是 60,下一个最大值是 70。由于学生尚未达到 B 级的阈值 (70),我需要 Excel 来取查找数组 (60) 中的下一个最小值,从返回数组中返回 C 级。因此,我将输入 -1 作为公式中的匹配选项。

这意味着查找数组不必按顺序排列 - 如果不完全匹配,Excel 会搜索整个查找数组以查找最接近的较高或较低值。我还可以从查找数组中省略 FAIL 行,因为如果学生的分数与任何成绩都不匹配,我可以使用 XLOOKUP 语法中的第四个参数返回单词 FAIL。

更多搜索模式(方向)

VLOOKUP 从第一个到最后一个搜索,返回第一个匹配值,而 XLOOKUP 提供四种搜索选项。

查找表

在大多数查找场景中,从第一个到最后一个搜索查找数组将返回您需要的结果。例如,如果您有电话号码和人员姓名的目录,则使用 VLOOKUP 根据您输入的姓名查找电话号码就可以了,因为该人的姓名可能只出现一次。

X查找

但是,XLOOKUP 允许您选择搜索方向:

  • 1:从头到尾搜索
  • -1:从最后到第一个搜索
  • 2:二分查找(查找数组按升序排列)
  • -2 二分查找(查找数组按降序排列)

从后到先查找的好处是,您可以在按日期顺序列出的查找数组中找到最近出现的值。要使用 VLOOKUP 实现此目的,您必须首先反转数据的顺序。

定义错误输出

XLOOKUP 的一个真正有用的功能是“如果未找到参数”,这是 VLOOKUP 所没有的。

查找表

如果在完全匹配的 VLOOKUP 公式中找不到某个值,Excel 将返回可怕的 #N/A 错误消息。为了解决这个问题,我始终将 VLOOKUP 函数嵌入到 IFERROR 函数中,以便在 VLOOKUP 找不到匹配项时可以定义输出。

=IFERROR(VLOOKUP(B6,$E$2:$F$8,2,TRUE)," ")

虽然这是一个合理的解决方案,但它使公式编写变得更加复杂,并且可能隐藏可能影响数据分析准确性的问题。

X查找

由于 XLOOKUP 已准备好“如果未找到”参数,因此您可以定义如果该值未出现在查找中会发生什么情况,从而使您不必将公式嵌入 IFERROR 中。

返回溢出的数组

VLOOKUP 最顽固的属性之一是它只能返回单个匹配项,而 XLOOKUP 可以返回一个范围。

X查找

在此示例中,输入

=XLOOKUP(I1,A2:A7,B2:F7)

在单元格 I1 中查找值(在本例中为 Quizpicable Me),在单元格 A2 到 A7 中查找该值,并以溢出数组的形式返回所有相应的值。

查找表

如果我尝试使用 VLOOKUP 复制此内容,我会输入

=VLOOKUP(I1,A2:F7,2:6)

但这会返回#REF!错误,因为第三个参数(列索引号)只能是单个数字,而不是范围。这意味着 XLOOKUP 的适应性更强,因为它可以返回单个值或范围,具体取决于您添加到公式中的参数。


由于 Excel 2019 之前的版本不支持 XLOOKUP,因此不要完全放弃 VLOOKUP 和 HLOOKUP!在某些情况下,您可能仍然需要使用它们,例如您要将电子表格发送给几年没有升级 Office 软件包的人。

相关文章