検索値が1つに対して複数ヒットする関数

質問

月次請求書をエクセルで作成します。（Sheet1)
受注情報は別シート（Sheet2)に入力しています。

Sheet2の入力内容

A1　受注日
B1　顧客番号
C1　顧客名
D1　商品名

月締めですから、X顧客は１点のみ購入、Y顧客は３点購入など、バラバラです。
顧客番号をキーにして、ＶＬＯＯＫＵＰのように請求書に反映させる関数はありますか？
＊フィルター以外で

Sheet1のレイアウト

A1　顧客番号　＊複数あっても表示は1つ
B1　顧客名　　　＊複数あっても表示は1つ
C1～　受注日　＊複数ある場合は複数行
D1～　商品名　＊複数ある場合は複数行

回答No.2

kagakusuki

まず、適当な使用していない列（ここでは仮にSheet3のA列とします）を作業列として使用する事にして、その列の1行目のセル（Sheet3!A1セル）に次の関数を入力して下さい。

=IF(INDEX(Sheet2!$A:$A,ROW())="","",INDEX(Sheet2!$A:$A,ROW())&"■"&COUNTIF(Sheet2!$A$1:INDEX(Sheet2!$A:$A,ROW()),INDEX(Sheet2!$A:$A,ROW())))

　次に、Sheet3のA1セルをコピーして、Sheet3のA2以下に貼り付けて下さい。

　次に、Sheet1のB1セルに次の関数を入力して下さい。

=IF(AND($A$1<>"",COUNTIF(Sheet2!$B:$B,$B$1)),VLOOKUP($B$1,Sheet2!$B:$C,2,FALSE),"")

　次に、Sheet1のC1セルに次の関数を入力して下さい。

=IF(OR($A$1="",ROWS($1:1)>COUNTIF(Sheet2!$B:$B,$B$1)),"",IF(INDEX(Sheet2!$A:$A,MATCH($A$1&"■"&ROWS($1:1),Sheet3!$A:$A,0))="","",INDEX(Sheet2!$A:$A,MATCH($A$1&"■"&ROWS($1:1),Sheet3!$A:$A,0))))

　次に、Sheet1のD1セルに次の関数を入力して下さい。

=IF(OR($A$1="",ROWS($1:1)>COUNTIF(Sheet2!$B:$B,$B$1)),"",IF(INDEX(Sheet2!$D:$D,MATCH($A$1&"■"&ROWS($1:1),Sheet3!$A:$A,0))="","",INDEX(Sheet2!$D:$D,MATCH($A$1&"■"&ROWS($1:1),Sheet3!$A:$A,0))))

　次に、Sheet1のC1～D1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。

　以上で準備は完了で、後はSheet1のA1セルに顧客番号を入力しますと、その顧客番号のデータのみが抽出されて表示されます。
　尚、この方法では、Sheet1のコピーシートを作成しますと、そのコピーシートにおいても別の顧客番号のデータを表示させる事が出来ますから、顧客番号ごとに請求書のシートを作成しておくという事も出来ます。

回答No.1

Cupper-2

やり方は人それぞれと思いますので、自分ならこうするというやり方で…。

Sheet1のA1、B1セルは普通に顧客番号を入力したセルや、
その値を使いVLOOKUP関数を使うなどして表示させましょう。
　A1=顧客番号
　B1=VLOOKUP(顧客番号,A:B,2,-1)

問題は複数ある場合です。
ここから自己流になります。
対象になる行にマークして、そのマークのある行のデータを読み取ればいいということです。
Sheet2のE列にCOUNT関数を使い、指定した顧客番号で範囲（Sheet2のA列）に対してその数を数えます。
　E1=COUNTIF(A$1:A1,顧客番号)
これを入力した範囲の最後の行までコピーします。
するとデータがヒットする都度、上から数値が加算されていきます。
あとはINDEX関数で１から最大カウント数までの数値で値を引っ張ってきたら出来上がり。
こんな感じかな。
　C1=INDEX(C:C,MATCH(ROW(A1),E:E,0))
　D1=INDEX(D:D,MATCH(ROW(A1),E:E,0))
MATCH関数は一番初めに見つけたセルを返してくるので「加算された」行を選択することになります。
それぞれ適当な行までコピーしてください。
しかし、これだけでは最大カウント数よりも大きい数字を拾ってこようとするとエラーになりますので、
最大カウント数よりも大きい数字を拾うことの無いようエラートラップをIF関数で作るとよいでしょう。
　C1=IF(MAX(E:E)>ROW(A1),"",INDEX(C:C,MATCH(ROW(A1),E:E,0)))
みたいにね。

なお、検索に使う顧客番号を入力するセルが示されていませんでしたので、
そのセルに範囲名「顧客番号」を設定したとしています。、
また、記述が面倒なのでシート間の参照に Sheet2!A1 のような表現をせず
単に A1 などとしていますので適当に読み替えてください。

あくまでも自己流ですので、もっとスマートなやり方があると思います。
自身でも考えてみてください。