如何在 Excel 中使用全新且高级的 XLOOKUP 函数（10 个示例）
Excel 全新推出的 XLOOKUP 函数是 Excel 中最强大且最易用的查找函数！经过不懈努力，微软终于推出这一函数，用以取代 VLOOKUP、HLOOKUP、INDEX+MATCH 等传统查找函数。
在本教程中，我们将为您揭示 XLOOKUP 的强大优势，并演示如何获取和应用它，轻松应对各类查找难题。
如何获取 XLOOKUP？
由于 XLOOKUP 函数仅适用于 Microsoft 365 专属 Excel、Excel 2021 及更高版本，以及 Excel 网页版，若您仍在使用 Excel 2019 或更早版本，建议升级以畅享 XLOOKUP 的强大功能。
语法
该函数会在指定范围或数组中进行查找，并返回首个匹配项的值。其语法如下：
参数：
- Lookup_value (required)：您要查找的值，可位于 table_array 范围内的任意列中。
- Lookup_array (required)您用于搜索查找值的数组或范围。
- Return_array (required)您希望从中提取值的数组或范围。
- If_not_found (optional)：当未找到有效匹配项时返回的值。您可在 【if_not_found】 中自定义提示文本，用于显示“无匹配项”。
否则，默认返回 #N/A。 - Match_mode (optional)在此处可指定 lookup_value 与 lookup_array 中的值的匹配方式。
- 0（默认）= 精确匹配。若未找到匹配项，则返回 #N/A。
- ——1 = 精确匹配。若未找到匹配项，则返回次小值。
- 1 = 精确匹配。若未找到匹配项，则返回下一个较大的值。
- 2 = 部分匹配。使用通配符（如 *、？ 和 ~）进行通配符匹配。
- Search_mode (optional)在此处可指定搜索的执行顺序。
- 1（默认）= 从 lookup_array 的第一个项目开始，依次向最后一个项目搜索 lookup_value。
- ——1 = 从 lookup_array 中的最后一个项目向第一个项目进行搜索。当您需要获取 lookup_array 中最后一个匹配项时，此选项非常实用。
- 2 = 执行二分搜索，要求 lookup_array 按升序排列；若未排序，返回结果将无效。
- ——2 = 执行二分搜索，要求 lookup_array 按降序排列；若未排序，返回结果将无效。
如需详细了解参数，请按以下步骤操作：
1. 请在空白单元格中输入以下语法，注意只需输入括号的单侧即可。
2. 按下 Ctrl+A，随即弹出提示框显示函数参数，同时另一侧的括号将自动补全。
3. 下拉数据面板，即可查看 XLOOKUP 的全部六个参数。
 | >>> |  |
示例
相信您已经掌握了 XLOOKUP 的基本原理，接下来，让我们直接进入它的实际应用示例。
示例 1：精确匹配
您是否曾因每次使用 VLOOKUP 时都必须手动指定精确匹配模式而感到困扰？幸运的是，当您尝试强大的 XLOOKUP 函数时，这一烦恼将彻底消失——XLOOKUP 默认即采用精确匹配。
现在，假设您有一份办公用品库存清单，需要查询某项物品（例如鼠标）的单价，请按以下步骤操作。
在空白单元格 F2 中输入以下公式，然后按 Enter 键即可获得结果。
=XLOOKUP(E2,A2:A10,C2:C10)
现在，您已通过高级 XLOOKUP 公式轻松获取鼠标的单价。得益于其默认采用精确匹配模式，无需额外设置，比 VLOOKUP 更简单、更高效。
或许您正在使用较低版本的 Excel，且暂无计划升级至 Excel 2021 或 Microsoft 365. 在这种情况下，我推荐您使用一个便捷功能——“查找值”。借助此功能，您无需复杂公式或 XLOOKUP，即可轻松获得结果。
1. 单击您希望放置匹配结果的单元格。
2. 转到“Kutools”选项卡，单击“公式助手”，然后在下拉列表中再次选择“公式助手”。
3. 在公式助手对话框中，请按以下方式配置：
- 在“公式类型”部分中选择“Lookup”；
- 在“选择一个公式”部分中，选择“在区域中查找数据”；
- 在“参数输入”部分中，请执行以下操作：
- 在“Table_array”框中，选择包含查找值和结果值的数据区域；
- 在“Lookup_value”框中，选择您要查找的值所在的单元格或区域。请注意，该值必须位于 table_array 的第一列中。
- 在“Column”框中，选择您希望从中返回匹配值的列。
4. 单击“确定”按钮，立即获取结果。
Kutools for Excel——通过 300 多款必备工具全面增强 Excel 功能，助您工作更快速、更轻松，并借助 AI 功能实现更智能的数据处理与高效办公！立即获取
示例 2：近似匹配
要执行近似查找，请在第五个参数中将匹配模式设为 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 列每个单元格中的公式仅在 lookup_value 参数上发生变化。
例如，E13 中的公式现在变为：
=XLOOKUP(D13,$B$2:$B$8,$A$2:$A$8,,1)
示例 3：通配符匹配
在深入探讨 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：向左查找
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. 单击“确定”按钮，立即获取结果。
Kutools for Excel——通过 300 多款必备工具全面增强 Excel 功能，助您工作更快速、更轻松，并借助 AI 功能实现更智能的数据处理与高效办公！立即获取
示例 5：垂直或水平查找
作为 Excel 用户，您可能熟悉 VLOOKUP 和 HLOOKUP 函数：VLOOKUP 用于在列中垂直查找数据，而 HLOOKUP 则用于在行中水平查找数据。
如今，全新的 XLOOKUP 将两者合二为一，让您只需掌握一种语法，即可轻松实现垂直或水平查找。是不是很巧妙？
以下示例将展示如何仅用一个 XLOOKUP 函数，轻松实现垂直或水平查找。
要在空白单元格 E2 中执行垂直查找，请输入以下公式，然后按 Enter 键获取结果。
=XLOOKUP(E1,A2:A13,B2:B13)
在空白单元格 P2 中输入以下公式，然后按 Enter 键，即可执行水平查找并获取结果。
=XLOOKUP(P1,B1:M1,B2:M2)
如您所见，两者的语法完全一致，唯一的区别在于：垂直查找以列作为输入，而水平查找则以行作为输入。
示例 6：双向查找
您是否还在用 INDEX 和 MATCH 函数来在二维表格中查找值？不妨试试升级版 XLOOKUP，让工作更轻松高效！
XLOOKUP 支持双重查找，精准定位两个值的交汇点。通过将一个 XLOOKUP 嵌套于另一个 XLOOKUP 之中，内部的 XLOOKUP 可返回整行或整列，该结果随即作为外部 XLOOKUP 的返回数组。
假设您有一份包含各学科成绩的学生名单，想查询 Kim 的化学成绩。
让我们一起来看看如何用强大的 XLOOKUP 函数轻松实现这一操作！
- 我们使用“内部”XLOOKUP 函数来获取整列的返回值。XLOOKUP(H2, B1:E1, B2:E10) 可以提取化学成绩所在的范围。
- 我们将“内部”XLOOKUP 嵌套于“外部”XLOOKUP 中，并将其作为完整公式中的 return_array 使用。
- 最终公式如下：
=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 结果。只需在可选的第四个参数中输入所需文本，并用双引号（“）括起即可。
例如，当城市“Denver”未找到时，XLOOKUP 会返回 #N/A 错误；但若将第四个参数自定义为文本“No Match”，公式便会显示“No Match”而非错误信息。
在空白单元格 F3 中输入以下公式，然后按 Enter 键即可获得结果。
=XLOOKUP(E2,A2:A11,C2:C11,"No Match")
要快速将 #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. 点击“确定”按钮即可获取结果；若未找到匹配项，系统将显示自定义消息。
Kutools for Excel——通过 300 多款必备工具全面增强 Excel 功能，助您工作更快速、更轻松，并借助 AI 功能实现更智能的数据处理与高效办公！立即获取
示例 8：返回多个值
XLOOKUP 的另一大优势在于，能为同一匹配项同时返回多个值——只需输入一个公式，即可获得首个结果，其余值将自动填充至相邻的空白单元格中。
在下例中，您希望获取学号为“FG 9940005”的学生的所有信息。关键在于将公式中的 return_array 设置为一个区域，而非仅限单列或单行——此处的返回数组区域为 B2:D9，包含三列数据。
在空白单元格 G2 中输入以下公式，然后按 Enter 键即可获取全部结果。
=XLOOKUP(F2,A2:A9,B2:D9)
所有结果单元格均显示相同的公式。您可以在第一个单元格中编辑或修改公式，但其他单元格中的公式不可编辑——此时编辑栏将显示为灰色，表明无法进行更改。
总而言之，相比 VLOOKUP，XLOOKUP 的多值返回功能是一项实用的改进——您无需再为每个公式单独指定列号，便捷高效！
示例 9：多条件查找
XLOOKUP 的另一项强大新功能是支持多条件查找。其关键技巧在于：在公式中分别使用“&”运算符，将待检索值区域与查找数组连接起来。以下示例将为您清晰演示这一用法。
我们需要查询中号蓝色花瓶的价格。本例中需同时满足三个检索条件才能找到匹配项。请在空白单元格 I2 中输入以下公式，然后按 Enter 键获取结果。
=XLOOKUP(F2&G2&H2,A2:A12&B2:B12&C2:C12,D2:D12)
√ 注意：XLOOKUP 可直接处理数组，无需按 Ctrl + Shift + Enter 组合键确认公式。
在 Excel 中，是否有比 XLOOKUP 更快捷、更简便的多条件查找方法？Kutools for Excel 提供强大的“Lookup 多条件查找”功能，只需几次点击，即可轻松完成多条件查找！
1. 在 Excel 中切换到“Kutools”选项卡，找到“高级 LOOKUP”，然后在下拉列表中单击“Lookup 多条件查找”。
2. 在“Lookup 多条件查找”对话框中，请按以下步骤操作：
- 在“待检索值与输出结果区域”部分中，选择查找值范围和列表放置区域；
- 在“数据区域”部分中，请执行以下操作：
- 在“主键列”框中，按住 Ctrl 键依次选择包含待检索值区域的相应关键列；
- 在“返回列”框中，指定包含所需返回值的列。
3. 点击“确定”按钮，立即获取结果。
√ 注意：
- 对话框中“用指定值替换未找到项并返回‘N/A’”的输出结果部分为可选项，您可以选择指定或不指定。
- 在“关键列”框中输入的列数必须与“待检索值区域”框中的列数相等，且两个框中的条件顺序需一一对应。
Kutools for Excel——通过 300 多款必备工具全面增强 Excel 功能，助您工作更快速、更轻松，并借助 AI 功能实现更智能的数据处理与高效办公！立即获取
示例 10：查找最后一次匹配的值
要在 Excel 中查找最后一次匹配的值，请将第六个参数设为反向搜索。
默认情况下，XLOOKUP 的搜索模式为 1（即从首项向末项查找）。但 XLOOKUP 的强大之处在于支持灵活调整查找方向——通过可选的 [search_mode] 参数即可控制搜索顺序。只需将第六个参数设为 ——1，即可轻松实现从末项向首项反向查找。
请参见以下示例：我们希望查询数据库中 Emma 的最新销售记录。
在空白单元格 G2 中输入以下公式，然后按 Enter 键即可获得结果。
=XLOOKUP(F2,B2:B11,D2:D11,,,-1)
√ 注意：本例中省略了第四和第五个参数，仅将可选的第六个参数设为 -1.
如果您无法使用 XLOOKUP，又不想记复杂的公式，不妨试试“从下到上查找”功能，轻松搞定！
1. 在 Excel 中切换到“Kutools”选项卡，找到“高级 LOOKUP”，然后从下拉列表中点击“从下到上查找”。
2. 在“从下到上查找”对话框中，请按以下方式进行配置：
- 在“待检索值与输出结果区域”部分中，选择查找范围和列表放置区域；
- 在“数据区域”部分，选择所需的数据区域，并指定“关键列”与“返回列”。
3. 点击“确定”按钮，立即获取结果。
√ 注意：对话框中“用指定的值替换未找到而返回‘N/A’的输出结果”这一部分为可选项，您可以根据需要选择是否指定。
Kutools for Excel——通过 300 多款必备工具全面增强 Excel 功能，助您工作更快速、更轻松，并借助 AI 功能实现更智能的数据处理与高效办公！立即获取
下载 XLOOKUP 示例文件
相关文章：
- 如何在 Excel 中巧妙结合使用 INDEX 和 MATCH 函数？
- 如何在 Excel 中使用模糊查找来获取近似匹配的结果？
- 如何在 Excel 中使用双向查找公式？
- 如何在 Excel 中使用多条件 VLOOKUP 查找值？
- 如何在 Excel 中实现从右向左的 VLOOKUP 查找？
最佳办公效率工具
| 🤖 | KUTOOLS AI 助手：基于以下内容革新数据分析：智能执行 | 生成代码| 创建自定义公式 | 数据分析及生成图表| 调用 Kutools Functions…… |
| 热门功能：查找、高亮或标记重复项 | 删除空白行 | 合并列或单元格且不丢失数据 | 不使用公式的四舍五入…… | |
| 高级 LOOKUP：多条件 VLookup | 多值 VLookup | 跨多工作表 VLookup | 模糊查找…… | |
| 高级下拉列表：快速创建下拉列表 | 级联下拉列表 | 多选下拉列表…… | |
| 列管理器：添加指定数量的列|移动列|切换隐藏列的可见性状态|比较区域与列…… | |
| 特色功能：网格聚焦 | 设计视图 |增强编辑栏 | 工作簿和表管理器 | 资源库（自动文本）| 日期提取 | 汇总工作表 | 加密/解密单元格 | 按列表发送邮件 | 超级筛选 | 特殊筛选（筛选粗体单元格/斜体/删除线……） ...... | |
| 精选 15 工具集：12 文本工具（添加文本，删除特定字符，……）| 50+ 图表 类型（甘特图，……）| 40+ 实用公式（基于生日计算年龄，……）| 19 插入工具（插入二维码，从路径插入图片，……）| 12 转换工具（小写金额转大写，汇率转换，……）| 7 合并和拆分工具（高级合并行，分割单元格，……）|……更多 |
使用 Kutools for Excel 大幅提升您的 Excel 技能，体验前所未有的高效。Kutools for Excel 提供 300 多项高级功能，助您提升生产力、节省时间。立即点击此处，获取您最需要的功能……
Office Tab 为 Office 带来标签式界面，让您的工作更轻松
- 在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中启用标签式编辑和阅读。
- 在同一个窗口的新标签页中打开并创建多个文档，而非在新窗口中。
- 将您的工作效率提升 50%，每天减少数百次鼠标点击！
所有 Kutools 插件，一个安装程序
Kutools for Office 套件捆绑了适用于 Excel、Word、Outlook 和 PowerPoint 的插件以及 Office Tab Pro，非常适合需要跨多个 Office 应用高效协作的团队。
- 一体化套件— Excel、Word、Outlook 和 PowerPoint 插件 + Office Tab Pro
- 一个安装程序，一个许可证— 几分钟内完成设置（支持 MSI）
- 协同效果更佳— 在多个 Office 应用中实现高效协同
- 30 天全功能试用— 无需注册，无需信用卡
- 超值之选— 比单独购买插件更省钱