XLOOKUP 函数

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

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

注意: 2019年11月25日,: XLOOKUP 当前是一个 beta 功能,并且目前仅适用于Office 预览体验成员的一部分。 我们将在未来的几个月里继续优化该功能。 XLOOKUP 准备就绪后,我们会向所有 Office 预览体验成员和 Office 365 订阅者发布该功能。

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

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

参数

说明

lookup_value

必需

查找值

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 公式是:=VLOOKUP(F2,B2:D11,3,FALSE)

示例 2

在此示例中,我们将根据员工 ID 编号查找员工信息。 与 VLOOKUP 不同,XLOOKUP 可以返回具有多个项的数组,这允许单个公式从单元格 C5: D14 返回员工姓名和部门。

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

示例 3

此示例将if_not_found参数添加到上面的示例。

XLOOKUP 函数的示例,该函数基于使用 if_not_found 参数的员工 ID 返回员工姓名和部门。 公式为 = 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)

注意: 与 VLOOKUP 不同,lookup_array 列位于 return_array 列的右侧,而 VLOOKUP 只能从左到右查找。

示例 5

接下来,我们将使用嵌套的 XLOOKUP 函数执行垂直和水平匹配。 在这种情况下,它将首先查找 B 列中的毛利润,然后查找表格首行(范围 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,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17))。

示例 6

此示例使用 SUM 函数,并嵌套两个 XLOOKUP 函数对两个范围之间的所有值求和。 在这种情况下,我们希望对葡萄、香蕉和 include 梨的值进行求和,这些值位于两个值之间。

结合使用 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 支持专员。

×