也提供一個不是INDIRECT的思路
公式稍長
=OFFSET($A$4,IF(ISNA(MATCH(SMALL(IF(($A$5:$A$11=$A$4:$A$10)*(COLUMN($A:$B)=1),"",COLUMN($A:$B)+(ROW($1:$7)-1)*2),ROW(A1)),IF(($A$5:$A$11=$A$4:$A$10),"",1+(ROW($1:$7)-1)*2),)),MATCH(SMALL(IF(($A$5:$A$11=$A$4:$A$10)*(COLUMN($A:$B)=1),"",COLUMN($A:$B)+(ROW($1:$7)-1)*2),ROW(A1)),2+(ROW($1:$7)-1)*2,),MATCH(SMALL(IF(($A$5:$A$11=$A$4:$A$10)*(COLUMN($A:$B)=1),"",COLUMN($A:$B)+(ROW($1:$7)-1)*2),ROW(A1)),IF(($A$5:$A$11=$A$4:$A$10),"",1+(ROW($1:$7)-1)*2),)),IF(ISNA(MATCH(SMALL(IF(($A$5:$A$11=$A$4:$A$10)*(COLUMN($A:$B)=1),"",COLUMN($A:$B)+(ROW($1:$7)-1)*2),ROW(A1)),IF(($A$5:$A$11=$A$4:$A$10),"",1+(ROW($1:$7)-1)*2),)),1,0))
數組 |