XLOOKUP 函数
来源:微软帮助:XLOOKUP
使用 XLOOKUP 函数按行查找表或区域中的项. 例如,按部件号查找汽车部件的价格,或根据员工 ID 查找员工姓名. 使用 XLOOKUP,可以在一列中查找一个搜索词,并返回另一列中同一行的结果,而不管返回列位于哪一侧.
注意:
XLOOKUP在Excel 2016和Excel 2019中不可用,但是,你可能会遇到将 Excel 2016 或 Excel 2019 中的工作簿与其他人使用较新版本的 Excel 创建的XLOOKUP函数配合使用的情况.
据悉
Excel 2021开始完整支持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 按降序排序的二进制搜索. 如果未排序,将返回无效结果. |
使用 XLOOKUP 查找区域中的国家/地区名称,然后返回其电话国家/地区代码. 它包括 lookup_value (单元格 F2) 、 lookup_array (范围 B2:B11) ,以及 return_array (范围 D2:D11) 参数. 它不包括 match_mode 参数,因为 XLOOKUP 默认生成完全匹配项.
注意:
XLOOKUP使用查找数组和返回数组,而 VLOOKUP 使用单个表数组,后跟列索引号. 在这种情况下,等效的 VLOOKUP 公式为: =VLOOKUP (F2,B2:D11,3,FALSE)
根据员工 ID 号查找员工信息. 与 VLOOKUP 不同,XLOOKUP 可以返回包含多个项的数组,因此单个公式可以从单元格 C5:D14 返回员工姓名和部门.
将 if_not_found 参数添加到前面的示例中.
在 C 列中查找在单元格 E2 中输入的个人收入,并在 B 列中找到匹配的税率.它将 if_not_found 参数设置为在未找到任何内容时返回 0 (零) . match_mode参数设置为 1,这意味着函数将查找完全匹配项,如果找不到匹配项,则返回下一个较大的项. 最后, search_mode 参数设置为 1,这意味着函数将从第一项搜索到最后一项.
注意: XARRAY 的 lookup_array 列位于 return_array 列的右侧,而 VLOOKUP 只能从左到右查看.
使用嵌套的 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) ) .
使用 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 提供此示例建议.