在查找引用函数中,很多EXCEL使用者都喜欢用三剑客index+small+if,几乎成了法则,但是这个公式,稍微多一点数据,就卡的要死,现在我们介绍的,是可以实现同功能的高效函数Vlookup,如果亲感兴趣,欢迎添加关注,或是私信讨论,我们愿意一起进步
工具/原料
EXCEL2003-2016
电脑一台
方法/步骤
1、新建EXCEL表格,输入演示数据,要求达到黄色区域通过公式实现,输入地名,所有包含的全部出来
2、在J142单元格输入公式=IFERROR(VLOOKUP(K$141&RO掳姗浚瑙W(A1),IF({1,0},H$142:H$153&COUNTIF(INDIRECT烫喇霰嘴("H$142:H"&ROW($142:$153)),K$141),I$142:I$153),2,FALSE),"")而后三键结束,就是同时按住ctrl+shift+enter,得到输入公式,就是公式两端出现大括号就OK了
3、下拉J142公式,得出全部结果
方法2
1、在K142输入公式=VLOOKUP(K$141&ROW(A1),H$142:I$153&COUNTIF(INDIRECT("H$142:H"&ROW($142:$153)),K$141),2,FALSE)而后三键结束,得到输入公式,获取第一个值
2、下拉K142公式,获取所有符合条件的值,这时我们看到,每个值后面自动添加了符合条件的顺序,这是因为H$142:I$153&COUNTIF(这个部分吧HI列后的每个值,自动添加上了序号,这个方法,在有些时候很有用,
3、下面我们尝试改变公式,将裼沙钔炯上步出现的序列号清除,首先在L142输入公式=IFERROR(SUBSTITUTE(VLOOK炽扃仄呦UP(K$141&ROW(A1),H$142:I$153&COUNTIF(INDIRECT("H$142:H"&ROW($142:$153)),K$141),2,FALSE),ROW(A1),""),"")而后三键结束,获得第一个值,
4、下拉L142,获取所有值,我们发现完全正确,高兴之余,我们讲人名都改成了1,2,3,4,5,会发现出错,因为这个公式,只能使用在中文状态,如果为数字,会导致替换的时候,把不该替换的都替换了,-------------如何既能中文,又能数字呢?-----------继续往下看
5、在M142输入公式=IFERROR(SUBSTITUTE(VLOOKUP(K$14龀音孵茧1&ROW烫喇霰嘴(A1),H$142:I$153&COUNTIF(INDIRECT("H$142:H"&ROW($142:$153)),K$141),2,FALSE)&"小日本",ROW(A1)&"小日本",""),"")而后三键结束,,确定,获取第一个
6、下拉M142我们回去所有值
7、下面我们验证,我们就会发现,中文状态,都OK,数字时,最后一个公式,我门也解决了,也是正确的,完美收工