複数のセルに合致するデータを全て表示する

質問

表１で１ヶ月分、日にちごとに誰が何を発注しているのかをまとめてあります。
（個人単位で１ヶ月のデータがでるようになっています。）
表２に１日単位で発注品毎に誰が頼んでいるか名前が出るように
したいのですが関数で出来ますか？

表１
　　　Ａ列　　　　　　　Ｂ列　　Ｃ列　　　　Ｄ列 　　　　　　　　　　　注文名
１行　　氏　名 11 12 13
２行 　　　　　　日 日 日
３行　　ああ 　Ａ Ｂ
４行　　かか Ｂ Ａ 　　　　　　
５行　　ささ 　Ｂ Ｃ Ｃ
６行　　たた 　Ａ Ａ Ｂ
７行　　なな 　Ｃ Ｂ
８行　　はは 　Ａ Ａ

表２（１１日の注文）
　　　　注文名　　Ａ　　　　　Ｂ　　　　　Ｃ
　　　　　　　　　　ああ 　ささ　　　　なな
　　　　　　　　　　たた
　　　　注文者　 はは

回答No.2

KURUMITO · ベストアンサー

回答No1です。
初めにシート1のAH2セルには次の式を入力しますね。

=IF(Sheet2!A$1="","",IF(INDEX(A:AF,ROW(),MATCH(Sheet2!A$1,A$1:AF$1,0))=0,"",INDEX(A:AF,ROW(),MATCH(Sheet2!A$1,A$1:AF$1,0))&COUNTIF(INDEX(A:AF,2,MATCH(Sheet2!A$1,A$1:AF$1,0)):INDEX(A:AF,ROW(),MATCH(Sheet2!A$1,A$1:AF$1,0)),INDEX(A:AF,ROW(),MATCH(Sheet2!A$1,A$1:AF$1,0)))))

この式はシート2のA1セルに表示したい日付が入力されていなければ空白にしなさい。もしもシート2の日付と一致する（MATCH関数を使っています）列でその式の入力されている行（ROW()）,AH2のセルに入力された式ですから2行目、のデータが空の場合には空白にしなさい。そうでない場合には、AH2セルに該当する日付の2行目にあるデータ（商品名）とそれにその商品名がその該当の日付の列で上の行から数えて何個目になっているか（COUNTIF関数を使っています）を結合させたデータを表示しなさい。という意味になります。
実際にAH列のデータがそうなっていることを確認してください。商品名と上から数えて何番目になっているかが分かるようになっています。
シート2ではA1セルにシート1の日付にある表示させたい日付を入力します。
B2セルから右横方向にはA,B,Cなどの商品名を入力します。
B3セルには次の式を入力して右横方向にドラッグコピーしたのちに下方にもドラッグコピーします。

=IFERROR(INDEX(Sheet1!$A:$A,MATCH(B$2&ROW(A1),Sheet1!$AH:$AH,0)),"")

この式はシート1のA列に並んだ氏名を表示させるための式ですが、シート2の2行目の商品名と上からの行番号（ROW(A1)の時は行番号は１です。)を結合させたデータがシート1のAH列に一致した行がシート1に有ればその行のA列に記載されている氏名を表示しなさい。もしも該当するデータが無ければ空のセルにしなさい。という意味になります。

お礼

細かく丁寧に説明いただきありがとうございます。
セルの文字などではなく、行、列のマッチングする値の検索なんですね。
注文名の何番でみる。ということですね。
御陰様でなんとか一覧を作成することが出来ました。
本当にありがとうございます。
これからもっと勉強しようと思います。

回答No.1

KURUMITO

例えば表1がシート1に有ってA1セルに氏名の文字がB1セルから右横の列には例えば10月1日から始まってAF1セルで10月31日で終わるカレンダーが入力されているとします。
A2セルから下方にはああ、かか…といった氏名が入力されB列からAF列までの間に商品名のA,B,Cなどが入力されているとします。
データが多くなっても計算速度に負担のかからない方法は作業列を使った簡単な式を用いることです。
AH2セルには次の式を入力して下方にドラッグコピーします。

=IF(Sheet2!A$1="","",IF(INDEX(A:AF,ROW(),MATCH(Sheet2!A$1,A$1:AF$1,0))=0,"",INDEX(A:AF,ROW(),MATCH(Sheet2!A$1,A$1:AF$1,0))&COUNTIF(INDEX(A:AF,2,MATCH(Sheet2!A$1,A$1:AF$1,0)):INDEX(A:AF,ROW(),MATCH(Sheet2!A$1,A$1:AF$1,0)),INDEX(A:AF,ROW(),MATCH(Sheet2!A$1,A$1:AF$1,0)))))

シート2のA1セルにはシート1の1行目にある日付と一致する日付が入力されていることが必要です。
シート2に移ってシート2のA1セルには10月1日などの日付を入力します。
B1セルには注文などの文字を入力します。
B2セルから右横方向の列にはA,B,C・・・といった商品名を入力します。
B3セルには次の式を入力して右横方向にドラッグコピーしたのちに下方にもドラッグコピーします。

=IFERROR(INDEX(Sheet1!$A:$A,MATCH(B$2&ROW(A1),Sheet1!$AH:$AH,0)),"")

エクセルのバージョンが2007よりも前のバージョンでしたら次の式になります。

=IF(ISERROR(INDEX(Sheet1!$A:$A,MATCH(B$2&ROW(A1),Sheet1!$AH:$AH,0))),"",INDEX(Sheet1!$A:$A,MATCH(B$2&ROW(A1),Sheet1!$AH:$AH,0)))

お礼

早速の回答ありがとうございました。
上記（作業列）の関数についてですが、エクセルにはあまり詳しくなく
この式内でのやりとりが分かりません。まず「Sheet2」の日付をみてその日付と同じ日の
「Sheet1」の注文状況を見ているのでしょうか？また、「Sheet2」では注文した品名ごとに
注文者が表示されるようにしたいのですが、セルに入れる関数には商品名等が入っていないですよね？「Sheet1」内での注文内容をどのようにして見て、注文品Aを頼んだ人だと、「Sheet2」に表示されるのかが分らないのです。
全くの素人で申し訳ございませんが、補足説明いただけると大変助かります。