VLOOKUP函数查找,一般情况下,一次只能查找一个值,但是有时候我们要查找的条件是相同的,但相同的条件对应的值是不相同的,现在我们想通过VLOOKUP函数批量将相同条件下的值查找出来,函数该怎么写?
很多人完全没有思路,毕竟多条件查找,反向查找这些都还没学会,现在又来一个批量查找,完全吃不消啊!但如果你看了我的教程,相信再长的公式,你都可以理解的明明白白!不信往下看看!
例子:下图是一个销售表,现在我们要查找姓名为“张三”对应的所有销售额,为了让大家看的更明显,张三所对应的销售额已经用黄色区域标注出来。
具体操作步骤如下:
1、选中G2单元格 -- 在编辑栏中输入公式“=VLOOKUP(F$3&ROW(C1),IF({1,0},$C$3:$C$11&COUNTIF(INDIRECT('C3:C'&ROW($3:$11)),F$3),$D$3:$D$11),2,)”-- 按组合键“Ctrl+Shift+Enter”结束公式 -- 下拉公式至单元格出现错误值“#N/A”,说明已查找到所有的值。
2、动图演示如下。
3、公式解析。
(1)F$3&ROW(C1):
ROW(C1)的意思是返回C1单元格所在的行号1。F$3是绝对行引用,当公式下拉时,F$3还是F$3。所以F$3&ROW(C1)的结果为“张三1”,当公式下拉时,公式F$3&ROW(C1)变成F$3&ROW(C2),F$3&ROW(C3),对应的结果分别为“张三2,张三3,....”以此类推。也就是说该公式的意思是将张三与行号连接。
(2)IF({1,0},$C$3:$C$11&COUNTIF(INDIRECT('C3:C'&ROW($3:$11)),F$3),$D$3:$D$11):
INDIRECT('C3:C'&ROW($3:$11)):
ROW($3:$11)返回一组行号{3;4;5;6;7;8;9;10;11}。'C3:C'&ROW($3:$11)返回一组单元格区域{'C3:C3';'C3:C4';'C3:C5';'C3:C6';'C3:C7';'C3:C8';'C3:C9';'C3:C10';'C3:C11'}。 INDIRECT函数的作用是返回由文本字符串指定的引用。所以公式INDIRECT('C3:C'&ROW($3:$11))得到的结果是{'张三';'张三';'张三';'张三';'张三';'张三';'张三';'张三';'张三'}。
COUNTIF(INDIRECT('C3:C'&ROW($3:$11)),F$3):
使用COUNTIF函数对单元格区域C3:C11内与F3值相同的数值进行计数,得到结果为:{1;1;1;1;2;2;2;2;3}。
$C$3:$C$11&COUNTIF(INDIRECT('C3:C'&ROW($3:$11)),F$3):
将C3与C11单元格区域的内容和使用COUNTIF函数计数的结果连接,得到一个新的区域{'张三1';'李四1';'王五1';'赵六1';'张三2';'甲2';'乙2';'丙2';'张三3'}。
{1,0}:
{1,0}相当于{TRUE,FALSE}。所以该公式就有两种情况:第一种情况:IF(1,$C$3:$C$11&COUNTIF(INDIRECT('C3:C'&ROW($3:$11)),F$3),$D$3:$D$11),这种情况返回第2个参数的结果{'张三1';'李四1';'王五1';'赵六1';'张三2';'甲2';'乙2';'丙2';'张三3'}。第二种情况:IF(0,$C$3:$C$11&COUNTIF(INDIRECT('C3:C'&ROW($3:$11)),F$3),$D$3:$D$11),这种情况返回D3:D11单元格区域内容。所以{1,0}相当于重新构建了两列数据,如下图所示。
(3)=VLOOKUP(F$3&ROW(C1),IF({1,0},$C$3:$C$11&COUNTIF(INDIRECT('C3:C'&ROW($3:$11)),F$3),$D$3:$D$11),2,):
公式F$3&ROW(C1)返回的结果,也就是查找值,根据查找值找到与之对应的所有销售额,在IF构建的新的查找区域中,属于第2列,所以第3个参数为2,第4个参数默认为0或者FALSE,表示精确查找。
以上就是VLOOKUP函数批量查找的一种方法,公式虽然比较长,但也作了详细的解析,如有不懂之处,可在评论区留言。转发收藏起来,上班花几分钟学学,效果会很不错哦~
您的每一份赞赏、转发、评论、点赞、收藏都将成为我们写出更多优质教程的动力!感激不尽!
联系客服