エクセルで２つの条件を満たした列をHLOOKUPで

質問

エクセルで、１行目と２行目の条件を満たした列をHLOOKUPで参照したいです。

人件費の表をつくっています。
給与明細の名前の欄をプルダウンで変更可能にし、名前を変えればその人の出勤時間が表示されるようにしたいです。

時間を記入するシート１は下のようになってます

Ａ　　　　　Ｂ　　　　　Ｃ　　　　　Ｄ
山田　　　山田　　　鈴木　　　鈴木
出勤　　　退勤　　　出勤　　　退勤
１４　　　　２１　　　　１８　　　　２３
１６　　　　２１　　　　１８　　　　２２
１５　　　　２３　　　　１７　　　　２３
１６　　　　２２　　　　１７　　　　２１

１行目が氏名
２行目が出勤か退勤か
３行目以降が時間記入欄です。

別シート2に給与明細がありまして

Ａ　　　　　Ｂ　　　　　Ｃ
氏名　　　山田
出退　　　出勤　　　退勤
１日　　　１４　　　　２１
２日　　　１６　　　　２１
３日　　　１５　　　　２３

のように参照されています

氏名の「山田」の部分がプルダウンリストで選べるようになっていて
この氏名の部分を「山田」から「鈴木」に変更すると自動的に出勤と退勤の時間が「鈴木」のものへ変更になるようにしたいです。

やりたいことは以下の事です

シート２の氏名が「山田」であれば例のシート２のまま

シート２の氏名の部分を「山田」から「鈴木」へ変更すれば
Ｂ４、Ｂ５、Ｂ６セルは１８、１８、１７に変わり
Ｃ４、Ｃ５、Ｃ６セルは２３、２２，２３に変化するようにしたいです。

関連しそうなIF,AND,MATCH,HLOOKUP等組み合わせてみましたが、どうもうまくいきません。

わかりづらいと思いますが、是非教えて頂ければ幸いですので宜しくお願いします。

回答No.2

KURUMITO

シート１にお示しのデータがあって、１行目には氏名が２行目には項目名が、３行目以降にはデータがあるとします。
シート２ではＢ１セルに氏名が入力されるとして、２行目にはＡ２セルに出退、Ｂ２セルに出勤、Ｃ２セルに退勤の文字がそれぞれ入っているとします。
Ａ３セルには次の式を入力して下方にオートフィルドラッグします。

=IF(B3="","",COUNT(B$3:B3)&"日")

Ｂ３セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF($B$1="","",IF(OR(COLUMN(A1)>2,INDEX(Sheet1!$A:$XX,ROW(A3),MATCH($B$1,Sheet1!$1:$1,0)+COLUMN(A1)-1)=0),"",INDEX(Sheet1!$A:$XX,ROW(A3),MATCH($B$1,Sheet1!$1:$1,0)+COLUMN(A1)-1)))

回答No.1

kagakusuki

御質問文中にあるシート1には、日付けが記されていませんから、このままでは検索する事が不可能です。
　ですから、シート1のデザインを、次の様に変更して下さい。

　　 　A列 　B列 　C列 　D列 　E列
1行目　　　　山田　　　　鈴木
2行目　日付　出勤　退勤　出勤　退勤
3行目　1日　　14　　21　　18　　23
4行目　2日　　16　　21　　18　　22
5行目　3日　　15　　23　　17　　23
6行目　4日　　16　　22　　17　　21

　そして、Sheet2のB3セルに次の数式を入力して下さい。

=IF(OR($B$1="",$A3="",COUNTIF(Sheet1!$1:$1,$B$1)=0,COUNTIF(Sheet1!$A:$A,$A3)=0),"",OFFSET(Sheet1!$A$2,MATCH($A3,Sheet1!$A$3:$A$33),MATCH($B$1,Sheet1!$1:$1)+COLUMNS（$B:B)-1))

　次に、Sheet2のB3セルをコピーして、Sheet2のB3～C33の範囲に貼り付けて下さい。

　後は、Sheet2のB1セルに氏名を入力すると、自動的に時間が表示されます。

　尚、OFFSET関数の代わりにINDEX関数を使う方法もあります。

=IF(OR($B$1="",$A3="",COUNTIF(Sheet1!$1:$1,$B$1)=0,COUNTIF(Sheet1!$A:$A,$A3)=0),"",INDEX(Sheet1!$3:$33,MATCH($A3,Sheet1!$A$3:$A$33),MATCH($B$1,Sheet1!$1:$1)+COLUMNS（$B:B)-1))

　それから、Sheet1のA列に並んでいる日付の範囲とSheet2のA列に並んでいる日付の範囲が、必ず同じである場合には、次の様な数式にする事も出来ます。

=IF(OR($B$1="",$A3="",COUNTIF(Sheet1!$1:$1,$B$1)=0),"",OFFSET(Sheet1!$A$2,ROWS($3:3),MATCH($B$1,Sheet1!$1:$1)+COLUMNS（$B:B)-1))

=IF(OR($B$1="",$A3="",COUNTIF(Sheet1!$1:$1,$B$1)=0),"",INDEX(Sheet1!$3:$33,ROWS($3:3),MATCH($B$1,Sheet1!$1:$1)+COLUMNS（$B:B)-1))