青出于蓝的Excel函数：XLOOKUP
VLOOKUP函数是所有使用Excel的朋友对非常熟悉的一个函数。尽管它有这样那样的缺陷，但是我们还是离不开它。几乎，在Excel的各种应用场景中，我们都会发现它的身影。为了更好地使用这个函数解决各种实际问题，我们还发明了很多方法，写出一个一个复杂又巧妙的公式。很多朋友对这些公式（也是对VLOOKUP）是爱恨交加，既认为这个函数和这些公式可以解决自己那些难以处理的工作问题，同时，又觉得这些复杂的公式难度有点高，很难理解，也很难记住，更不用说举一反三，灵活使用了。
现在，这一切已经改变了。这些改变的发生都是源于一个新的函数：XLOOKUP。
2019年8月28日，微软发布了一个新的Excel函数：XLOOKUP。作为LOOKUP系列函数家族的新生力量，它可以比它的前辈更好的完成各种查找场景的工作。
与传统的VLOOKUP函数相比，XLOOKUP函数至少具有下面的优点：
XLOOKUP函数可以进行“反向查找”。
VLOOKUP函数只能返回查找值右边的列，如果要返回查找值左边的列，要么结合其他的函数（或者使用数组），要么修改源数据。而XLOOKUP可以很灵活的返回查找区域的任意列。XLOOKUP可以查找最后一个匹配值。
VLOOKUP函数只能返回第一个匹配值。在那些需要返回最后一个匹配值的场景中，需要大费周章。在查找区域中插入或者删除列后，XLOOKUP公式可以自动变化。
VLOOKUP函数的第三个参数是个数字，表示返回查找区域的相对列号。如果查找区域中插入和删除列，这个数字不会自动变化。缺省情况下，XLOOKUP函数进行精确匹配。
很多人使用VLOOKUP函数的错误是由于省略最后一个参数造成的。因为，最后一个参数表示匹配方式，如果省略这个参数，缺省值是近似匹配。这个“别扭”的设置导致我在每一个培训课上都会强调：不要省略最后一个参数。XLOOKUP函数没有这个问题了，因为缺省情况下，XLOOKUP函数进行的是精确匹配。XLOOKUP函数可以返回一个单元格区域。
XLOOKUP函数既可以像VLOOKUP函数一样返回一个单元格，也可以像INDEX一样返回一个区域。这是非常有用的特性。XLOOKUP自带错误处理机制。
VLOOKUP找不到匹配结果时会返回#N/A错误。需要使用IFERROR函数来处理。但是XLOOKUP不用这么麻烦！
说了这么多，你是不是对XLOOKUP函数很有兴趣了。下面我们一起看看这个函数是如何使用的。
01
XLOOKUP的语法
按照惯例，我们先来看看这个函数的语法：
这个函数有6个参数：
lookup_value
查找值，表示你希望用来匹配的条件。可以是直接输入的值，也可以是单元格引用。lookup_array
查找区域，是个数组或者单元格区域（只能一列或者一行），用来与查找值进行比对。return_array
返回区域。你希望返回的值所在的单元格区域或者数组（可以多列或多行）if_not_found
如果没有找到匹配值，XLOOKUP就返回这个参数。这个参数是可以省略的。如果省略，并且没有找到匹配值，将返回#N/Amatch_mode
匹配方式，有4个值可以选择：0-精确匹配，-1-精确匹配或者比查找值小的值中最大的那个值，1-精确匹配或者比查找值大的值中最小的那个值，2-通配符匹配。缺省情况下，是精确匹配。search_mode
搜索方式，有4个值可以选择：1-从前往后搜索，-1-从后往前搜索，2-二分法搜索（升序），-2-二分法搜索（降序）。缺省情况下，是第一种搜索方式）—从前往后搜索。
单纯看这些参数，可以有点隔靴搔痒。下面我们结合例子来看这个函数的使用和各参数的意义。
02
使用XLOOKUP的例子
例1 最简单的匹配查找
在这个例子中，我们使用XLOOKUP查找满足B14中的值“芬达苹果”的记录，需要在C3:C10区域进行匹配，返回E3:E10区域中的对应值。这里可以看出，由于返回区域可以指定范围而不是数字，从而可以进行“反向查找”。由于后面的参数省略了，所有采用的是精确匹配。
这个公式的结果等价于公式：
=VLOOKUP(B14,C3:E10,3,0)例2 同时返回多项
乍看上去，这个例子跟例1一样。但是仔细看，在这个例子中，我们使用一个公式返回了多列的结果（数量和金额），要点在与返回区域的参数从一列（E3:E10)变成了两列(D3:E10)。由于返回了两列的对应值，因此，结果“溢出”了。
例3 匹配不成功的处理
前两个例子中，没有指定匹配不成功的处理方式，因此，如果找不到，就会返回#N/A
找不到，所有返回#N/A。由于匹配不成功，所以，尽管指定返回两列，结果也没有“溢出”。
如果不希望返回错误值，可以使用第4个参数：
我们将第4个参数输入文本“找不到”，这样当匹配不成功时，就会返回这个值。
例4 近似匹配
使用VLOOKUP函数进行近似匹配最典型的例子就是个人所得税的计算。XLOOKUP做起来也很简单：
这个公式将XLOOKUP函数的所有参数都写全了。如果匹配不成功，就返回0，第5个参数是-1，表示精确匹配或者比查找值小的最大值。最后一个参数表示从前往后查找。
这个公式本身的结果跟VLOOKUP的使用是一样的。但是需要指出的是：最后一个参数的使用不影响查找的结果。在VLOOKUP函数中，如果要用近似匹配，查找区域必须按照第一列查找值进行升序排序。而在XLOOKUP中，不再有这样的要求了。
例5 返回动态区域
在前面的例子中，返回区域都是写死的。实际上，我们经常需要根据某个参数确定要返回的列。这是可以使用两个XLOOKUP结合：
这里，我们使用了XLOOKUP公式来确定需要返回的结果，如果单步执行，可以看到这个内层的XLOOKUP公式返回的是D3:D10区域。
这个例子实际上提示我们，XLOOKUP函数可以返回一个区域。在下面的例子中，更好的说明了这一点。
例6 返回一个区域
在这个例子中，第一个XLOOKUP返回的是D5单元格，而第二个XLOOKUP返回的是D11，因此可以使用SUM函数进行D5:D11的求和。
例7 返回最后一个值：
在这个例子中，左边一个XLOOKUP公式返回的是第一个匹配成功的结果。而右边一个XLOOKUP返回的是最后一个匹配成功的结果。
03
总结和其他
XLOOKUP函数中还有一些其他要注意的地方，比如要进行通配符的匹配时，必须将第5个参数指定为2。另外，这个函数返回多个不连续的列时需要结合CHOOSE等函数使用等。这里就没有过多涉及。这些技巧有待大家熟悉后，逐渐挖掘设计。
值得一提的是，在官方的说法中，XLOOKUP的精确匹配算法重新进行了设计，所以速度非常快。有人说精确匹配和近似匹配没有明显的差距了（大家知道，VLOOKUP的精确匹配和近似匹配的计算速度相差数百倍）。这个我没有验证，根据我的理解，精确匹配的改进主要是返回多列时的速度更快了。具体这方面的内容等我有时间了进行一下测试，再跟大家交流。如果大家有这方面的经验，也可以留言告诉我。
Excel变简单，从此不加班！加入E学会，学习更多Excel函数和数据处理技巧。一次加入，永久有效。