二维码 购物车
部落窝在线教育欢迎您!

VLOOKUP和XLOOKUP都无法的多区域查找,试用R1C1地址提取

 

作者:小窝来源:部落窝教育发布时间:2023-07-31 09:24:52点击:1072

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。
编按:

在上下并排的多组横排表格中查找数据,VLOOKUPXLOOKUP都办不到。这个时候尝试用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;4001200}

② 获得列数:

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

③ 两者相加得到一组116列的包含了行数和列数的数{201,202,203,204,205,206;3011206}

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

⑤ 将③和④中相乘,除开{201,202,203,204,205,206;3011206}中第76列的806外,其他都变成了0

⑥ 用MAX0806中的最大值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),"")

 

 

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

 

 

 

本文配套的练习课件请加入QQ群:902294808下载。

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

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

用R1C1样式地址跨表求和

跨表查找

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

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

版权申明:

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