神了，Excel的这个操作我今天才知道

文章来源：简说Python

作者：老表

大家好，我是老表，上一篇文章给大家分享了 Excel lookup家族的 hlookup和lookup，在更早之前还给大家分享了vlookup的使用，今天，我们来聊聊一个更高级，神职更高的函数 XLOOKUP。

得神人指点我才认识到这个函数，之前突然就记起来，前一段时间（可能是去年）是有Excel神级函数口号漫天宣扬，我以为是大家又认识到了vlookup的好，在吹vlookup，谁知？是我太垃圾了。

接下来开始正题？What is XLOOKUP？

首先这个X不像V（Vertica）和H（Horizontal），但是我们从小就知道X表示未知，这也赋予了xlookup一层神秘面纱，让人不禁神往，今天让我们揭开这层朦胧的面纱。

既然是lookup家族一员，功能必然是查找匹配相关，与其他人不同的是，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]：可选参数，指定匹配类型，和vlookup的[range_lookup]参数类似，但是比它的模式多 0  - 完全匹配。 如果未找到，则返回 #N/A。 这是默认选项。 -1 - 完全匹配。 如果没有找到，则返回下一个较小的项。 1 - 完全匹配。 如果没有找到，则返回下一个较大的项。 2 - 通配符匹配，其中 *, ? 和 ~ 有特殊含义。[search_mode]：可选参数，指定要使用的搜索模式：1 - 从第一项开始执行搜索。 这是默认选项。-1 - 从最后一项开始执行反向搜索。2 - 执行依赖于 lookup_array 按升序排序的二进制搜索。 如果未排序，将返回无效结果。-2 - 执行依赖于 lookup_array 按降序排序的二进制搜索。 如果未排序，将返回无效结果。参数解析来源：https://support.office.com/zh-CN/

这里主要和vlookup对比一下，所以我把vlookup的参数也放出来，lookup相关参数也放出来大家复习下。

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])lookup_value：必选参数，要查找的值，必须位于查找区域table_array的第一列。table_array：必选参数，VLOOKUP 在其中搜索 lookup_value 和返回值的单元格区域。col_index_num：包含返回值的列号（从1开始的table_array的最左侧列）。[range_lookup]：一个逻辑值，该值指定希望 VLOOKUP 查找近似匹配(1/True)还是精确匹配(0/False)，默认为近似匹配。
LOOKUP(lookup_value, lookup_vector, [result_vector])lookup_value：必选参数，要查找的值。lookup_vector：必选参数，只包含一行或一列的区域，且必须为升序排列。[result_vector]：可选参数，只包含一行或一列的区域。result_vector 参数必须与 lookup_vector 参数大小相同（指包含元素个数）。【注意】1、如果 LOOKUP 函数找不到 lookup_value，则该函数会与 lookup_vector 中小于或等于 lookup_value 的最大值进行匹配。2、如果 lookup_value 小于 lookup_vector 中的最小值，则 LOOKUP 会返回 #N/A 错误值。

1、从参数上看

xlookup有6个参数，而vlookup只有三个，主要是xlookup多了[if_not_found],[search_mode]这两个可选参数，另外[match_mode]相比于vloookup的[range_lookup]匹配模式更加齐全。

比较重要的一点，xlookup和lookup一样，把查找区域和结果区域分开了，所以也算多了一个参数return_array，这使得xlookup可以和lookup一样支持反向查找，但加上其他参数，明显功能优于lookup和vlookup。

2、从功能上看

a.其实上面已经说了一点了，xlookup支持反向查找（也就是结果列在被查找值所在列的左侧），这一点vlookup也可以实现但是麻烦。

VLOOKUP(D23,IF({0,1},C12:C18,D12:D18),2,0) XLOOKUP(D23,D12:D18,C12:C18)

xlookup函数默认是精确匹配，所以只需设置前三个必选参数即可；vlookup本身不支持反向查找，所以需要借助if函数来构建一个新的查询区域，也就是把要查找的值所在列移到最前面，这里用到了excel中的数组{0,1}。

IF(判断条件,判断条件为真时返回什么,判断条件为假时返回什么)IF({0,1},C12:C18,D12:D18)当{0,1}取0时返回 D12:D18，取1时返回C12:C18，最终返回一个新的区域：D12:D18列在前，C12:C18在后的一个数据区域。

b.xlookup支持一次返回多个匹配值，vlookup和lookup都只能返回一个匹配值。

/ XLOOKUP(D16,B4:B10,D4:E10)

xlookup的return_array 参数可以接受多列数组，使得xlookup可以一对多查询。

c.对[match_mode]参数设置可以近似匹配较大或较小的项，甚至可以通过通配符进行匹配。

VLOOKUP(E15,I4:J10,2,1)    XLOOKUP(E16,I4:I10,J4:J10,'没有找到',-1,1)    VLOOKUP(E17,L4:M10,2,1)    XLOOKUP(E18,L4:L10,M4:M10,'没有找到',-1,1)

这里需要注意，vlookup近似匹配时需要匹配数组为升序排列才能查询出正确结果，而xlookup则无此要求。

XLOOKUP(E16,I4:I10,J4:J10,'没有找到',-1,1)

上面一段公式中，前三个必选参数我不解释了可以直接看文中xlookup参数介绍了解，公式最后两个参数：-1表示在完全匹配的前提下如果没有匹配到，就返回小于查找值中最大的一项，此时也就相当于近似匹配，1表示从第一项开始查找，默认值。

d.对[search_mode]参数设置可以实现从前往后查找或者从后往前查找，也能根据顺序查找。

VLOOKUP(D15,D4:E11,2,0)    XLOOKUP(D15,C4:C11,D4:D11)    XLOOKUP(D15,D4:D11,E4:E11,'没有找到',0,-1)