Excel函数XLOOKUP常规用法：参数真不少，数据查找有哪些优势？

编按：XLOOKUP函数参数太多：6个参数。那这些参数带来了什么呢？是否让它更灵活呢？今天首先来看看它在常规用法上与VLOOKUPLOOKUP的对标。

XLOOKUP有6个参数，横竖皆可，正反都来，有一点通吃的感觉。

=XLOOKUP(查找值,查找区域或数组,返回区域或数组,找不到后要返回的值,匹配类型,搜索模式)

1. 大多时候你用前三个参数工作

不要担心参数多，多数你用的是前3个。

=XLOOKUP(查找值,某一行或某一列查找区域,返回区域)

为啥？

匹配类型默认为完全匹配，数值为0，与VLOOKUP一样。省略不写，就是默认。

搜索模式默认为从上往下从左到右，数值为1，与VLOOKUP一样。省略不写，就是默认。

第4参数可以不写。不写的最大后果就是查找不到的时候显示错误值。

因为上面的特点，所以大多数你用的是前3个。

2.常规用法

1）精确查找

（1）返回单个值（前提：返回区域是单行或单列）

譬如根据姓名查分数。

=XLOOKUP(G2,A2:A10,E2:E10)

对比参考：

VLOOKUP公式=VLOOKUP(G2,A2:E10,3,0)

LOOKUP公式=LOOKUP(1,0/(A2:A10=G2),E2:E10)

继承LOOKUP查找区域和返回区域各自独立的特点，XLOOKUP查找也能轻松完成反向查找。譬如根据学号查姓名。

=XLOOKUP(G6,C2:C10,A2:A10)

对比参考：

VLOOKUP公式=VLOOKUP(G6,IF({1,0},C2:C10,A2:A10),2,0)

LOOKUP公式 =LOOKUP(1,0/(C2:C10=G6),A2:A10)

（2）返回一行或者一列值（前提：返回区域是多行多列）

譬如根据姓名查其所有信息。

=XLOOKUP(A14,A2:A10,B2:E10)

对比参考：

VLOOKUP公式，高版本=VLOOKUP(A14,A2:E10,{2,3,4,5},0)

低版本=VLOOKUP($A14,$A2:$E10,COLUMN(B2),0)，然后右拉填充

LOOKUP公式 =LOOKUP(1,0/($A2:$A10=$A14),B2:B10)，然后右拉填充

再譬如查所有总分。

=XLOOKUP(G8,B1:E1,B2:E10)

对比参考：

VLOOKUP公式,高版本=VLOOKUP(G8,TRANSPOSE(A1:E10),ROW(2:10),0)

TRANSPOSE是转置函数，把表格横竖方向换位。VLOOKUP只能纵向查找，所以此处需要TRANSPOSE的帮忙。

低版本=VLOOKUP(G$8,TRANSPOSE(A$1:E$10),ROW(A2),0),然后下拉填充

LOOKUP公式 =LOOKUP(1,0/(A$1:E$1=G$8),A2)，然后下拉填充。

2）模糊查找，典型的区间或者等级查找，第5参数-1或1

=XLOOKUP(查找值,某一行或某一列查找区域,返回区域,,-1或1)

-1，表示找小于等于自己的最大数。效果与平常的VLOOKUP模糊查找一致。

1，表示找大于等于自己的最小数。

查找区域不需要排序！不需要排序！不需要排序！这与VLOOKUP或者LOOKUP模糊查找最大不同。

譬如根据分数查等级。

=XLOOKUP(G9,$B$17:$E$17,$B$16:$E$16,,-1)

对比参考：

VLOOKUP公式

如果不改变当前的等级和分值布局，要转置还要交换列顺序，公式比较复杂：

=VLOOKUP(G9,IF({1,0},INDEX(TRANSPOSE($B$16:$E$17),0,2),INDEX(TRANSPOSE($B$16:$E$17),0,1)),2,1)

LOOKUP公式=LOOKUP(G9,$B$17:$E$17,$B$16:$E$16)，然后下拉填充。

打乱查找区域排序，结果仍然正确：

对比参考：

VLOOKUP公式：对不起，我无法

LOOKUP公式：对不起，我无法。

额外说明：

第6参数中的升序（数字2）、降序（数字-2）只用于按VLOOKUP或者LOOKUP工作模式进行模糊匹配。设置2，好比使用VLOOKUP或者LOOKUP进行模糊查找，那就必须让查找区域升序排列， 否则结果可能是错的；设置为-2，就必须让查找区域降序排列，否则结果也可能是错的。

只要不写第6参数，或者让其保持默认值1，则模糊查找时，不需要排序就能得到正确结果。这就是第6参数中的2、-2很少使用的原因。

3.通配符查找：必须2一下（第5参数）

支持查找包含某某字符的数据，与VLOOKUP通配符查找功能一样，但没有VLOOKUP方便，必须要多写一个第5参数“2”。2表示按通配符查找。

譬如，查找姓名中含“坤”的人员。

=XLOOKUP("*坤",A2:A10,A2:A10,,2)

注意返回的是从上往下第一个含“坤”人员。

如果打破第6参数的默认，写成-1，则返回从下往上查的第一个：

=XLOOKUP("*坤",A2:A10,A2:A10,,2,-1)

对比参考：

VLOOKUP公式=VLOOKUP("*坤",A2:A10,1,0)（返回“唐坤”）

LOOKUP公式=LOOKUP(99,FIND("坤",A2:A10),A2:A10)（返回“毕开坤”）

提示：第6参数搜索模式1或者-1，让XLOOKUP更灵活。默认1，从上往下，效果等同VLOOKUP精确查找；-1，从下往上，效果等同LOOKUP精确查找套路——有相同值的返回最后一个。

4.多条件查找：保持了VLOOKUP的合并条件做法

可以把多个条件合并在一起，然后将查找区域也合并在一起，实现多条件查找。

譬如，有两个杨开颜，只找2组的杨开颜的总分。

=XLOOKUP(A13&B13,A2:A10&B2:B10,E2:E10)

对比参考：

Vlookup公式=VLOOKUP(A13&B13,IF({1,0},A2:A10&B2:B10,E2:E10),2,0)

LOOKUP公式=LOOKUP(1,0/((A2:A10=A13)*(B2:B10=B13)),E2:E10)

OK，关于XLOOKUP的常规用法今天就说到这里。

不知通过三个函数的对标，你是否对XLOOKUP了解更深了？是否更喜欢了？

做Excel高手，快速提升工作效率，部落窝教育Excel精品好课任你选择！

学习交流请加微信hclhclsc进群领取资料

相关推荐：

LOOKUP函数经典用法7例

LOOKUP二分法原理与查找规律

深度解读VLOOKUP四大参数以及最新用法

液面效果百分比图

版权申明：

本文作者Mutou；部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。