G2:N7{=IFERROR(IF(MOD(COLUMN(A1),3),OFFSET($B$1,SMALL(IF(OFFSET($B$2,,MATCH(LOOKUP("座",$H$1:H$1),$C$1:$E$1,),8)>0,ROW($1:$8)),ROW(A1)),MATCH(LOOKUP("座",$H$1:H$1),$C$1:$E$1,)*(MOD(COLUMN(A1),3)>1)),""),"")
A B C 需同順序{=IFERROR(IF(MOD(COLUMN(A1),3),OFFSET($B$1,SMALL(IF(OFFSET($C$2,,MAX(COLUMN(A1)/3),8)>0,ROW($1:$8)),ROW(A1)),(COLUMN(C1)/3)*(MOD(COLUMN(A1),3)>1)),""),"")
另解{=IFERROR(IF(MOD(COLUMN(A1),3),INDIRECT(TEXT(SMALL(IF(($C$2:$E$9>0)*($C$1:$E$1=LOOKUP("座",$H$1:H$1)),ROW($2:$9)/1%+IF(MOD(COLUMN(A1),3)=1,2,{3,4,5})),ROW(A1)),"!R0C00"),),""),"")
留言
張貼留言