申明:本公众号下文章,均为小熙本人原创,每个字都是自己码,每张图都是亲自制作。请尊重原创,如有方法上的异见,欢迎留言交流!
查找对象有多个对应值,既可以用加辅助列的办法解决,其实也可以用index+small+if的组合来实现,只是较为复杂,那也是高手的方法。
查找最基本的方法就是用vlookup函数,不过此函数对查找有多个对应值的时候,直接写入函数只能查找到符合条件的第一个对应值,而它的亲兄弟lookup函数,则是查找最后一个对应值。这点,请大家一定要记住,也要在实际中多多应用。
下面对符合条件的查找多个对应值的方法总结如下:
1、查找外国作家对应的作品
基于以上表格格式,最好的方法就是用index+small+if的组合来实现,在E2单元格内输入:=INDEX($B:$B,SMALL(IF($A$2:$A$21=$D2,ROW($A$2:$A$21),4^8),COLUMN(A1)))&''。这里因为要右拉填充与下拉填充,所B:B也要锁定,$D2要锁定列,否则右拉填充复制条件会改变。COLUMN(A1)是提取列号,右拉填充时会依次从小到大的提取列号。
E2输入公式,右拉填充,下拉填充,就会得出所有作家的作品。
此方法在理解写入公式时较复杂,也容易出错,但是在这个表格模式下,写好第一个公式后,其它的数据都会填充得到。
2,用vlookup?lookup?
查找“高尔基”的作品,用vlookup函数查找,只能查找符合“高尔基”的第一个作品“小市民”。
用lookup函数,只能查找到符合条件“高尔基”的最后一个作品“母亲”:
所以这两个函数的这个特点,我们一定要牢记,牢记,牢记。
如果要查找到这列最后一个数据,则lookup是最好的选择:
输入公式:=LOOKUP(1,0/(C:C''),C:C)。
C:C'':是C列不为空值。写入时记得加()。
3、辅助列的简易性
如图,在作家栏新建一辅助列,在A2输入公式:B2&COUNTIF($B$2:B2,B2),回车后下拉填充,得到如下图示:
在右边查找列F2输入公式:=IFERROR(VLOOKUP($E2&COLUMN(A1),$A$2:$C$21,3,0),'')
IFERROR是个容错函数,此处如果错误值时显示为空。$E2&COLUMN(A1)是作家名字加上数字,COLUMN(A1)提取列号。这里E2必须锁定列,这样右拉时才能锁定在E2,相信这个公式不难理解。
然后右拉填充,再下拉填充,就可得到所有作家的作品。
总结:
1、高手常用查找多个对应值的方法:index+small+if+row(或者COLUMN)的组合。
2、用vlookup查找符合条件的多个对应值查找到的是第一个值 。用lookup函数查找符合条件的多个对应值查找到的是最后一个值。
3、用辅助列进行多个对应值查找是最省脑力的办法。
明天见!!!
联系客服