INDEX,MATCH使用方法

質問

Sheet1
　　　　　　　　　　A　　　　　　　B　　　　　C　　　　　　　　　　D　　　　　　　　　　E
1　　　　得意先コード入力　　　　　納品先候補：　　　納品先コード　　　　納品先名
2　　　　1001　　　　　　　　　　　　　　　　　　　　　　　　　1　　　　　　　　　　　あああ
3　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　2　　　　　　　　　　　いいい
4　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　3　　　　　　　　　　　ううう
5　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　4　　　　　　　　　　　えええ
6　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　5　　　　　　　　　　　おおお
7　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　1　　　　　　　　　　　かかか

Sheet2
　　　　A　　　　　　　　　　B　　　　　　　　　　C
1　　　得意先コード　　　納品先コード　　　納品先名
2　　　1001　　　　　　　　1　　　　　　　　　　あああ
3　　　1001　　　　　　　　2　　　　　　　　　　いいい
4　　　1001　　　　　　　　3　　　　　　　　　　ううう
5　　　1001　　　　　　　　4　　　　　　　　　　えええ
6　　　1001　　　　　　　　5　　　　　　　　　　おおお
7　　　1002　　　　　　　　1　　　　　　　　　　かかか
8　　　1002　　　　　　　　2　　　　　　　　　　ききき
9　　　1002　　　　　　　　3　　　　　　　　　　くくく
10　　1002　　　　　　　　4　　　　　　　　　　けけけ

お世話になります。

Sheet1で得意先コードをA2に入力すれば、Sheet2から納品先候補を検索して表示するようにしたいのですが、現状ではSheet1のD2以下に下記のような関数を入れています。
D2：=INDEX(Sheet2!$B$2:$B$10,MATCH(Sheet1!$A$2,Sheet2!$A$2:$A$10,0))
D3：=INDEX(Sheet2!$B$2:$B$10,(MATCH(Sheet1!$A$2,Sheet2!$A$2:$A$10,0)+1))
以下同様、E列も同じです。

これでも表示はされるのですが、ドラッグコピーが出来ない(+1,2…)ので手入力になるのとエラーや0を非表示にする為にさらにISERRORを組み込む事を考えますとかなりしんどい事になりそうですので、もっと良いやり方はないでしょうか。
さらに、入力した得意先コードの分のみの納品先候補表示が出来ればありがたいです。

宜しくお願い致します。

回答No.2

MackyNo1 · ベストアンサー

現在の数式をそのまま利用するなら以下の式を入力して右方向に1つ、下方向に適当数オートフィルコピーしてください。

=IF(COUNTIF(Sheet2!$A$2:$A$10,$A$2)<ROW(A1),"",INDEX(Sheet2!B$2:B$10,MATCH($A$2,Sheet2!$A$2:$A$10,0)+ROW(A1)-1))

お礼

ご回答ありがとうございました。
無事コピーして表示する事が出来ました。

回答No.1

bunjii

>さらに、入力した得意先コードの分のみの納品先候補表示が出来ればありがたいです。
Excel 2007以降であれば次の式で良いでしょう。
IFERROR関数がExcel 2003以前のバージョンにはありませんので、代わりの処理をするには数式が長すぎます。
Excel 2003はサポートが4月で終了になりますのでアドバイスの対象外とさせて頂きます。

Sheet1!C2=IFERROR(IF(INDEX(Sheet2!$A$2:$A$10,(MATCH($A$2,Sheet2!$A$2:$A$10,0)+ROWS(D$2:D2)-1))=$A$2,INDEX(Sheet2!B$2:B$10,(MATCH($A$2,Sheet2!$A$2:$A$10,0)+ROWS(C$2:C2)-1)),""),"")

C2セルをD2へコピーし、そのまま下へ必要数コピーしてください。
式中の最大行は必要に応じて訂正してください。

貼付画像はExcel 2013で検証した結果です。

お礼

ご回答ありがとうございました。
無事コピーして表示する事が出来ました。