国棋 发表于 2021-10-19 09:39:00

大神,帮忙改个公式

在汇总表里查找6个工作表对应的高和宽,详见附件

吉人天佑 发表于 2021-11-16 23:16:06

C2=VLOOKUP($B2,INDIRECT("'"&LOOKUP(1,0/COUNTIF(INDIRECT("'"&{"1#楼","2#楼","3#楼","4#楼","配建","门岗"}&"'!a:a"),$B2),{"1#楼","2#楼","3#楼","4#楼","配建","门岗"})&"'!a:z"),2+(C$1="高度"),0)

右拉下拉。

敢动中国 发表于 2022-1-21 05:59:45

=LOOKUP(9^9,1/SUMIF(INDIRECT("'"&{"配建","门岗","4#楼","3#楼","1#楼"}&"'!a:a"),$B2,INDIRECT("'"&{"配建","门岗","4#楼","3#楼","1#楼"}&MID("'!b:b'!c:c",COLUMN(A1)*5-4,5)))^-1)

右拉下拉

这颗心稀巴烂 发表于 2022-1-21 08:04:55

=VLOOKUP($B2,INDIRECT(INDEX("'"&{"1#楼";"3#楼";"4#楼";"门岗";"配建"}&"'!A:E",MATCH(,0/COUNTIF(INDIRECT("'"&{"1#楼";"3#楼";"4#楼";"门岗";"配建"}&"'!A:E"),$B2),))),COLUMN(B1),)
页: [1]
查看完整版本: 大神,帮忙改个公式