XLOOKUP函数在Excel中

VLOOKUP和HLOOKUP函数在Excel中具有重要性和受欢迎程度。这种受欢迎的原因之一是，在以前，当用户想要在Excel中查找任何值时，没有可用的选项。对于复杂的公式，他们不得不依赖于INDEX MATCH组合。但现在不用了！

在Excel的新版本中，即Excel 365或Excel 2021中，您不再需要选择上述函数-因为微软引入了一个更强大和稳健的函数，它克服了VLOOKUP的烦人错误和所有缺点， 就是XLOOKUP函数 。

在许多方面，它比其他查找函数更好。它可以垂直和水平查找，向左或向上，具有使用多个条件搜索的能力。

在本教程中，我们将涵盖以下主题：

1. XLOOKUP函数：语法，参数，返回值
2. VLOOKUP与XLOOKUP的区别
3. 如何在Excel中使用XLOOKUP：示例
   • 垂直和水平的XLOOKUP
   • 向左的XLOOKUP
   • 精确匹配和近似匹配的XLOOKUP
   • 使用通配符的XLOOKUP
   • 最后一个匹配的XLOOKUP
   • 返回多个值（行或列）的XLOOKUP
   • 具有多个条件的XLOOKUP
   • 双向XLOOKUP
   • 错误时的XLOOKUP
   • 区分大小写的XLOOKUP
4. XLOOKUP函数不起作用

XLOOKUP函数：语法，参数，返回值

“Excel中的XLOOKUP函数查找一个给定值的范围或数组，并从另一列返回相应的值。”

使用此函数的优点是它可以同时查找表中的位置，即垂直和水平，并且可以执行精确匹配（默认），近似匹配（找到最接近的数据）或通配符匹配（使用通配符字符找到部分匹配）。

语法

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

参数

1. Lookup_value（必需） - 此参数表示要在表格中搜索的值。
2. Lookup_array（必需） - 此参数表示要搜索的表格、范围或数组。
3. Return_array（必需） - 它表示要返回值的范围或数组。
4. If_not_found [可选] - 这是一个可选参数，表示如果找不到匹配项返回的值。如果您跳过了此参数，则返回 #N/A 错误。
5. Match_mode [可选] - 根据您的需求，此参数可以接受四个值；如果您跳过此参数，默认情况下它将取 0 为其值并返回一个精确匹配项。

• 0 - 如果您想要精确匹配，请输入此值。
• -1 - 如果您正在寻找精确匹配项或下一个较小值，请输入此值。如果找不到精确匹配项，则返回下一个较小的值。
• 1 - 如果要返回精确匹配项或下一个较大值，请输入此值。如果找不到精确匹配项，则返回下一个较大的值。
• 2 - 此值表示通配符匹配。
  6. Search_mode [可选] - 此参数帮助用户管理搜索的方向。如果您跳过此参数，默认情况下它将取 1 为其值，并从数组的第一个位置开始搜索到最后一个位置。它可以取以下值：
• 1 – 将此值设置为从定义的数组的第一个位置开始搜索到最后一个位置。

• -1 – 将此值设置为从后向前搜索，即从最后一个位置开始搜索。
• 2 – 如果要进行二进制搜索，其中数据按升序排序，则使用此值。
• -2 – 如果要进行二进制搜索，其中数据按降序排序，则使用此值。

返回值

XLOOKUP 函数在预定义的区域或数组中搜索值，并从另一列返回相关的值。

XLOOKUP 函数的工作原理是什么

为了理解 XLOOKUP 函数的工作原理，我们将利用查找公式来执行精确查找。

例如，我们有5个不同销售人员的姓名和他们的每月销售额。现在使用XLOOKUP，我们必须获取候选人的销售报告。人员的姓名将在单元格B1中表示为lookup_value，现在我们将在我们的lookup_array（A2：A10）中查找这个值，根据位置，它将返回return array（B2：B10）中的值。我们的公式变为：

=XLOOKUP(F3,C2:C11,D2:D11)

没有令人烦恼的列索引号问题，不需要排序，也没有其他困扰。只需一个简单的公式，你就能得到结果！

XLOOKUP与VLOOKUP对比

与传统的VLOOKUP相比，XLOOKUP具有许多优点。下面是一个对比表格，列出了XLOOKUP和VLOOKUP之间的差异：

如何在Excel中实现XLOOKUP功能：示例

示例1：垂直和水平查找

在早期版本的Excel（365之前），如果用户想要进行垂直或水平查找，他们必须为不同的查找类型分别使用两个函数：使用VLOOKUP函数进行垂直查找，使用HLOOKUP函数进行水平查找。

幸运的是，通过Excel 2021或Excel 365，Microsoft引入了XLOOKUP函数，可以使用单个语法执行这两种操作。您只需要提供相应的查找和返回数组即可。

例如，下面是数据样本，给出了ID、医院物品及其可用状态。

应用VLOOKUP:

1. 输入下面的XLOOKUP公式。

Formula Used: =XLOOKUP(G4,B3:B9,C3:C9)

1. 按下回车键。它将从垂直表中查找产品。

HLOOKUP的应用：

1. 键入以下XLOOKUP公式。通过这种方式，我们将水平提供数据数组，而不是垂直提供。

Formula Used: =XLOOKUP(G4,B3:B9,C3:C9)

1. 按下回车键。它将从水平表格中查找产品.

示例2：使用XLOOKUP执行左侧查找

对于早期版本的Excel，没有任何直接的公式可以在数据中向左查找。您必须结合使用Index和Match函数来应用Excel工作表中的左侧查找。但是使用XLOOKUP函数，您不再需要结合使用Index和Match函数。您只需要指定查找表，XLOOKUP将为您处理剩下的任务。

例如，在上面的数据中，我们在左侧添加了另一列List_number。如果给出产品名称，并要求查找List_number，我们将如何操作呢？

我们可以使用XLOOKUP函数来进行左侧查找。按照以下步骤进行左侧查找：

1. 键入XLOOKUP函数并指定其参数。公式如下：

Formula applied: =XLOOKUP (F4,C3:C9,A3:A9)

1. 按下回车键。它将快速应用左侧查找并从List_Number表中获取输出。

使用相同的步骤，您可以为行水平实现左侧查找！

示例3：执行精确匹配和近似匹配

在本教程的开始部分，我们已经介绍了XLOOKUP函数中使用的参数。match_mode参数控制匹配行为。它有四个选项：

• 0 - 如果您想要精确匹配，请输入此值。
• -1 - 如果您要查找精确匹配或下一个较小值，请输入此值。如果找不到精确匹配，它将返回下一个较小的值。
• 1 - 如果要返回精确匹配或下一个较大值，请输入此值。如果找不到精确匹配，它将返回下一个较大的值。
• 2 - 此值表示通配符匹配。

精确匹配XLOOKUP

在大多数情况下，您将在Microsoft Excel中使用精确查找。因此，它是默认属性，如果您在match_mode参数中不传递任何值，隐式地它会采用0。因此，您可以绕过match_mode参数，只提供前三个必需参数。

1. 键入XLOOKUP函数并指定其参数。公式如下：

Formula applied: = =XLOOKUP(F4,C3:C9,B3:B9,"Not found",0)

1. 按下回车按钮。它将快速应用XLOOKUP查找并从表中取回输出。由于’Stop Ca’不在我们的表中，它将返回“未找到”。

大约匹配的XLOOKUP

要执行大约查找匹配，你只需要将match_mode参数设置为-1或1，取决于你想要为较小或较大的数据返回一个大约值。

例如，在这里，我们采用了一个查找表，列出了成绩的下限。因此，当找不到精确匹配时，我们将match_mode设置为1来搜索下一个更大的值：

1. 输入XLOOKUP函数并指定其参数。公式如下：

Formula applied: = =XLOOKUP(F4,C3:C9,B3:B9,"Not found",1)

1. 按下回车键。它将快速应用准确的XLOOKUP查找并从表中获取输出。由于 ‘Stop Ca’ 不在我们的表中，因此它将寻找下一个更大的值，并返回 Stop Cap 的ID号码。

示例4：匹配部分数据（通配符）

XLOOKUP提供了执行部分匹配查找的选项。您只需将match_mode参数设置为2即可。主要地，大多数用户更喜欢使用以下通配符字符：

1. 星号 (*) - 这个通配符字符用于定义字符的顺序。
2. 问号 (?) - 这个通配符字符用于定义任意单一字符。

让我们使用一个实时示例来实现部分匹配。

上面的表格列出了手机的特点、存储空间、价格、显示屏等信息。您对某款智能手机的价格感兴趣。唯一的问题是您只知道手机的名称，而不知道完整的型号名称，就像在A列中显示的那样。解决办法是输入手机名称，并用通配符替换前面和后面的字符。

假设我们想获取三星 Formula 的价格信息，我们将使用以下带有通配符字符的公式，并将 match_mode 参数设置为2：

Formula Applied: =XLOOKUP("*"&C10&"*",A3:A7,B3:B7,,2)

上述公式将返回表格中部分匹配Samsung后的定价:

注意：您还可以直接在双引号（””）之间传递电话名称，而不是单元格引用。

Formula Applied: =XLOOKUP("*samsung*",A3:A7,B3:B7,,2)

示例5：倒序工作以获取最后一次出现

有时，您的查找表中包含多个查找值的实例。 默认情况下，XLOOKUP公式获取查找值的第一次出现的匹配输出。 但是如果您想返回最后一次匹配怎么办？ 要完成此操作，您所需做的就是将XLOOKUP公式设置为以相反的方向搜索。

您可以通过名为search_mode的第6个参数来控制搜索的顺序：

• 1（默认值）- 如果您想将搜索从第一个值设置为最后一个值，请输入此参数。
• -1 - 如果您想按相反的顺序设置搜索，或者想获取最后一个到第一个值，请使用此参数。

例如，我们将实施XLOOKUP公式以返回产品“STERI SET”的最后一次销售值。 与上述公式不同，我们将前三个必需的参数（lookup_value参数为E3，lookup_array为B2：B11，return_array参数为C2：D11）放在一起，然后将第5个参数设置为-1。

Formula Applied = XLOOKUP(E3,B2:B11,C2:C11,,,-1)

结果将搜索lookup_value “STERI SET” 的最后一个出现位置，并返回最低的定价。

例6：返回多列或多行信息

你知道XLOOKUP函数不仅可以返回单个值吗？使用这个公式，你可以返回与相同查找值相关联的多个值。而要实现这个功能，你只需要输入前三个必需参数，不需要任何额外的操作！

举个例子，假设你从上述智能手机数据表中被要求获取一个特定手机的所有详细信息。实现这个功能的公式非常简单。你所需做的只是将整个范围（B1：G6）作为return_array参数输入：

Formula Applied = XLOOKUP(C9,A1:A6,B1:G6)

您只需要输入公式，Ms. Excel将自动将输出延伸到相邻的空白单元格中。在我们的情况下，它返回了所有六列。参考以下输出：

使用Transpose函数，您还可以将上述输出垂直返回到列中。将XLOOKUP嵌套到Transpose中将自动垂直翻转数组。

Formula Applied = TRANSPOSE(XLOOKUP(C9,A1:A6,B1:G6))

你将会得到以下的输出：

注意：在使用此公式之前，请确保在公式单元格的右侧或下方有足够的空单元格。因为多个值会调整到相邻的单元格，如果Excel发现任何数据，它将返回一个#SPILL!错误。

示例7：XLOOKUP多个条件

XLOOKUP的另一个令人惊奇的功能是它能够原生地处理数组。由于有了这个功能，它可以直接在lookup_array参数中处理多个条件。按照下面的语法来处理多个条件：

XLOOKUP (1, (criteria_range1=criteria1) * (criteria_range2=criteria2) * (...), return_array)

每个标准的输出是一个包含TRUE或FALSE值的数组。但是由于我们正在将这些数组相乘，TRUE或FALSE布尔值分别被转换为1和0，从而生成最终的查找数组。根据基本的数学规则，如果你将任何东西与0相乘，输出将始终为零；因此，在查找表中，只有满足所有标准的项目被表示为1。

您可以在下表中检查，我们给出了三个要检查的标准。根据这些标准，我们需要确定从E2:E11（return_array）中的销售额。

我们将应用以下三个标准：

1. 标准1（销售人员）= H2
2. 标准2（目录号）= H3
3. 标准3（产品）= H4

公式的形式如下：

Formula Applied = XLOOKUP(1,(D2:D11=H2)*(A2:A11=H3)*(B2:B11=H4),E2:E11)

作为结果，XLOOKUP函数将处理所有的条件，如果每个条件被满足，它将在返回数组中查找输出。

示例8：双向（两个方向）XLOOKUP

双向或两个方向的查找通常用于查找给定行和列的交叉值。令人惊讶的是，Excel XLOOKUP也可以做到这一点！它只需要将XLOOKUP嵌套到另一个中，你就会得到以下的语法：

XLOOKUP(lookup_value1, lookup_array1, XLOOKUP(lookup_value2, lookup_array2, data_values))

对于这个例子，我们有三个月的销售报告，即S1、S2、S3。在这里，我们将找到特定销售人员在S3季度内的销售报告。为了解决这个问题，我们将使用以下公式的双向Xlookup：

Formula Applied=XLOOKUP(I2,C2:C11,XLOOKUP(I3,D1:F1,D2:F11))

示例9：区分大小写的 XLOOKUP 函数

在上面的示例中，您可能已经注意到 XLOOKUP 函数将小写字母和大写字母视为相同。默认情况下，此函数是区分大小写的。要使 XLOOKUP 区分大小写，我们将使用 EXACT 函数替换参数 lookup_array。请使用以下语法：

XLOOKUP(TRUE, EXACT(lookup_value, lookup_array), return_array)

正如其名称所示，EXACT函数的作用是将给定的查找值与数组中的每个值进行比较；如果找到匹配（包括字母大小写），则返回TRUE。现在，XLOOKUP将其查找值与其查找数组进行匹配，如果两者都为TRUE，则在给定的数组中搜索TRUE并从返回数组中返回匹配项。

假设我们想要获取产品STERI SET的销售数据，并进行区分大小写的匹配，我们将使用以下公式：

Formula Applied: =XLOOKUP(TRUE, EXACT(E3, B2:B11), C2:C11, "Not in the list!")

由于找到了一个完全匹配，它将从返回数组中返回销售值，

注意。如果您的查找数组包含两个或更多相同的数据，XLOOKUP函数将返回第一个匹配值。

示例10：如果错误XLOOKUP

XLOOKUP函数在找不到查找值时返回＃N/ A错误。在专业使用公式时，＃N/ A错误看起来不太好，甚至可能会让许多中级Excel用户感到困惑。因此，建议捕捉错误并编写用户友好的注释。

例如，让我们考虑一种没有找到匹配项的情况。

结果是，你得到了一个#N/A错误。要用你自定义的消息替换错误，你需要在第四个参数中添加一个文本。

Formula Applied: = =XLOOKUP(F3,C2:C11,D2:D11,"Invalid Name")

例如，如果有人输入了一个无效的产品名称，我们的公式将返回“无效名称”消息。

XLOOKUP函数不起作用

很多时候，您的Excel XLOOKUP公式无法正常工作，经常会返回错误。这主要是由于以下原因之一：

1. 您正在使用先前的Excel版本的XLOOKUP。

XLOOKUP函数的唯一限制是它不向后兼容，只能在Excel for Microsoft 365和Excel 2021中使用，不能在较早的版本中使用。

2. Excel XLOOKUP返回错误的输出

有时，这个Excel函数可能会返回错误的输出。常见原因是您输入了错误的数据值，或者复制公式到单元格时返回数组范围可能已经发生了偏移。您可以使用绝对单元格引用（例如，A1:A20）来防止这种情况发生。在两个范围中都使用绝对引用，将会锁定数组范围并返回正确的输出。

3. XLOOKUP函数返回#N/A错误

如果XLOOKUP函数无法从查找表中找到查找值，它将返回#N/A错误。然而，您可以用自定义的消息替换该错误。

4. XLOOKUP函数返回#VALUE错误

如果查找数组和返回数组具有不兼容的维度，则会出现#VALUE!错误。例如，如果您在参数中水平提供了一个lookup_array，并且想要返回一个垂直的return_array中的值。

5. XLOOKUP函数返回#REF错误

当在两个不同的工作簿之间查找数据时，如果其中一个工作簿已关闭，此函数将抛出#REF!错误。要修复该错误，简单地打开这两个文件。