忘记 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 返回“无效名称”。
我选择了参数 e 和 f 的默认值，因为我想要精确匹配，并且我希望 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 软件包的人。