Excel函数|VLOOKUP的升级Xlookup来了

Excel中有几个非常常见的函数,Vlookup是其中常见之常见。很多时候我们说数据匹配一下,捞一下数据的,都是代指使用vlookup函数来做操作。Vlookup有个兄弟函数,就是Hlookup,平常使用的较少。Vlookup平常使用中也有不少的问题,所以最近微软发布了新的Excel函数xlookup,完全是vlookup跟hlookup的结合后的升级。目前微软office365内测版本才支持使用,还是在测试中。

Xlookup支持从右向左查、多条件查找、从上向下查、查找最后一个,基本上囊括了前期的lookup、vlookup、hlookup等函数的功能。

语法

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 (range B2: B11) 和 return_array (range D2: D11) 参数。 它不包含 match_mode 参数, 因为它默认为精确匹配。

图1.示例1-xlookup函数使用

注意: XLOOKUP 与 VLOOKUP 的不同之处是它使用单独的查找和返回数组, 其中 VLOOKUP 使用一个表数组, 后跟一个列索引号。 在此情况下, 等效的 VLOOKUP 公式为: 

= VLOOKUP (F2, B2: D11, 3, FALSE)

示例 2

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

图2.示例2-xlookup函数使用

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

 

示例 3

接下来, 我们将使用嵌套的 XLOOKUP 函数同时执行垂直和水平匹配。 在这种情况下, 它将首先在列 B 中查找毛利润, 然后在表的首行中查找 “第 1季度” (区域 C5: F5), 并返回二者相交处的值。 这类似于结合使用INDEX和MATCH函数。

图3.示例3-xlookup函数使用

单元格 D3 中的公式: F3 为:

= XLOOKUP (D2, $B 6: $B 17, XLOOKUP ($C 3, $C 5: $G 5, $C 6: $G 17))。

 

示例 4

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

图4.示例4-xlookup函数使用

单元格 E3 中的公式为:

= SUM (XLOOKUP (C3, C6: C10, F6: F10): XLOOKUP (D3、C6: C10、F6: F10))

 

它如何工作? XLOOKUP 返回一个区域, 因此当它计算时, 该公式最后看起来如下所示: = SUM ($F $7: $F $9)。 你可以通过选择带有类似 XLOOKUP 公式的单元格来查看它的工作方式, 然后转到公式>公式审核>计算公式, 然后按 “求值” 按钮逐句通过计算。

 

参考:https://support.office.com/zh-cn/article/xlookup-%E5%87%BD%E6%95%B0-b7fd680e-6d10-43e6-84f9-88eae8bf5929