VLOOKUP和XLOOKUP都无法的多区域查找,试用R1C1地址提取
作者:小窝来源:部落窝教育发布时间:2023-07-31 09:24:52点击:1072
在上下并排的多组横排表格中查找数据,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),"")
一对多查同一姓名的多个返回值:
本文配套的练习课件请加入QQ群:902294808下载。
做Excel高手,快速提升工作效率,部落窝教育Excel精品好课任你选择!
扫下方二维码关注公众号,可随时随地学习Excel:
相关推荐:
版权申明:
本文作者小窝;部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。