VLOOKUPで抽出したデータのプルダウンリスト

質問

初めて質問いたします。
VLOOKUP関数の複数データの抽出方法をいくつか調べたのですが、
自分の条件に合うものが見当らなかったのでよろしくお願いします。

現在、来客対応をスムーズにするために今エクセルの表を使っています。
担当の名字を入れると、名前、内線番号、部署、役職などが表示されるように
VLOOKUP関数を使っているのですが、名字に重複データがあると、
一番上のデータのみが表示されます。

複数データがある場合、そのデータをすべて表示したいのですが、
いくつか条件があります。

・抽出元のデータはいじれない。
　　　（役職や新人の入社などでデータが日々変わるので、他部署の作成した表を同期させてデータを抽出しています。）

・抽出したデータが複数の場合、プルダウンリストにして選択したい。
　　　（現在時間ごとに分けた形で表を作っているので、重複データが縦に並ぶと下の表が壊れてしまう。担当が複数の場合もあるので、横にも並べられない。）

・出来ることなら、プルダウンリストで、名前、内線番号、部署、役職のどれかを選択した時点で、該当するデータをすべて表示したい。

・少し面倒ですが、最悪重複データがあることだけでも分かれば、抽出元のデータで確認できます。

回答No.2

KURUMITO · ベストアンサー

回答Ｎｏ1です。
どうなのか良くわかりませんが抽出元のシートをシート2としてＡ1セルからＤ1セルにかけて名前、内線番号、部署、役職の文字がそれぞれ入力されており下方にそれぞれのデータが入力されているとします。
作業列としてＥ列には名字のみを入力します。Ｆ2セルには次の式を入力して下方にドラッグコピーします。

=IF(E2="","",IF(COUNTIF(E$2:E2,E2)=1,ROUNDUP(MAX(F$1:F1),-2)+101,INDEX(F$1:F1,MATCH(E2,E$1:E1,0))+COUNTIF(E$1:E1,E2)))

そこでプルダウンを作るシートはシート1としてＡ1セルには名字の文字が下方に名字を入力するとします。
Ｂ列はプルダウンとしますがそれに先立って作業列を用意します。
例えばＪ2セルには次の式を入力してＸ2セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF($A2="","",IFERROR(INDEX(Sheet2!$A:$A,MATCH(INDEX(Sheet2!$F:$F,MATCH($A2,Sheet2!$E:$E,0))+COLUMN(A1)-1,Sheet2!$F:$F,0))&INDEX(Sheet2!$B:$B,MATCH(INDEX(Sheet2!$F:$F,MATCH($A2,Sheet2!$E:$E,0))+COLUMN(A1)-1,Sheet2!$F:$F,0))&INDEX(Sheet2!$C:$C,MATCH(INDEX(Sheet2!$F:$F,MATCH($A2,Sheet2!$E:$E,0))+COLUMN(A1)-1,Sheet2!$F:$F,0))&INDEX(Sheet2!$D:$D,MATCH(INDEX(Sheet2!$F:$F,MATCH($A2,Sheet2!$E:$E,0))+COLUMN(A1)-1,Sheet2!$F:$F,0)),""))

その後にＢ2セルから下方の行を範囲として選択し、「データ」タブの「データの入力規則」から入力値の種類で「リスト」を選択し元の値の窓には次の式を入力してＯＫします。

=$I2:$X2

Ｊ2セルからの範囲とした場合にはリストの表示が必ずしも先頭から並ぶことになりません。
これでＢ列のリストには氏名から役職までが一気に表示されますので、その中から選択すればよいでしょう。Ｂ列の幅を出来るだけ大きくすればよいでしょう。

お礼

ありがとうございます。

動きました。
まだまだ覚えたてで、数式の意味はまだ理解できませんが、
勉強して自分でも式を作れるようにがんばります。

本当に助かりました。
ありがとうございます。

回答No.1

KURUMITO

抽出元のデータシートの離れた列に作業列を作ることもできない、プルダウンを作るシートにも作業列を作ることもできないでは方法を考えることもできません。マクロを使って何とか処理できるとしてもダブル可能性のある姓がどの程度の数になるのかによっても対応は変わりますね。
担当の名字が入力される行数はどの程度になるのでしょう。
最悪重複データが有ることが分かればということでしたら、例えばシート1のA列に名字を入力するとしてB列にはVLOOKUP関数で氏名をシート2のA列から表示させるとした場合では次のようにすればよいでしょう。
B列を選択してから「ホーム」タブの「条件付き書式」の「新しいルール」を選択します。
表示の画面で「数式を使用して…」を選択したのちに数式の窓には次の式を入力します。

=AND(A1<>"",COUNTIF(Sheet2!A:A,"*"&A1&"*")>1)

その後に「書式」から「塗りつぶし」のタブで黄色などを設定してOKします。
そうすることで入力した名字の名前がシート2のA列に複数有る場合にはB列のセルに黄色が表示されます。

補足

初心者ですみません。
的を射てない補足かもしれませんが、
よろしくおねがいします。

＞抽出元のデータシートの離れた列に作業列を作ることもできない、プルダウンを作るシートにも作業列を作ることもできないでは方法を考えることもできません。

データシートの作業列に関しては、他部署から同期している
シートから、必要な箇所を別のシートにコピーすることで解決しました。
プルダウンを作るシートの作業列に関しては、入力用シートと出力用シートを分け、ことで解決できますでしょうか？

＞マクロを使って何とか処理できるとしてもダブル可能性のある姓がどの程度の数になるのかによっても対応は変わりますね。

重複している姓は現在最大で10名おります。増えることも考えて15名ほど対応できればと思います。

＞最悪重複データが有ることが分かればということでしたら、例えばシート1のA列に名字を入力するとしてB列にはVLOOKUP関数で氏名をシート2のA列から表示させるとした場合では次のようにすればよいでしょう。

ありがとうございます。現在この方法で対応しておりますが、
上記の方法で可能でしたらよろしくお願いいたします。

ありがとうございます。