别再VLOOKUP了!这个函数才是Excel查询的天花板

小伙伴们好啊，今天咱们聊聊XLOOKUP这个函数。

先看它最基础的用法，公式长这样：

=XLOOKUP(查找内容,一行或一列的查找区域,要回传内容的区域)

拿下面这张表来说，要根据G3单元格里的姓名，在左边数据区域查这个人属于哪个部门。H3单元格的公式就是：

=XLOOKUP(G3,D2:D7,B2:B7)

先看数据长啥样：

G3里填的是"王五"，公式跑一遍，姓名列里找到"王五"在第4行，返回部门列第4行的值，结果就是"采购部"。

Excel 2021有个自动溢出的特性，所以下面这个公式能一次性把部门和职务都拉出来：

=XLOOKUP(G3,D2:D7,B2:C7)

还是G3="王五"，这次回传区域是部门和职务两列，结果会同时溢出成两个单元格：采购部、专员。

那要是想用多个条件查呢？看这个例子，根据G3的姓名和H3的部门，查对应的职务，公式这样写：

=XLOOKUP(G3&H3,D2:D7&B2:B7,C2:C7)

数据还是上面那张表，假设G3="王五"，H3="采购部"。第一参数拼成"王五采购部"，第二参数把姓名列和部门列拼起来逐行比对，第4行拼出来正好是"王五采购部"，匹配成功，返回职务列第4行的"专员"。

查不到内容的时候，第四参数能派上用场，让它返回指定内容或者跑别的计算。比如下面这个，找不到关键字就显示"找不到啊"：

=XLOOKUP(G3,D2:D7,B2:B7,"找不到啊")

G3里填个"钱九"，姓名列里压根没这人，公式直接返回"找不到啊"，不会甩个#N/A出来。

查数值的时候，第五参数可以指定匹配方式。看这个例子，要根据右边的对照表把E2的考核分转成等级，公式是：

=XLOOKUP(E2,A:A,B:B,,-1)

对照表长这样：

E2里填的是66。因为是近似匹配，不用屏蔽错误值，第四参数空着就行。第五参数填-1，意思是在分数列里找不到66时，就拿比66小的最接近那个值来匹配。分数列里比66小的有0和60，最接近的是60，于是返回等级列对应的"巴结"。

如果想在第一参数里用通配符做关键字查询，第五参数得写成2：

=XLOOKUP(G3&"*",D2:D7,B2:B7,,2)

还是用最开始那张表，G3里填"张"，拼成"张*"，在姓名列里找到以"张"开头的姓名"张三"，返回部门列对应的"采购部"。

查询区域里有多条符合条件的记录时，第六参数能控制返回第一条还是最后一条。比如部门列有两个采购部的记录（张三、王五），下面这个公式会匹配最后一条：

=XLOOKUP(G3,B2:B7,D2:D7,"找不到",,-1)

G3里填"采购部"，部门列里张三和王五两条都命中，第六参数-1表示取最后一条，返回姓名列里王五。想匹配第一条的话，这个参数省略或者写1，就会返回张三。

XLOOKUP还能玩二维查询，垂直和水平两个条件一起上。看这个例子，根据I2的部门和I3的月份，从左边表格里查对应的数值：

=XLOOKUP(I2,A2:A7,XLOOKUP(I3,B1:G1,B2:G7))

数据表长这样：

I2里填"技术部"，I3里填"4月"。这里套了两层XLOOKUP。里面那层XLOOKUP(I3,B1:G1,B2:G7)，是按I3的"4月"在月份行里找到对应列，返回数据区里那一列的所有数值，得到4月那一列的完整数据：150、108、88、220、145、130。外面那层XLOOKUP，就拿I2的"技术部"当查询值，部门列当查询区域，里面那层返回的结果当回传区域，在4月那列数据里找"技术部"对应的值，最终返回220。