真伤脑！VLOOKUP、LOOKUP、INDEX+MATCH都解决不了的多行多列查找难题

1月9日

周四

跟着卢子一起成长。...

恭喜下面5位读者获得书籍：淡语嫣然、李婷、睡梦、君子不器、Sunny，加卢子微信chenxilu2019，发送姓名电话地址。

文末点亮在看+留言，随机抽取5名赠送书籍Excel透视表跟卢子一起学 早做完，不加班。

最近一段时间，发现好几个学员这样，做表格，将内容记录成多行多列。或许是为了方便打印，不过这样后期不利于查找，和统计。

这种布局的查找，可以分成2种情况，根据文本查找数值，会比较简单，根据数值查找文本会(文本会，)非常难。

1.根据名称查找编号



可能有的人会用多个VLOOKUP和IFERROR函数嵌套从而找到对应值。不过列数很多，会比较繁琐，而且容易出错。

对于文本查找数值，其实还有一种很简单，的方法。sumif函数除了可以对，一列内容进行求和，同时也能对多列，内容进行求和。这里的编号是数值格式，可以进行求和，因为是唯一值，也等同于查找。

在使用区域的时候，条件区域选择A:g也就是从第1个，名称到最后1个名称的区域，求和区域选择B:h也就是从第1个，编号到最后1个名称的编号，千万别选错。

=SUMIF(A:G,J2,B:H)

不过在使用sumif函数的，时候也有弊端，只能查找数值不能查找，文本。

还有，即使是数值，在下面这种情况，同样会出错。

=SUMIF(A:A,J2,B:H)

每个函数都有本身的，运用场景，并非万能，这点要记住。

=SUMPRODUCT(($A$2:$A$6=J2)*$B$2:$H$6)

2.根据编号查找名称



查找文本，所有求和函数都用不上，文本求和只能得到0。

常用的VLOOKUP、LOOKUP、INDEX+match函数等也派不，上用场，这些都只能针对一列，内容查找。

常规方法都派不上用场，只能用其他特别的方法。

判断编号在第几行和，第几列，确定行列号以后，就可以嵌套，index函数解决。

INDEX函数语法:

=INDEX(区域,第几行,第几列)

比如卢子在第2行，第7列。

=INDEX(A:H,2,7)

编号在第几行，数组公式，需要按Ctrl+Shift+Enter结束。

=MAX(IF(J2=$A$2:$H$14,ROW($2:$14)))

编号在第几列，数组公式，需要按Ctrl+Shift+Enter结束。

=MAX(IF(J2=$A$2:$H$14,COLUMN(A:H)))

ROW函数是获取行号，column函数是，获取列号。if函数就是让满足条件，的值返回行、列号，其他的都返回逻辑值，max函数获取最大的，数字，也就是行、列号。

这样就可以获得编号，结果跟原来一样，证明公式没问题。

=INDEX(A:H,MAX(IF(J2=$A$2:$H$14,ROW($2:$14))),MAX(IF(J2=$A$2:$H$14,COLUMN(A:H))))

编号前面1列就是名称，只需用COLUMN-1，就可以解决。记得按Ctrl+Shift+Enter结束。

=INDEX(A:H,MAX(IF(J2=$A$2:$H$14,ROW($2:$14))),MAX(IF(J2=$A$2:$H$14,COLUMN(A:H)-1)))

最后，尽量不要使用这种布局，名称、编号各一列就好，这样后期会方便很多。

源文件：

https://pan.baidu.com/s/172mUjMlZPrXj942X0XD2OA

VIP 888 元，一次报名，所有课程，终生免费学，提供一年在线答疑服务。报名后加卢子微信chenxilu2019，发送报名截图邀请进群。

推荐：不可思议，MAX+IF组合居然比VLOOKUP查找更牛逼

上篇：2020年，我连续输入错了7次，你出错了多少次？



作者：卢子，清华畅销书作者，《Excel效率手册 早做完，不加班》系列丛书创始人，个人公众号：Excel不加班（ID：Excelbujiaban）无需打赏，请点在看↓↓↓

    关注 Excel不加班