编按: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精品好课任你选择! 相关推荐: 版权申明: 本文作者Mutou;部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。 |
|
来自: 部落窝教育BLW > 《部落窝excel/VBA》