如何使用 Excel 中的全新 & 高级 XLOOKUP 函数（10 个示例）

Excel 的新 XLOOKUP 是 Excel 提供的最强大且最简单的查找函数。经过不懈努力，微软终于发布了这个函数来替代 VLOOKUP、HLOOKUP、INDEX+MATCH 和其他查找函数。

在本教程中，我们将向您展示 XLOOKUP 的优势是什么，以及如何获取并应用它来解决不同的查找问题。

如何获取 XLOOKUP？

语法

示例

• 示例1：精确匹配
  • 使用 XLOOKUP 执行精确匹配
  • 只需点击几下即可获得精确匹配

• 示例2：近似匹配
  • 使用 XLOOKUP 执行近似匹配

• 示例3：通配符匹配
  • 使用 XLOOKUP 执行通配符匹配

• 示例4：从右到左查找
  • 使用 XLOOKUP 从右到左查找
  • 只需点击几下即可从右到左查找值

• 示例5：垂直或水平查找
  • 使用 XLOOKUP 执行垂直或水平查找

• 示例6：双向查找
  • 使用 XLOOKUP 执行双向查找

• 示例7：自定义未找到消息
  • 使用 XLOOKUP 自定义未找到消息
  • 使用便捷功能自定义 #N/A 错误

• 示例8：多个值
  • 使用 XLOOKUP 返回多个值

• 示例9：多条件
  • 使用 XLOOKUP 执行多条件查找
  • 使用快速方法进行多条件查找

• 示例10：查找最后匹配的值
  • 使用 XLOOKUP 获取最后匹配的结果
  • 使用惊人的工具轻松查找最后匹配的值

下载 XLOOKUP 示例文件

如何获取 XLOOKUP？

由于 XLOOKUP 函数仅在 Microsoft365 的 Excel、Excel2021及更高版本和 Excel for the web 中可用。如果您使用的是 Excel2019 或更早版本，请考虑升级以访问 XLOOKUP。

语法

查找一个区域或数组，然后返回第一个匹配结果的值。语法如下：

参数：

1. Lookup_value（必需）：您要查找的值。它可以位于表格区域的任何列中。
2. Lookup_array（必需）：您搜索查找值的数组或区域。
3. Return_array（必需）：您希望获取值的数组或区域。
4. If_not_found（可选）：当未找到有效匹配时返回的值。您可以自定义 [if_not_found] 中的文本以显示没有匹配项。否则，返回值将默认为 #N/A。
5. Match_mode（可选）：在此处您可以指定如何将 lookup_value 与 lookup_array 中的值进行匹配。
   • 0（默认）= 精确匹配。如果未找到匹配项，则返回 #N/A。
   • -1 = 精确匹配。如果未找到匹配项，则返回下一个较小的值。
   • 1 = 精确匹配。如果未找到匹配项，则返回下一个较大的值。
   • 2 = 部分匹配。使用通配符字符如 *、? 和 ~ 来执行通配符匹配。
6. Search_mode（可选）：在此处您可以指定执行搜索的顺序。
   • 1（默认）= 从 lookup_array 中的第一个项目到最后一个项目搜索 lookup_value。
   • -1 = 从最后一个项目到第一个项目搜索 lookup_value。当您需要在 lookup_array 中获取最后一个匹配结果时，这很有帮助。
   • 2 = 执行需要 lookup_array 按升序排序的二进制搜索。如果未排序，返回结果将无效。
   • -2 = 执行需要 lookup_array 按降序排序的二进制搜索。如果未排序，返回结果将无效。

有关参数的详细信息，请按以下步骤操作：

1. 在空单元格中输入以下语法，请注意您只需输入括号的一侧。

2. 按 Ctrl+A，然后会弹出一个显示函数参数的提示框。括号的另一侧会自动完成。

3. 下拉数据面板，然后您可以看到 XLOOKUP 的所有六个函数参数。

>>>

示例

我相信您现在已经掌握了 XLOOKUP 的基本原理。让我们直接进入 XLOOKUP 的实际示例。

示例1：精确匹配

使用 XLOOKUP 执行精确匹配

您是否曾因为每次使用 VLOOKUP 时都必须指定精确匹配模式而感到沮丧？幸运的是，当您尝试惊人的 XLOOKUP 函数时，这个麻烦不再存在。默认情况下，XLOOKUP生成精确匹配。

现在，假设您有一个办公用品库存列表，并且您需要知道某个项目的单价，比如鼠标，请按以下步骤操作。

在空白单元格 F2 中输入以下公式，然后按 Enter 键获取结果。

=XLOOKUP(E2,A2:A10,C2:C10)

现在您知道了使用高级 XLOOKUP公式的鼠标单价。由于匹配代码默认设置为精确匹配，您无需指定它。比 VLOOKUP 更简单高效。

只需点击几下即可获得精确匹配

也许您正在使用较低版本的 Excel，并且尚无计划升级到 Excel2021 或 Microsoft365。在这种情况下，我将推荐一个便捷功能 - "在区域中查找数据"。 使用此功能，您无需复杂的公式或访问 XLOOKUP 即可获得结果。

1. 点击单元格以放置匹配结果。

2. 转到 "Kutools"选项卡，点击 "公式助手"，然后在下拉列表中点击 "公式助手"。

3. 在公式助手对话框中，请按以下步骤配置：

• 在 "公式类型" 部分选择 "查找"；
• 在 "选择一个公式" 部分选择 "在区域中查找数据"；
• 在 "参数输入" 部分，请按以下步骤操作：
  • 在 "表格区域" 框中，选择包含查找值和结果值的数据区域；
  • 在 "查找的值" 框中，选择您要查找的值的单元格或区域。请注意，它必须在表格区域的第一列中；
  • 在 "列" 框中，选择您将返回匹配值的列。

4. 点击确定按钮以获得结果。

示例2：近似匹配

使用 XLOOKUP 执行近似匹配

要运行近似查找，您需要在第五个参数中将匹配模式设置为1 或 -1。当未找到精确匹配时，它会返回下一个较大或较小的值。

在这种情况下，您需要知道员工收入的税率。电子表格左侧是2021 年联邦所得税等级。您如何获取列 E 中员工的税率？别担心。请按以下步骤操作：

1. 在空白单元格 E2 中输入以下公式，然后按 Enter 键获取结果。然后根据需要更改返回结果的格式。

=XLOOKUP(D2,B2:B8,A2:A8,,1)

>>>

√ 注意：第四个参数 [If_not_found] 是可选的，所以我省略了它。

2.现在您知道了单元格 D2 的税率。要获得其余结果，您需要将 lookup_array 和 return_array 的单元格引用转换为绝对引用。

• 双击单元格 E2以显示公式 =XLOOKUP(D2,B2:B8,A2:A8,,1)；
• 在公式中选择查找范围 B2:B8，按 F4 键获取 $B$2:$B$8；
• 在公式中选择返回范围 A2:A8，按 F4 键获取 $A$2:$A$8；
• 按 Enter 按钮获取单元格 E2 的结果。

>>>

3. 然后向下拖动填充柄以获得所有结果。

√ 注意：

• 按键盘上的 F4 键可以通过在行和列前添加美元符号来将单元格引用更改为绝对引用。
• 在对查找和返回范围应用绝对引用后，我们将单元格 E2 中的公式更改为此版本：

=XLOOKUP(D2,$B$2:$B$8,$A$2:$A$8,,1)

• 当您从单元格 E2 向下拖动填充柄时，E 列中每个单元格的公式仅在查找值方面发生变化。例如，E13 中的公式现在变成了这样：

=XLOOKUP(D13,$B$2:$B$8,$A$2:$A$8,,1)

示例3：通配符匹配

使用 XLOOKUP 执行通配符匹配

在我们深入了解 XLOOKUP 通配符匹配功能之前，让我们先看看什么是通配符。

在 Microsoft Excel 中，通配符是一种特殊字符，可以替换任何字符。当您想进行部分匹配查找时，它特别有用。

通配符有三种类型：星号 (*)、问号 (?) 和波浪号 (~)。

• 星号 (*)代表文本中的任意数量的字符；
• 问号 (?)代表文本中的任意单个字符；
• 波浪号 (~) 用于将通配符 (*, ? ~) 转换为文字字符。在通配符前放置波浪号 (~)以实现此功能；

在大多数情况下，当我们执行 XLOOKUP 通配符匹配功能时，我们使用星号 (*) 字符。现在让我们看看通配符匹配是如何工作的。

假设您有一个美国50 家最大公司的股票市值列表，您想知道一些公司的市值，但公司名称是缩写的，这是通配符匹配的完美场景。请跟随我一步步完成这个技巧。

√ 注意：要执行通配符匹配，最重要的是将第五个参数 [match_mode] 设置为2。

1. 在空白单元格 H3 中输入以下公式，然后按 Enter 键获取结果。

=XLOOKUP("*"&G3&"*",B3:B52,D3:D52,,2)

>>>

2.现在您知道了单元格 H3 的结果。要获得其余结果，您需要通过将光标放在数组中并按 F4 键来固定 lookup_array 和 return_array。然后 H3 中的公式变为：

=XLOOKUP("*"&G3&"*",$B$3:$B$52,$D$3:$D$52,,2)

3. 向下拖动填充柄以获得所有结果。

√ 注意：

• 单元格 H3 中公式的 lookup_value 是 "*"&G3&"*"。我们使用 & 运算符将星号通配符 (*) 与值 G3连接起来。
• 第四个参数 [If_not_found] 是可选的，所以我省略了它。

示例4：向左查找

使用 XLOOKUP 从右到左查找

VLOOKUP 的一个缺点是它只能执行查找列右侧的查找。如果您尝试查找查找列左侧的值，您将得到 #N/A 错误。别担心。XLOOKUP 是解决此问题的完美查找函数。

XLOOKUP 被设计为查找查找列左侧或右侧的值。它没有限制，满足 Excel 用户的需求。在下面的示例中，我们将向您展示这个技巧。

假设您有一个国家和电话代码的列表，您想根据已知的电话代码查找国家名称。

我们需要查找列 C 并返回列 A 中的值。请按以下步骤操作：

1. 在空白单元格 G2 中输入以下公式。

=XLOOKUP(F2,C2:C11,A2:A11)

2. 按 Enter 键获取结果。

√ 注意：XLOOKUP 向左查找功能可以替代 Index 和 Match 来查找左侧的值。

只需点击几下即可从右到左查找值

对于那些不想记住公式的人，我将推荐一个有用的功能 - "从右到左查找"。使用此功能，您可以在几秒钟内从右到左执行查找。

1. 转到 Excel 中的 "Kutools"选项卡，找到 "高级 LOOKUP"，然后在下拉列表中点击 "从右到左查找"。

2. 在 "从右到左查找" 对话框中，您需要按以下步骤配置：

• 在 "待检索值与输出结果区域" 部分，指定查找范围和输出范围；
• 在 "数据区域" 部分，输入数据范围，然后指定 "关键列" 和 "返回列"；

3. 点击确定按钮以获得结果。

示例5：垂直或水平查找

使用 XLOOKUP 执行垂直或水平查找

作为 Excel 用户，您可能熟悉 VLOOKUP 和 HLOOKUP 函数。VLOOKUP 是在列中垂直查找，HLOOKUP 是在行中水平查找。

现在新的 XLOOKUP 将它们结合在一起，这意味着您只需使用一个语法即可执行垂直查找或水平查找。是不是很聪明？

在下面的示例中，我们将说明如何仅使用一个 XLOOKUP语法来垂直或水平查找。

要执行垂直查找，请在空白单元格 E2 中输入以下公式，按 Enter 键获取结果。

=XLOOKUP(E1,A2:A13,B2:B13)

要执行水平查找，请在空白单元格 P2 中输入以下公式，按 Enter 键获取结果。

=XLOOKUP(P1,B1:M1,B2:M2)

如您所见，语法是相同的。这两个公式之间的唯一区别是垂直查找时输入列，而水平查找时输入行。

示例6：双向查找

使用 XLOOKUP 执行双向查找

您还在使用 INDEX 和 MATCH 函数在二维表中查找值吗？试试改进的 XLOOKUP 更轻松地完成工作。

XLOOKUP 可以执行双重查找，找到两个值的交集。通过将一个 XLOOKUP 嵌套在另一个 XLOOKUP 中，内部 XLOOKUP 可以返回整行或整列，然后将此返回的行或列输入到外部 XLOOKUP作为返回数组。

假设您有一个学生成绩列表，包含不同学科，您想知道 Kim 的化学成绩。

让我们看看如何使用神奇的 XLOOKUP 来完成这个技巧。

• 我们运行“内部”XLOOKUP 来返回整列的值。XLOOKUP(H2,B1:E1,B2:E10) 可以获取化学成绩的区域。
• 我们通过将“内部”XLOOKUP作为完整公式中的 return_array 来将其嵌套在“外部”XLOOKUP 中。
• 然后这里是最终公式：

=XLOOKUP(H1,A2:A10,XLOOKUP(H2,B1:E1,B2:E10))

• 在空白单元格 H3 中输入上述公式，按 Enter 按钮获取结果。

或者您可以反过来使用“内部”XLOOKUP 返回整行的值，这些值是 Kim 的所有学科成绩。然后使用“外部”XLOOKUP 在 Kim 的所有学科成绩中查找化学成绩。

• 在空白单元格 H4 中输入以下公式，然后按 Enter 按钮获取结果。

=XLOOKUP(H2,B1:E1,XLOOKUP(H1,A2:A10,B2:E10))

XLOOKUP 的双向查找功能也是其垂直和水平查找功能的完美说明。如果您愿意，可以试试！

示例7：自定义未找到消息

使用 XLOOKUP 自定义未找到消息

就像其他查找函数一样，当找不到匹配项时，将返回 #N/A 错误消息。对于某些 Excel 用户来说，这可能会造成困惑。但好消息是 XLOOKUP 函数的第四个参数提供了错误处理。

使用内置的 [if_not_found] 参数，您可以指定自定义消息来替换 #N/A结果。在可选的第四个参数中输入您需要的文本，并将文本用双引号（"）括起来。

例如，城市丹佛未找到，因此 XLOOKUP 返回 #N/A 错误消息。但在我们用文本“未匹配”自定义第四个参数后，公式将显示“未匹配”文本而不是错误消息。

在空白单元格 F3 中输入以下公式，然后按 Enter 按钮获取结果。

=XLOOKUP(E2,A2:A11,C2:C11,"未匹配")

使用便捷功能自定义 #N/A 错误

要快速用自定义消息覆盖 #N/A 错误，Kutools for Excel 是 Excel 中的完美工具。通过其内置的用空白或特定值替换0 或 #N/A 功能，您可以在不使用复杂公式或访问 XLOOKUP 的情况下指定未找到的消息。

1. 转到 Excel 中的 "Kutools"选项卡，找到 "高级 LOOKUP"，然后在下拉列表中点击 "用空白或特定值替换0 或 #N/A"。

2. 在 "用空白或特定值替换0 或 #N/A" 对话框中，您需要按以下步骤配置：

• 在 "待检索值与输出结果区域" 部分，选择查找范围和输出范围；
• 然后选择 "用特定值替换0 或 #N/A"选项，输入您喜欢的文本；
• 在 "数据区域" 部分，选择数据范围，然后指定 "关键列" 和 "返回列"。

3. 点击确定按钮以获得结果。当找不到匹配项时，将显示自定义消息。

示例8：多个值

使用 XLOOKUP 返回多个值

XLOOKUP 的另一个优点是能够同时返回多个值。输入一个公式以获取第一个结果，然后其他返回的值会自动溢出到相邻的空白单元格中。

在下面的示例中，您想获取有关学生 ID “FG9940005”的所有信息。诀窍是在公式中提供一个范围作为 return_array，而不是单个列或行。在这种情况下，返回数组范围是 B2:D9，包括三列。

在空白单元格 G2 中输入以下公式，按 Enter 键获取所有结果。

=XLOOKUP(F2,A2:A9,B2:D9)

所有结果单元格显示相同的公式。您可以在第一个单元格中编辑或修改公式，但在其他单元格中，公式不可编辑。您可以看到编辑栏是灰色的，表示无法对其进行更改。

总之，XLOOKUP 的多值功能是对 VLOOKUP 的有用改进。您无需为每个公式单独指定每个列号。赞！

示例9：多条件

使用 XLOOKUP 执行多条件查找

XLOOKUP 的另一个惊人的新功能是能够使用多个条件进行查找。诀窍是在公式中分别使用 "&" 运算符连接查找值和查找数组。让我们通过下面的示例来说明。

我们需要知道中等蓝色花瓶的价格。在这种情况下，需要三个查找值（条件）来查找匹配项。在空白单元格 I2 中输入以下公式，然后按 Enter 键获取结果。

=XLOOKUP(F2&G2&H2,A2:A12&B2:B12&C2:C12,D2:D12)

√ 注意：XLOOKUP 可以直接处理数组。无需使用 Control + Shift + Enter 确认公式。

使用快速方法进行多条件查找

在 Excel 中是否有比 XLOOKUP 更快更简单的方法来执行多条件查找？Kutools for Excel 提供了一个惊人的功能 - "多条件查找"。使用此功能，您只需点击几下即可运行多条件查找！

1. 转到 Excel 中的 "Kutools"选项卡，找到 "高级 LOOKUP"，然后在下拉列表中点击 "多条件查找"。

2. 在 "多条件查找" 对话框中，请按以下步骤操作：

• 在 "待检索值与输出结果区域" 部分，选择查找值范围和输出范围；
• 在 "数据区域" 部分，请执行以下操作：
  • 在 "关键" 列框中按住 Ctrl 键逐一选择包含查找值的相应关键列；
  • 在 "返回" 列框中指定包含返回值的列。

3. 点击确定按钮以获得结果。

√ 注意：

• 在对话框中，用指定值替换 #N/A 错误值部分是可选的，您可以指定也可以不指定。
• 在关键列框中输入的列数必须等于在查找值框中输入的列数，并且两个框中的条件顺序必须一一对应。

示例10：查找最后匹配的值

使用 XLOOKUP 获取最后匹配的结果

要在 Excel 中查找最后匹配的值，请将第六个参数设置为反向搜索顺序。

默认情况下，XLOOKUP 的搜索模式设置为1，即从第一个到最后一个进行搜索。但 XLOOKUP 的优点是可以更改查找方向。XLOOKUP 提供了可选的 [search mode] 参数来控制搜索顺序。只需将第六个参数中的搜索模式设置为 -1，查找方向就会更改为从最后到第一个进行搜索。

请参见下面的示例。我们想知道数据库中 Emma 的最后一次销售。

在空白单元格 G2 中输入以下公式，然后按 Enter 键获取结果。

=XLOOKUP(F2,B2:B11,D2:D11,,,-1)

√ 注意：第四和第五个参数是可选的，在这种情况下被省略。我们只将可选的第六个参数设置为 -1。

使用惊人的工具轻松查找最后匹配的值

如果您无法访问 XLOOKUP 并且也不想记住复杂的公式，您可以应用 "从下到上查找" 功能轻松完成。

1. 转到 Excel 中的 "Kutools"选项卡，找到 "高级 LOOKUP"，然后在下拉列表中点击 "从下到上查找"。

2. 在 "从下到上查找" 对话框中，您需要按以下步骤配置：

• 在 "待检索值与输出结果区域" 部分，选择查找范围和输出范围；
• 在 "数据区域" 部分，选择数据范围，然后指定 "关键列" 和 "返回列"。

3. 点击确定按钮以获得结果。

√ 注意：在对话框中，用指定值替换 #N/A 错误值部分是可选的，您可以指定也可以不指定。

下载 XLOOKUP 示例文件

XLOOKUP 示例.xlsx

相关文章：

• 如何在 Excel 中一起使用 INDEX 和 MATCH？
• 如何在 Excel 中应用模糊查找以找到近似匹配结果？
• 如何在 Excel 中使用双向查找公式？
• 如何在 Excel 中使用多个条件查找值？
• 如何在 Excel 中从右到左查找值？