錦子老師

 雨萱：「錦子老師，下方工作表1工作表的表格為每日要KEY IN的資料。

依據上方的日期及姓名將假別及時數自動套用到下方表單內，用過INDEX+MATCH一直無法成功，想請幫忙還有什麼函數可以讓上方的資料自動套用到下方工作表二？謝謝！」

錦子老師：

點取B3儲存格輸入公式：

=IFERROR(OFFSET(工作表1!$C$1,IFERROR(SUMPRODUCT((工作表1!$A$1:$A$8=B$1)*(工作表1!$B$1:$B$8=$A3)*ROW(工作表1!$A$1:$A$8)),"")-1,0,1,1),"")

再將公式複製到C3:AF3儲存格。

【公式說明】

首先我們要判斷工作表1工作表中A2:A8儲存格中與工作表2工作表B1儲存格日期是同一天的資料(工作表1!$A$1:$A$8=B$1)，由於公式會向下複製到列號1須加上$鎖定。

接著判斷工作表1工作表中B2:B8儲存格中與工作表2工作表A3儲存格是同一類型的資料(工作表1!$B$1:$B$8=$A3)，由於公式會向右複製故欄名A須加上$鎖定。

傳回工作表1工作表中A2:A8儲存格的列號ROW(工作表1!$A$1:$A$8)。

最後利用SUMPRODUCT函數將前面三項相乘，由於第1,2項會傳回TRUE(1)與FALSE(0)，所以必須增加第3項的列號，使其可以變為數字並得知資料在第幾列SUMPRODUCT((工作表1!$A$1:$A$8=B$1)*(工作表1!$B$1:$B$8=$A3)*ROW(工作表1!$A$1:$A$8)),"")。

然後透過OFFSET函數，從C1儲存格向下移動前面算出的列數-1(因為一開始在第一列，故須減1)，不移動欄，傳回該儲存格內容(假別)OFFSET(工作表1!$C$1,IFERROR(SUMPRODUCT((工作表1!$A$1:$A$8=B$1)*(工作表1!$B$1:$B$8=$A3)*ROW(工作表1!$A$1:$A$8)),"")-1,0,1,1)。

如果有錯誤訊息表示沒資料，則不填入資料=IFERROR(OFFSET(工作表1!$C$1,IFERROR(SUMPRODUCT((工作表1!$A$1:$A$8=B$1)*(工作表1!$B$1:$B$8=$A3)*ROW(工作表1!$A$1:$A$8)),"")-1,0,1,1),"")。

點取B4儲存格輸入公式(傳回時數)：

=IFERROR(OFFSET(工作表1!$D$1,IFERROR(SUMPRODUCT((工作表1!$A$1:$A$8=B$1)*(工作表1!$B$1:$B$8=$A3)*ROW(工作表1!$A$1:$A$8)),"")-1,0,1,1),"")

再將公式複製到C4:AF4儲存格。

將B3:AF4框選起來，再複製到B5:AF12儲存格。 

部落格相關範例