XLOOKUP 函数
Applies To
Microsoft 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 函数的情况。
语法
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 默认生成完全匹配项。
: XLOOKUP 使用查找数组和返回数组,而 VLOOKUP 使用单个表数组,后跟列索引号。 在这种情况下,等效的 VLOOKUP 公式为: =VLOOKUP (F2,B2:D11,3,FALSE)
———————————————————————————
示例 2 根据员工 ID 号查找员工信息。 与 VLOOKUP 不同,XLOOKUP 可以返回包含多个项的数组,因此单个公式可以从单元格 C5:D14 返回员工姓名和部门。
———————————————————————————
示例 3 将 if_not_found 参数添加到前面的示例中。
———————————————————————————
示例 4 在 C 列中查找在单元格 E2 中输入的个人收入,并在 B 列中找到匹配的税率。它将 if_not_found 参数设置为在未找到任何内容时返回 0 (零) 。 match_mode参数设置为 1,这意味着函数将查找完全匹配项,如果找不到匹配项,则返回下一个较大的项。 最后, search_mode 参数设置为 1,这意味着函数将从第一项搜索到最后一项。
: XARRAY 的 lookup_array 列位于 return_array 列的右侧,而 VLOOKUP 只能从左到右查看。
———————————————————————————
示例 5 使用嵌套的 XLOOKUP 函数执行垂直和水平匹配。 它首先查找 B 列中的 “毛利润 ”,然后在表 (范围 C5:F5) 的上一行中查找 Qtr1 ,最后返回两者交集处的值。 这类似于将 INDEX 和 MATCH 函数一起使用。
: 你也可以使用 XLOOKUP 替换 HLOOKUP 函数。
: 单元格 D3:F3 中的公式为: =XLOOKUP (D2,$B 6:$B 17,XLOOKUP ($C 3,$C 5:$G 5,$C 6:$G 17) ) 。
———————————————————————————
示例 6 使用 SUM 函数和两个嵌套的 XLOOKUP 函数对两个区域之间的所有值求和。 在本例中,我们要对葡萄、香蕉的值求和,并包括两者之间的梨。
单元格 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 技术社区中咨询专家或在社区中获取支持。