Applies ToMicrosoft 365 专属 Excel Microsoft 365 Mac 版专属 Excel Excel 网页版 Excel 2024 Excel 2024 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2016 Excel for iPad Excel for iPhone Excel for Android 平板电脑版 Excel for Android 手机版

使用 XLOOKUP 函数按行查找表或区域中的项。 例如,按部件号查找汽车部件的价格,或根据员工 ID 查找员工姓名。 使用 XLOOKUP,可以在一列中查找搜索词,并从另一列中的同一行返回结果,而不管返回列位于哪一侧。

XLOOKUP 在 Excel 2016 和 Excel 2019 中不可用。 但是,如果工作簿是由其他人使用较新版本的 Excel 创建的,则可能会遇到在 Excel 2016 或 Excel 2019 中使用 XLOOKUP 函数的情况。

你的浏览器不支持视频。 请安装 Microsoft Silverlight、Adobe Flash Player 或 Internet Explorer 9。

语法

XLOOKUP 函数搜索区域或数组,然后返回与它找到的第一个匹配项对应的项。 如果不存在匹配项,则 XLOOKUP 可以返回最接近的 (近似) 匹配项。 

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

参数

说明

lookup_value

必填*

要搜索的值 *如果省略,XLOOKUP 将返回在 lookup_array 中找到的空白单元格。   

lookup_array

必需​​

要搜索的数组或区域

return_array

必需

要返回的数组或区域

[if_not_found]

可选

如果未找到有效的匹配项,则返回你提供的 [if_not_found] 文本。

如果未找到有效的匹配项,并且缺少 [if_not_found],则返回 #N/A

[match_mode]

可选

指定匹配类型:

0 - 完全匹配。 如果未找到,则返回 #N/A。 这是默认选项。

-1 - 完全匹配。 如果没有找到,则返回下一个较小的项。

1 - 完全匹配。 如果没有找到,则返回下一个较大的项。

2 - 通配符匹配,其中 *, ? 和 ~ 有特殊含义

[search_mode]

可选

指定要使用的搜索模式:

1 - 从第一项开始执行搜索。 这是默认选项。

-1 - 从最后一项开始执行反向搜索。

2 - 执行依赖于 lookup_array 按升序排序的二进制搜索。 如果未排序,将返回无效结果。

2 - 执行依赖于 lookup_array 按降序排序的二进制搜索。 如果未排序,将返回无效结果。

示例

示例 1    使用 XLOOKUP 查找区域中的国家/地区名称,然后返回其电话国家/地区代码。 它包括 lookup_value (单元格 F2) 、 lookup_array (范围 B2:B11) ,以及 return_array (范围 D2:D11) 参数。 它不包括 match_mode 参数,因为 XLOOKUP 默认生成完全匹配项。

用于返回基于员工 ID 的员工姓名和部门的 XLOOKUP 函数示例。 公式为 =XLOOKUP (B2,B5:B14,C5:C14)

XLOOKUP 使用查找数组和返回数组,而 VLOOKUP 使用单个表数组,后跟列索引号。 在这种情况下,等效的 VLOOKUP 公式为: =VLOOKUP (F2,B2:D11,3,FALSE)

———————————————————————————

示例 2    根据员工 ID 号查找员工信息。 与 VLOOKUP 不同,XLOOKUP 可以返回包含多个项的数组,因此单个公式可以从单元格 C5:D14 返回员工姓名和部门。

XLOOKUP 函数的示例,用于基于员工 ID 返回员工名称和部门。 公式为:=XLOOKUP (B2,B5:B14,C5:D14,0,1)

———————————————————————————

示例 3    将 if_not_found 参数添加到前面的示例中。

XLOOKUP 函数的示例,用于基于员工 ID 和 if_not_found 参数返回员工名称和部门。 公式为 =XLOOKUP (B2,B5:B14,C5:D14,0,1,“找不到员工”)

———————————————————————————

示例 4    在 C 列中查找在单元格 E2 中输入的个人收入,并在 B 列中找到匹配的税率。它将 if_not_found 参数设置为在未找到任何内容时返回 0 (零) 。 match_mode参数设置为 1,这意味着函数将查找完全匹配项,如果找不到匹配项,则返回下一个较大的项。 最后, search_mode 参数设置为 1,这意味着函数将从第一项搜索到最后一项。

用于返回基于最高收入的税率的 XLOOKUP 函数的图像。 这是一个近似匹配项。公式为:=XLOOKUP (E2,C2:C7,B2:B7,1,1)

XARRAY 的 lookup_array 列位于 return_array 列的右侧,而 VLOOKUP 只能从左到右查看。

———————————————————————————

示例 5    使用嵌套的 XLOOKUP 函数执行垂直和水平匹配。 它首先查找 B 列中的 “毛利润 ”,然后在表 (范围 C5:F5) 的上一行中查找 Qtr1 ,最后返回两者交集处的值。 这类似于将 INDEXMATCH 函数一起使用。

你也可以使用 XLOOKUP 替换 HLOOKUP 函数。

用于通过嵌套两个 XLOOKUPs 从表格中返回水平数据的 XLOOKUP 函数的图像。 公式为:=XLOOKUP (D2,$B 6:$B 17,XLOOKUP ($C 3,$C 5:$G 5,$C 6:$G 17) )

单元格 D3:F3 中的公式为: =XLOOKUP (D2,$B 6:$B 17,XLOOKUP ($C 3,$C 5:$G 5,$C 6:$G 17) )

———————————————————————————

示例 6    使用 SUM 函数和两个嵌套的 XLOOKUP 函数对两个区域之间的所有值求和。 在本例中,我们要对葡萄、香蕉的值求和,并包括两者之间的梨。

将 XLOOKUP 与 SUM 配合使用,使两个选定内容之间的值范围相加

单元格 E3 中的公式为:=SUM (XLOOKUP (B3,B6:B10,E6:E10) :XLOOKUP (C3,B6:B10,E6:E10) )

它如何工作? XLOOKUP 返回一个范围,因此在计算时,公式最终如下所示: =SUM ($E$7:$E$9) 。 可以通过选择 XLOOKUP 公式类似于此公式的单元格,选择“公式 ”>“ 公式审核 ”>“ 计算公式”,然后选择“ 计算”以逐步完成计算,即可自行查看其工作原理。 

感谢 Microsoft Excel MVP Bill Jelen 提供此示例建议。

———————————————————————————

另请参阅

可随时在 Excel 技术社区中咨询专家或在社区中获取支持。

XMATCH 函数

Excel 函数(按字母顺序)

Excel 函数(按类别列出)

需要更多帮助?

需要更多选项?

了解订阅权益、浏览培训课程、了解如何保护设备等。

社区可帮助你提出和回答问题、提供反馈,并听取经验丰富专家的意见。