昨天写了一篇vlookup函数各种用法合集的文章,有个小伙伴给打赏了。很感谢这位小伙伴,因为这是对我的一种认可,感觉自己写的东西对你们是有价值的。也感谢其他帮忙点赞转发的小伙伴,我会尽力发一些对大家有帮助的文章。如果觉得文章对你有帮助,可以帮忙打赏或点赞转发,这对于我来说也是一种激励。
言归正传,今天分享一下lookup函数的各种用法。lookup也是一个查找引用函数,它的用法更加灵活多样,所以作用也比较大。采用的是二分法查找方式。
-lookup-
各种用法合集
1.返回最后一个数值
如下图所示,要查找E列的最后一个数字,在G2单元格输入公式=LOOKUP(9E+307,E:E)。9E+307在excel中是一个相当大的数字,是9乘以10的307次方。
2.返回最后一个文本
如下图所示,要返回B列中最后一个文本,输入公式=LOOKUP("做",B:B)。"做"的字符编码排位也是靠后面的。
3.返回最后一个非空单元格的内容
E列中既有数字也有文本,要返回最后一个单元格的内容,是用9e307还是"做"呢?好像都不行,这时可以用非空进行判断,返回最后一个非空单元格的内容就可以了。公式为=LOOKUP(1,0/(E:E<>""),E:E)。其实这也是个单条件查询。
4.填充合并单元格
如下图所示,D列中的部门是合并单元格,现在要将其填充成E列那样。在E3单元格输入公式=LOOKUP("做",D$3:D3),向下填充。
5.带合并单元格的查询
如下图所示,左表的部门是合并单元格,现在要根据E3单元格的姓名查询出它在左表中对应的部门。在F3单元格输入公式=LOOKUP("做",A3:INDEX(A3:A11,MATCH(E3,B3:B11,)))。还可以用offset和indirect做到,可以下载文件自己查看。
6.逆向查询(单条件查询)
如下图所示,要根据G3单元格的姓名逆向查询出它在左表中对应的编号,在H3单元格输入公式=LOOKUP(1,0/(B3:B11=G3),A3:A11)。这是lookup单条件查询的经典套路。可以归纳为lookup(1,0/(条件区域=条件),返回区域)。如果有多个结果满足,它只返回最后一个结果。
7.多条件查询(返回最后一个成立的)
如下图所示,要根据G3单元格的学历和H3单元格的部门来查询出所对应的姓名,也就是学历是高中且部门是生产部所对应的姓名。在I3单元格输入公式=LOOKUP(1,0/((C3:C11=G3)*(D3:D11=H3)),B3:B11)。
这是lookup函数多条件查询的套路,可以归结为lookup(1,0/((条件区域1=条件1)*(条件区域2=条件2)),返回区域)。从上图中可以看出,满足条件的有2个,一个是老王,一个是小古,但是它只返回最后一个,这是lookup函数的特性。
8.区间查询(升序排序)
如下图所示,根据分数判断等级,判断的标准如右表所示。如果要用if函数判断,要嵌套好几层,容易写错。如果用lookup那就简单了。在E3单元格输入公式=LOOKUP(D3,{0,60,80,90},{"差";"中";"良";"优"})。这里第2,3参数都是用的常量数组,当然也可以写在单元格里引用。要注意的问题是第2参数必须以升序排序。
9.根据简称查询全称
现在要根据A列的地址简称在D列中查询出对应的全称,结果放到B列中,在B3单元格输入公式=IFNA(LOOKUP(,-FIND(A3,D$2:D$12),D$2:D$12),"")。
10.根据全称查询简称
现在要根据A列的地址全称在D列中查询出对应的简称,结果放到B列中,在B3单元格输入公式=IFNA(LOOKUP(,-FIND(D$3:D$11,A3),D$3:D$11),"")。
11.提取有规律的数字
如下图所示,A列是一些信息,其中包括手机号码。现在要将手机号码提取出来放在B列。在B3单元格输入公式=-LOOKUP(,-MID(A3,ROW($1:$99),11))。
12.按指定次数重复内容
A列是要重复的内容,B列是重复的次数,结果如E列所示。首先在C列增加一个辅助列,在C3单元格输入公式=SUM(B$2:B2),下拉到C7单元格。然后在E2单元格输入公式=LOOKUP(ROW(A1)-1,C$3:C$7,A$3:A$6)&"",向下填充。
链接:
https://pan.baidu.com/s/1C5o6m0iPK-PVpzIAIPkrFw
提取码:tpo0
联系客服