返回

实务交流

发帖

教你破解VLOOKUP函数三大疑难!

Excel不加班楼主

2018-09-21 09:43:59 274 3

VLOOKUP函数很好用,但也有很多问题。


下面都是读者提出来的疑问,卢子按自己的理解模拟数据说明。


这个可能是返回多列的对应值,也可能是根据多个条件查找,分成两种情况说明。


1.返回多列的对应值


根据凭证号依次查找对应的日期、打款人和金额。在B2输入公式,下拉和右拉。

=VLOOKUP($A2,$F:$I,COLUMN(B1),0)


这里的关键点:

01 $A2用混合引用,这里右拉的时候单元格就不会变,下拉的时候可以变动。


02 COLUMN(B1)可以依次获取列号,从而避免手工输入2,3,4。列号就是看字母依次数过去,A对应1,B对应2,C对应3,D对应4。


2.多条件查找对应值


两边的数据由不同的人记录,现在要根据日期、打款人和金额三个条件,核对金额是否正确。


这里当然也可以用VLOOKUP函数解决,不过比较繁琐,当做一种参考即可。


Step 01 两边都用&将内容合并起来。


在A12输入公式下拉。

=A1&B1&C1


在F12输入公式下拉。

=F2&G2&H2


Step 02 再用VLOOKUP函数对辅助列进行查找。在D13输入公式下拉。

=IF(ISNA(VLOOKUP(A13,F:F,1,0)),"×","√")


根据多列查找对应值,都是用类似的方法实现。如果是要根据多列核对,一般用COUNTIFS函数,会显得更加简单。


在D2输入公式下拉。

=IF(COUNTIFS(F:F,A2,G:G,B2,H:H,C2),"√","×")


3.返回多个对应值


类型这里多次出现,现在要将所有对应值找出来,而不是查找一个。直接用VLOOKUP函数查找,只能得到一个,希望达到的效果如动画。


如果熟练数组公式的使用,一条公式也可以解决。数组公式跟普通公式不一样,输入公式后需要按Ctrl+Shift+Enter三键结束,否则就出错。

=IFERROR(INDEX(F:F,SMALL(IF($E$2:$E$7=$A$2,ROW($2:$7)),ROW(A1))),"")


不会数组也没关系,借助辅助列也可以实现。


在D2输入公式下拉,类型+序号合并在一起,这样就将重复出现的内容变成不重复。

=E2&COUNTIF($E$2:E2,E2)


在A5输入公式下拉和右拉。

=IFERROR(VLOOKUP($A$2&ROW(A1),$D:$H,COLUMN(C1),0),"")


到此,问题就解决了。

作者:卢子,清华畅销书作者;个人公众号:Excel不加班(ID:Excelbujiaban)

飞翎恋雪1楼

2018-09-21 14:37:54

天使琴缘2楼

2018-09-29 22:01:09
谢谢卢子老师