XLOOKUP函数

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

在此示例中,我们将根据员工 ID 编号查找员工信息。 与 VLOOKUP 不同的是,XLOOKUP 能返回包含多项的数组,一个公式即可返回员工姓名和部门两项。


XLOOKUP 函数


注意: 2019 年 8 月 28 日:XLOOKUP 目前为 beta 功能,仅面向一部分 Office 预览体验成员提供。 我们将在未来的几个月里继续优化该功能。 XLOOKUP 准备就绪后,我们会向所有 Office 预览体验成员和 Office 365 订阅者发布该功能。


 语法

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

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


参数

说明

lookup_value

查找值

lookup_array

要搜索的数组或区域

return_array

要返回的数组或区域

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


示例 2

以下示例在 C 列中查找单元格 E2 中输入的个人收入,并在 B 列中找到匹配的税率。它使用 match_mode 参数设置为 1,这意味着该函数将查找完全匹配,如果找不到,将返回下一个较大的项。


XLOOKUP 函数


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

示例 3

接下来,我们将使用嵌套的 XLOOKUP 函数执行垂直和水平匹配。 在这种情况下,它将首先查找 B 列中的毛利润,然后查找表格首行(范围 C5:F5)中的第一季度,并返回两者交集的值。 这类似于结合使用 INDEX 和MATCH 函数。 你也可以使用 XLOOKUP 替换 HLOOKUP 函数。


XLOOKUP 函数


单元格 D3:F3 中的公式是:=XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17))。

示例 4

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


XLOOKUP 函数


单元格 E3 中的公式是:=SUM(XLOOKUP(C3,C6:C10,F6:F10):XLOOKUP(D3,C6:C10,F6:F10))

它如何工作? XLOOKUP 返回一个单元格区域,因此在计算时,该公式最后看起来如下所示:=SUM($F$7:$F$9)。 可通过选择包含与此类似的 XLOOKUP 公式的单元格来查看其工作原理,然后转到公式> 公式审核 > 公式求值,再按“求值”按钮逐步执行计算。

注意: 感谢 Microsoft Excel MVP Bill Jelen 推荐此示例。

 ——转载于Excel for Office 365 Excel for Office 365 for Mac Excel 网页版

相关推荐