公式和函数

XLOOKUP

XLOOKUP 函数

使用 XLOOKUP 函数按行查找表格或区域内容。 例如,按部件号查找汽车部件的价格,或根据员工 ID 查找员工姓名。 借助 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] 文本。

如果找不到有效的匹配项,并且缺少 [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 返回员工姓名和部门。

用于返回基于员工 IDt 的员工姓名和部门的 XLOOKUP 函数示例。 公式为:=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 列中查找 " 总利润",然后在表的首行中查找第 1 季度 (区域 C5:F5) ,最后返回两者交叉处的值。 这类似于同时使用 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 技术社区中咨询专家或在 Answers 社区获取

另请参阅

XMATCH 函数

Excel 函数(按字母顺序)

Excel 函数(按类别列出)

需要更多帮助?

扩展你的 Office 技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

×