如何在 Excel 中使用 NEW & ADVANCED XLOOKUP 函数（10 个示例）

如何在 Excel 中使用 NEW & ADVANCED XLOOKUP 函数（10 个示例）

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

在本教程中，我们将向您展示 XLOOKUP 的优势是什么 和 你怎么能得到它并应用它 解决不同的查找问题。

如何获得 XLOOKUP？

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 函数 is 只可用 in 适用于 Microsoft 365 的 Excel, Excel中2021及 网络版Excel，您可以将您的 Excel 升级到可用版本以获取 XLOOKUP。

XLOOKUP 函数语法

XLOOKUP 函数 查找范围或数组，然后返回第一个匹配结果的值。 该 句法 XLOOKUP 函数如下：

参数：

查找值（必填）：您正在寻找的价值。 它可以在 table_array 范围的任何列中。 Lookup_array（必需）：您在其中搜索查找值的数组或范围。 返回数组（必填）：要从中获取值的数组或范围。 If_not_found（可选）

：未找到有效匹配项时返回的值。 您可以自定义 [if_not_found] 中的文本以显示不匹配。

否则，返回值默认为#N/A。 匹配模式（可选）：在这里您可以指定如何将lookup_value 与lookup_array 中的值进行匹配。 0（默认）= 完全匹配。 如果未找到匹配项，则返回 #N/A。 -1 = 完全匹配。 如果未找到匹配项，则返回下一个较小的值。 1 = 完全匹配。 如果未找到匹配项，则返回下一个较大的值。 2 = 部分匹配。 使用通配符，如 *, ? 和 ~ 运行通配符匹配。 搜索模式（可选）：在这里您可以指定要执行的搜索顺序。 1（默认）= 在lookup_array 中从第一项到最后一项搜索lookup_value。 -1 = 从最后一项到第一项搜索lookup_value。 当您需要在 lookup_array 中获取最后一个匹配结果时，它会有所帮助。 2 = 执行需要按升序排序的lookup_array 的二分查找。 如果不排序，则返回结果无效。 -2 = 执行需要按降序排序的lookup_array 的二分查找。 如果不排序，则返回结果无效。

针对 XLOOKUP 函数参数的详细信息，请执行以下操作：

1. 键入 下面的语法 进入一个空单元格，请注意您只需要输入括号的一侧。

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

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

>>> XLOOKUP 函数示例

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

示例 1：完全匹配

使用 XLOOKUP 执行精确匹配

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

现在，假设您有一份办公用品库存清单，并且您需要知道一件物品的单价，比如说鼠标，请执行以下操作。

键入 下面的公式 进入空白单元格 F2，然后按 输入 获得结果的关键。

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

现在您使用高级 XLOOKUP 公式知道鼠标的单价。 由于匹配代码已默认为完全匹配，因此您无需指定它。 比 VLOOKUP 更容易、更高效。

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

也许您正在使用较低版本的 Excel，并且还没有升级到 Excel 2021 或 Microsoft 365 的计划。 在这种情况下，我会推荐 一个方便的功能 - 在列表公式中查找值 of Kutools for Excel. 使用此功能，您无需复杂的公式或访问 XLOOKUP 即可获得结果。

与our Excel加载项 研究所alled，请按以下步骤操作：

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

2。 去 Kutools 标签，点击 公式助手，然后单击 下拉列表中的公式助手.

3.在 公式助手对话框，请进行如下配置：

选择 查找 ，在 公式类型部分; 在 选择公式部分， 选择 在列表中查找值; 在 参数输入部分，请执行以下操作： 在 表数组框，选择包含查找值和结果值的数据范围； 在 查找值框，选择要搜索的值的单元格或范围。 请注意它必须在 table_array 的第一列； 在 柱箱，选择要从中返回匹配值的列。

4。 点击 OK 按钮获取结果。

点击下载 Kutools for Excel 30 天免费试用.

示例 2. 近似匹配

使用 XLOOKUP 执行近似匹配

运行一个 近似查找，您需要 在第五个参数中将匹配模式设置为 1 或 -1. 当没有找到完全匹配时，它返回下一个更大或更小的值.

在这种情况下，您需要了解员工收入的税率。 电子表格的左侧是 2021 年的联邦所得税括号。如何在 E 列中获得员工的税率？ 别担心。 请执行以下操作：

1. 键入 下面的公式 进入空白单元格 E2，然后按 输入

获得结果的关键。

然后根据需要更改返回结果的格式。

=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; 按 输入 按钮获取单元格 E2 的结果。 >>>

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

√ 注意：

按 F4 键 键盘上的允许您 将单元格引用更改为绝对引用 通过在行和列之前添加美元符号。 应用绝对参考后 为了查找和返回范围，我们改变了 单元格 E2 中的公式 到这个版本：

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

当你 从单元格 E2 向下拖动填充手柄, 公式 在 E 列的每个单元格中 仅在lookup_value 方面有所改变

.

比如现在E13中的公式变成了这样：

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

示例 3：通配符匹配

使用 XLOOKUP 执行通配符匹配

在我们研究之前 XLOOKUP 通配符匹配函数，我们先来看看 什么是通配符.

在 Microsoft Excel 中， 通配符是一种特殊的字符，可以替换任何字符. 特别是 有帮助 当您想要执行部分匹配查找时。

通配符分为三种类型： 星号 (*), 问号（？）及 波浪号 (~).

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

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

假设您有一份美国 50 家最大公司的股票市值列表，并且您想知道几家公司的市值，但公司名称很短，这是通配符匹配的完美场景。 请跟着我一步一步来做这个把戏。

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

1. 键入 下面的公式 到空白单元格 H3，然后按 输入 获得结果的关键。

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

>>>

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

=XLOOKUP("*"&G3&"*",$B:$B,$D:$D,,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。 按 输入 获得结果的关键。

√ 注意：XLOOKUP 向左查找函数可以代替 Index 和 Match 向左查找值。

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

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

与our Excel加载项 研究所alled，请按以下步骤操作：

1。 去 Kutools Excel 中的选项卡，找到 超级查找和点击 从右到左查找 在下拉列表中。

2.在 从右到左查找对话框，需要进行如下配置：

在 查找值和输出范围部分，请指定 查找范围 和 输出范围; 在 数据范围部分，输入 数据范围，然后指定 关键列 和 返回栏;

3。 点击 OK 按钮获取结果。

点击下载 Kutools for Excel 30 天免费试用.

示例 5：垂直或水平查找

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

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

现在 新的 XLOOKUP 结合了它们， 意思是 您只需要使用一种语法来执行垂直查找或水平查找. 天才，不是吗？

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

执行垂直查找, 键入 下面的公式 在空白单元格 E2 中，按 输入 获得结果的关键。

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

执行水平查找, 键入 下面的公式 在空白单元格 P2 中，按 输入 获得结果的关键。

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

正如你可以看到， 语法是一样的。 该 唯一的区别 两个公式之间是你输入 列 在您输入时进行垂直查找 行 在水平查找中。

示例 6：双向查找

使用 XLOOKUP 执行双向查找

你还在用吗 索引和匹配函数 在二维范围内查找值？ 尝试 改进的 XLOOKUP 完成你的工作 更容易.

XLOOKUP 可以执行 双重查找，找到一个 路口 的两个值。 经过 嵌套 一个 XLOOKUP 在另一个 XLOOKUP 里面，里面的 XLOOKUP 可以返回一整行或一列，然后这个返回的行或列作为返回数组输入到外部 XLOOKUP 中。

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

让我们看看我们如何使用神奇的 XLOOKUP 来实现这一目标。

我们运行“内部”XLOOKUP 以返回输入列的值。 XLOOKUP(H2,B1:E1,B2:E10) 可以获得一系列化学成绩。 我们通过使用“内部”XLOOKUP 作为完整公式中的 return_array 将“内部”XLOOKUP 嵌套在“外部”XLOOKUP 中。 然后是最后的公式：

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

键入 上面的公式 进入空白单元格 H3，按 输入 按钮获取结果。

或者你可以做 另一种方式，使用“内部”XLOOKUP 来返回整行的值，这些值都是 Kim 的科目成绩。 然后使用“外部”XLOOKUP 在 Kim 的所有科目成绩中查找化学成绩。

键入 下面的公式 在空白单元格 H4 中，然后按 输入 按钮获取结果。

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

XLOOKUP的双向查找功能也是其纵横查找功能的完美诠释。 如果你想试试！

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

使用 XLOOKUP 自定义未找到消息

就像其他查找函数一样，当 XLOOKUP 函数 找不到匹配项中， #N/A 错误信息 将被退回。 某些 Excel 用户可能会感到困惑。 但好消息是 错误处理 可以在 XLOOKUP 函数的第四个参数.

随着 内置 [if_not_found] 参数，您可以指定 用于替换 #N/A 结果的自定义消息. 在可选的第四个参数中键入您需要的文本并将文本括在 双引号 （”）.

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

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

=XLOOKUP(E2,A2:A11,C2:C11,"No Match")

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

要使用您的自定义消息快速覆盖 #N/A 错误， Kutools for Excel is 一个完美的工具 在 Excel 中为您提供帮助。 凭借其内置 将 0 或 #N/A 替换为空白或特定值功能，您可以指定未找到消息而无需复杂的公式或访问 XLOOKUP。

随着我们的 Excel加载项 已安装，请执行以下操作：

1。 去 Kutools Excel 中的选项卡，找到 超级查找和点击 将0或＃N / A替换为空白或特定值 在下拉列表中。

2.在 将 0 或 #N/A 替换为空白或特定值对话框，需要进行如下配置：

在 查找值和输出范围部分， 选择 查找范围 和 输出范围; 然后 选择用特定值替换 0 或 #N/A 选项, 输入文字 你喜欢; 在 数据范围部分，选择 数据范围，然后指定 关键列 和 返回列.

3。 点击 OK 按钮获取结果。 未找到匹配项时将显示自定义消息。

点击下载 Kutools for Excel 30 天免费试用.

示例 8：多个值

使用 XLOOKUP 返回多个值

另一个 优点 XLOOKUP 的特点是它能够 返回多个值 在同一时间进行同一场比赛。 输入一个公式以获得第一个结果，然后输入其他返回值 洒 自动进入相邻的空白单元格。

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

键入 下面的公式 在空白单元格 G2 中，按 输入 获得所有结果的关键。

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

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

总而言之，XLOOKUP 的多值函数是一个 有用的改进 与 VLOOKUP 相比。 您无需为每个公式分别指定每个列号。 竖起大拇指！

示例 9. 多个标准

使用 XLOOKUP 执行多条件查找

另一个 惊人的新功能 XLOOKUP 的特点是它能够 使用多个条件查找. 诀窍是 串联 查找值和查找数组 “＆“ 操作员 分别在公式中。 让我们通过下面的例子来说明。

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

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

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

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

有没有 更快更容易 在excel中执行多标准查找而不是XLOOKUP的方法？ Kutools for Excel 提供 一个惊人的功能 - 多条件查找. 使用此功能，您只需单击几下即可运行多个条件查找！

随着我们的 Excel加载项 已安装，请执行以下操作：

1。 去 Kutools Excel 中的选项卡，找到 超级查找和点击 多条件查找 在下拉列表中。

2.在 多条件查找对话框，请执行以下操作：

在 查找值和输出范围部分，选择 查找值范围 和 输出范围; 在 数据范围部分，请执行以下操作： 点击 对应的键列 通过持有一一包含查找值 按Ctrl 键入 键列框; 指定 栏 其中包含返回的值 返回列框.

3。 点击 OK 按钮获取结果。

√ 注意：

Replace #N/A error value with a specified value 部分在对话框中是可选的，您可以指定或不指定。 在 Key column 框中输入的列数必须等于在 Lookup Values 框中输入的列数，并且两个框中的条件顺序必须彼此一一对应。

点击下载 Kutools for Excel 30 天免费试用.

示例 10. 查找最后一个匹配项的值

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

为了找到 最后匹配值 在 Excel 中，设置 第六论点 在 XLOOKUP 函数中 逆序搜索.

默认情况下，XLOOKUP 中的搜索模式设置为 1，这是 从头到尾搜索. 但好的一点是 XLOOKUP 是 查找的方向可以改变. XLOOKUP 提供 可选 [搜索模式] 参数 控制搜索顺序。 只需将第六个参数中的搜索模式设置为 -1，查找方向更改为从最后一个到第一个搜索。

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

键入 下面的公式 在空白单元格 G2 中，然后按 输入 获得结果的关键。

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

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

使用神奇的工具轻松查找最后一个匹配值

如果您无法访问 XLOOKUP 并且不想记住复杂的公式，您可以应用 从下到上查找功能 of Kutools for Excel 完成它 容易.

随着我们的 Excel加载项 已安装，请执行以下操作：

1。 去 Kutools Excel 中的选项卡，找到 超级查找和点击 从下到上查找 在下拉列表中。

2.在 从下到上查找对话框，需要进行如下配置：

在 查找值和输出范围部分，选择 查找范围 和 输出范围; 在 数据范围部分，选择 数据范围，然后指定 关键列 和 返回栏.

3。 点击 OK 按钮获取结果。

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

点击下载 Kutools for Excel 30 天免费试用.

下载 XLOOKUP 示例文件

XLOOKUP 示例.xlsx

相关文章： Excel中如何同时使用INDEX和MATCH？ 如何应用模糊查找在 Excel 中查找近似匹配结果？ 如何在 Excel 中使用双向查找公式？ 如何在 Excel 中使用多个条件查找值？ 如何在 Excel 中从右到左查找值？