简说Python，号主老表，Python终身学习者，数据分析爱好者，从18年开始分享Python知识，原创文章227篇，写过Python、SQL、Excel入门文章，也写过Web开发、数据分析文章，老表还总结整理了一份2022Python学习资料和电子书资源，关注后私信回复：2022 即可领取。
大家好，我是老表，上一篇文章给大家分享了 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)
:58这里需要注意，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)
太惨了，一篇文章只能传三个视频，所以，只能上传gif，但是压缩严重，后台回复：XLOOKUP，可以获取本文全部相关视频。vlookup的匹配查找只支持从上往下查找，当查询区域中我们的目标值出现多次的时候，我们用vlookup永远只能得到第一个出现的目标值（特殊处理方法这里我们不考虑），而xlookup则提供了多种查找方式，从前往后，从后往前，还可以根据查找序列的顺序来。e.对[if_not_found]参数设置可以提高表格的可阅读性。
VLOOKUP(C15,IF({0,1},B4:B10,C4:C10),2,0) XLOOKUP(C15,C4:C10,B4:B10,"查无此人")
总结总的来说xlookup想比于vlookup便利了很很很多。唯一的遗憾是目前可能大多数读者朋友都还无法使用这个功能，目前该功能只对Microsoft 365用户开放，而大多数读者应该都使用的excel 2016或者更之前的版本，不过，总有一天这个功能会让所有的office用户都用上，但具体是多久？看官方了。
庆幸的是，到目前为止vlookup还是够用的，也是值得崇拜的，实在不够用，lookup家族不是还有index和match吗，没得怕。
