编按:在上下并排的多组横排表格中查找数据,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精品好课任你选择! 相关推荐: 版权申明: 本文作者小窝;部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。 |
|
来自: 部落窝教育BLW > 《部落窝excel/VBA》