Excel多维表格或多区域查找：可以采用R1C1地址提取方法

编按：在上下并排的多组横排表格中查找数据，VLOOKUP和XLOOKUP都办不到。这个时候尝试用R1C1地址提取往往有奇效！实际上这就是多区域查找，在同一个工作表中的多个区域中同时进行查找，类似跨表查找。

这是上下连排的多组并列数据，现在需要按姓名如“孙倩”“林菲”查成绩。

很显然，VLOOKUPLOOKUPXLOOUP都无法完成，除非把表格整理称标准的横排或者竖排一维表，如下。

这个时候，一个小配角，INDIRECT跳了出来唱大戏——他真的可以简单搞定这类查找。

直接上公式：

=INDIRECT(TEXT(MAX((A1:F11=H2)*(ROW(A2:F12)/1%+COLUMN(A:F))),"R0C00"),0)

公式解析：

获得行数：

ROW(A2:F12)，得到一组行数{2;3;4;5;6;7;8;9;10;11;12}数组。 再除以1%，将每个行数值放大100倍，变成{200;300;400…1200}

获得列数：

COLUMN(A:F)，得到一组列数{1,2,3,4,5,6}

两者相加得到一组11行6列的包含了行数和列数的数{201,202,203,204,205,206;301…1206}

 A1:F11=H2判断各数据是否等于孙倩，得到一组11行6列的数；很明显只有第7行的第6列是1，其他都是0。

将和中相乘，除开{201,202,203,204,205,206;301…1206}中第7行6列的806外，其他都变成了0。

用MAX取0和806中的最大值806。

用TEXT函数将806变成字符串R8C06，该字符串正好是我们曾讲过的R1C1地址样式。

最后用INDIRECT引用“R8C06”地址数据即可。

实际上它们就是多区域查找，只是没行列间隔。

下方各区域间有行或列间隔，甚至有错位，同样可行：

扩展应用：

一对多查多个相同姓名的所有成绩：

如果名单中有相同的姓名，则公式修改一下，即可实现一对多查询，把相同姓名的成绩都查出来。

=IFERROR(INDIRECT(TEXT(LARGE(($A$1:$F$11=$H$2)*(ROW($A$2:$F$12)/1%+COLUMN(A:F)),ROW(A1)),"R0C00"),0),"")

一对多查同一姓名的多个返回值：

做Excel高手，快速提升工作效率，部落窝教育Excel精品好课任你选择！

学习交流请加微信hclhclsc进群领取资料

相关推荐：

用R1C1样式地址跨表求和

跨表查找

怎么创建公式中常用的数列

图表坐标文字太长被斜着排，怎么扶正方便观看？

版权申明：

本文作者小窝；部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。