excel中强大的查询函数：xlookup函数

我们今天来讲讲XLOOKUP，一个强大的查询函数！

在说XLOOKUP函数之前，咱们先看看下面这个案例，根据姓名查找对应的性别。

这个问题太常见了！首先想到的就是用VLOOKUP函数。=VLOOKUP(E2,A2:C7,2,0)

在查找区域A2:C7的首列找到E2单元格的值「王五」，返回查找区域A2:C7第2列与之对应的值「男」。

初识XLOOKUP函数

再来看看XLOOKUP函数的用法：

=XLOOKUP（查找的值，查找范围，结果范围）

公式就可以这样写：=XLOOKUP(E2,A2:A7,B2:B7)

在查找范围A2:A7中找到E2单元格的值「王五」，返回B2:B7对应的值「男」。

如果姓名这列不是在前面，用VLOOKUP函数似乎就不太合适了。因为VLOOKUP函数的规则是在查找区域的首列查找。

这种情况下，我们通常会用 INDEX函数和MATCH函数组合写公式：=INDEX(A2:A7,MATCH(E2,B2:B7,0))

MATCH函数找出E2的值「王五」在B2:B7中是第几行，得到结果3，然后用INDEX函数将A2:A7的第3行的值引用出来，得到结果「男」。

而XLOOKUP函数就不一样了，它不会像VLOOKUP那样受位置的影响，依然照用不误：=XLOOKUP(E2,B2:B7,A2:A7)

在查找区域B2:B7中找到E2单元格的值「王五」，返回A2:A7对应的值「男」。

XLOOKUP函数的第4参数

经常有小伙伴提这样的问题，如何让VLOOKUP查找不到的数据返回为空值？

如下图，VLOOKUP函数在查找区域A2:B7的首列没有找到单元格的值「孙二」,就会返回错误值#N/A。=VLOOKUP(D2,A2:B7,2,0)

通常我们都会在VLOOKUP函数外层嵌套IFERROR函数，或者用IFNA函数来容错。=IFNA(VLOOKUP(D2,A2:B7,2,0),"")

而XLOOKUP函数有它专属的参数：它的第4个参数专门负责容错。

=XLOOKUP（查找值，查找范围，返回范围，[容错]）

这个参数是非必需参数，当你碰到上面这种问题，才有必要把它请出来。

公式就可以写成：=XLOOKUP(D2,A2:A7,B2:B7,"")

在查找范围A2:A7中找E2单元格的值「孙二」，如果有找到就返回B2:B7对应的值，如果没有找到，则返回第4参数指定的内容「""」。

当然第4参数的设定并非只可以是字符串，数值。也可以嵌套其它的公式返回结果。

XLOOKUP函数的第5参数

以下是评定的规则：

小于60分不合格；

大于等于60小于70为合格；

大于等于70小于80为良好；

大于等于80为优秀。

先为每个等级设置分数的下限，如下图A列，然后在E2单元格写入公式：=XLOOKUP(D2,A2:A5,B2:B5,,-1)

XLOOKUP函数的第5参数是匹配类型。

=XLOOKUP（查找值，查找范围，返回范围，[容错]， [匹配类型])

当第5参数的值为-1时，表示如果「查找值」没有在「查找范围」中，就返回下一个较小的值。

如上面的公式中，D2单元格的值是75，没有在查找区域A2:A7中，就找比75小的值，即70。再返回B2:B5对应的等级「良好」。

如果把A列的分数下限改成上限，公式就可以这样写：=XLOOKUP(D2,A2:A5,B2:B5,,1)

当第5参数为1时，表示如果「查找值」没有在「查找范围」中，就返回下一个较大的值。

如公式中，D2单元格的值是75，没有在查找区域A2:A7中，就找比75大的值，即79。再返回B2:B5对应的等级「良好」。

写到最后

关于VLOOKUP、XLOOKUP和LOOKUP有哪些区别，以下Tips供大家参考：

❶VLOOKUP函数必须在查找区域的首列查找，而XLOOKUP函数不受这种位置限制；

❷VLOOKUP函数需要其它函数嵌套来容错，而XLOOKUP函数有自己的参数做容错处理，更方便；

❸LOOKUP函数在多值判断时，需要升序排序，而XLOOKUP函数可以不用排序。