VLOOKUP函数的教程已经分享六篇了,今天算是这个函数的最后一篇基础教程,和大家分享两个问题,一对多查找的思路以及HLOOKUP函数的用法简介。
VLOOKUP一对多查找的思路
当有多条记录与查找值相同时,VLOOKUP只能找到第一个与条件一致的结果,如果要将这些结果都匹配出来,可以使用辅助列实现。
例如要得到指定姓名的所有数据,可以在数据源左边插入一列,使用公式=B2&COUNTIF($B$2:B2,B2)
在姓名后面添加一个序号,再使用公式=VLOOKUP($F$2&ROW(A1),$A:$D,COLUMN(C1),0)
完成结果的匹配,如图所示。
提示:对于一些问题使用辅助列可以降低解决难度,就一对多匹配的问题而言,也有不用辅助列的公式,但是比较难以理解,也可以利用高级筛选或者数据透视表等功能去实现,使用VLOOKUP只是其中的一种方法。
VLOOKUP躺下就是HLOOKUP
VLOOKUP是按照条件(第一参数)在数据区域(第二参数)的指定列(第三参数)进行数据匹配;
HLOOKUP则是按照条件(第一参数)在数据区域(第二参数)的指定行(第三参数)进行数据匹配。
两个函数的用法和特点完全一样,形象一点说VLOOKUP是纵向查找,而
HLOOKUP则是横向查找,以下通过两个示例来说明二者之间的差异。
示例1.
查找某个销售人员所有商品的销售数量时,VLOOKUP和HLOOKUP都可以完成,使用HLOOKUP的公式相对简洁一点,如图所示。
公式=VLOOKUP($G$2,$A$1:$E$5,COLUMN(B1),0)中,条件为姓名(必须在查找区域的首列),第三参数需要使用COLUMN实现连续查找的效果;
公式=HLOOKUP(H1,$A$1:$E$5,3,0)中,条件为商品名称(必须在查找区域的首行),第三参数为姓名(张珂)在数据区域中的行数(3),直接向右复制即可得到每个商品在这一行的销售数量。
示例2.
查找所有销售人员某个商品的销售数量时,VLOOKUP和HLOOKUP都可以完成,使用VLOOKUP的公式相对简洁一点,如图所示。
公式=VLOOKUP(M2,$A$1:$C$5,3,0)使用姓名为查找条件,第三参数直接用3即可得到所需结果;
公式=HLOOKUP($O$1,$A$1:$E$5,ROW(A2),0)使用商品名称(洗衣机)作为查找条件,第三参数需要使用ROW实现连续多行的查找。
提示:比较示例1和示例2可知,两个函数在某些情况下是可以通用的,区别在于查找条件和查找方向发生了转换,相对而言大家更加习惯纵向查看数据,所以VLOOKUP的使用频率远远高于HLOOKUP,有时候换个角度看问题会得到不同的思路,对于灵活运用公式函数解决问题是很有好处的。
本文节选自《菜鸟的Excel函数修炼手册》