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 参数, 因为它默认为精确匹配。
注意: XLOOKUP 与 VLOOKUP 的不同之处是它使用单独的查找和返回数组, 其中 VLOOKUP 使用一个表数组, 后跟一个列索引号。 在此情况下, 等效的 VLOOKUP 公式为:
= VLOOKUP (F2, B2: D11, 3, FALSE)
示例 2
以下示例在列 C 中查找在单元格 E2 中输入的个人收入, 并在列 B 中查找匹配的税率费率。它使用 match_mode 参数设置为 1, 这意味着该函数将查找精确匹配, 如果找不到它, 它将返回下一个较大的项。
注意: 与 VLOOKUP 不同, lookup_array 列位于 return_array 列的右侧, 而 VLOOKUP 只能从左到右查看。
示例 3
接下来, 我们将使用嵌套的 XLOOKUP 函数同时执行垂直和水平匹配。 在这种情况下, 它将首先在列 B 中查找毛利润, 然后在表的首行中查找 “第 1季度” (区域 C5: F5), 并返回二者相交处的值。 这类似于结合使用INDEX和MATCH函数。
单元格 D3 中的公式: F3 为:
= XLOOKUP (D2, $B 6: $B 17, XLOOKUP ($C 3, $C 5: $G 5, $C 6: $G 17))。
示例 4
此示例使用SUM 函数和两个 XLOOKUP 函数嵌套在一起, 对两个区域之间的所有值求和。 在这种情况下, 我们希望对葡萄、香蕉和梨的值进行求和, 这些值位于两个值之间。
单元格 E3 中的公式为:
= SUM (XLOOKUP (C3, C6: C10, F6: F10): XLOOKUP (D3、C6: C10、F6: F10))
它如何工作？ XLOOKUP 返回一个区域, 因此当它计算时, 该公式最后看起来如下所示: = SUM ($F $7: $F $9)。 你可以通过选择带有类似 XLOOKUP 公式的单元格来查看它的工作方式, 然后转到公式>公式审核>计算公式, 然后按 “求值” 按钮逐句通过计算。