XLOOKUP 函数

使用 XLOOKUP 函数按行查找表格或区域内容。 例如,按部件号查找汽车部件的价格,或根据员工 ID 查找员工姓名。 借助 XLOOKUP,你可以在一列中查找搜索词,并在同一行的另一列中返回结果,无论返回结果的列在原列的哪一侧。

Surface Book 设备照片

语法

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

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

参数

说明

lookup_value

必需*

查找值

*如果省略,则使用空白单元格进行匹配。   

注意: 空字符串与空白单元格不同,无效 用于lookup_value,并生成错误。

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 查找区域中的国家/地区名称,然后返回其电话国家/地区代码。 它包括单元格F) 2 lookup_value (、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 参数添加到上一个示例。

用于根据员工 ID 和 if_not_found 参数返回员工姓名和部门的 XLOOKUP 函数示例。 公式为 =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 技术社区中咨询专家,在解答社区获得支持,或在 Excel User Voice 上建议新功能或功能改进。

另请参阅

XMATCH 函数

Excel 函数(按字母顺序)

Excel 函数(按类别列出)

需要更多帮助?

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

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×