Excel函数XLOOKUP常规用法：参数真不少，数据查找有哪些优势？
发布日期：2024-07-22 09:47 点击次数：141
编按：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精品好课任你选择！
相关推荐：
LOOKUP函数经典用法7例
LOOKUP二分法原理与查找规律
深度解读VLOOKUP四大参数以及最新用法
液面效果百分比图
版权申明：
本文作者Mutou；部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。
本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。
