今仮に、Sheet4のA2~A9の範囲にグループ番号が入力されていて、Sheet5のA列を作業列として使用して、Sheet1のH3セルに入力されているグループ番号に該当する「Sheet4のI列の値」を、Sheet1のI3セルに表示させるものとします。
まず、Sheet5のA2セルに次の関数を入力して下さい。
=IF(INDEX(Sheet4!$A:$A,ROW())="","",IF(COUNTIF(Sheet4!$A$1:INDEX(Sheet4!$A:$A,ROW()),INDEX(Sheet4!$A:$A,ROW()))=COUNTIF(Sheet4!$A:$A,INDEX(Sheet4!$A:$A,ROW())),INDEX(Sheet4!$A:$A,ROW()),""))
次に、Sheet5のA2セルをコピーして、Sheet5のA3~A9のセル範囲に貼り付けて下さい。
その上で、Sheet1のI3セルには次の関数を入力して下さい。
=IF(AND($H3<>"",COUNTIF(Sheet5!$A:$A,$H3)),INDEX(Sheet4!$I:$I,MATCH($H3,Sheet5!$A:$A,0)),"")
尚、もしも作業列を使用したくない場合には、Sheet5は使用せずに、Sheet1のI3セルに入力する関数を次の様にして下さい。
=IF(AND($H3<>"",COUNTIF(Sheet5!$A:$A,$H3)),INDEX(Sheet4!$I:$I,SUMPRODUCT(MAX((Sheet4!$A$2:$A$9=$H3)*ROW(Sheet4!$A$2:$A$9)))),"")
又、もしSheet4の最終行が何行目になるのか不明な場合には、Sheet1のI3セルに入力する関数を次の様な関数として下さい。(但し、Excel2007以前のバージョンのExcelではこの関数を使えません)
=IF(AND($H3<>"",COUNTIF(Sheet5!$A:$A,$H3)),INDEX(Sheet4!$I:$I,SUMPRODUCT(MAX((Sheet4!$A:$A=$H3)*ROW(Sheet4!$A:$A)))),"")
又、もしSheet4のA列に入力されているグループ番号が必ず数値データとなっている事が保障されている場合には、Sheet1のI3セルに入力する関数を次の様な関数とする事も出来ます。
=IF(AND($H3<>"",COUNTIF(Sheet5!$A:$A,$H3)),INDEX(Sheet4!$I:$I,SUMPRODUCT(MAX((Sheet4!$A$1:INDEX(Sheet4!$A:$A,MATCH(9E+307,Sheet4!$A:$A))=$H3)*ROW(Sheet4!$A$1:INDEX(Sheet4!$A:$A,MATCH(9E+307,Sheet4!$A:$A)))))),"")
但し、SUMPRODUCT関数を使った方法や、(今回は提示しておりませんが)配列式を使用した方法では、元データの行数が多くなれば多くなる程処理が重くなりますので、元データの行数が万単位になる様な場合には作業列を用いる方法を使った方が実用的です。
お礼
ありがとうございました。