エクセルのVLOOKUPで1つの検索値に複数の該当が有った場合の処理

お世話に成ります。
エクセル2010のVLOOKUP関数で、例えばA列に日付が入っていて、B列に人の名前が入っていて、日付けで検索した場合にA列に同じ日付が有って、B列に違う名前が有った場合の処理について教えてください。

やりたいのは、上から順に名前が引き当てられる。　＜表示例 １＞
これは当方には難しい気がするので、第2希望としては全ての該当氏名が同じ日付に引き当てられる。
＜表示例 ２＞
この場合、表示させるセルの場所の指定が煩雑になりそうな気がしますが、何とかできないでしょうか？

A列　　　　　　　B列
2017/7/1　　　　Aさん
2017/8/1　　　　Bさん
1017/7/1　　　　Cさん

＜表示例 １＞
検索値　　　　結果　
2017/7/1 　　Aさん
2017/7/1　 　Cさん

もしくは
＜表示例 ２＞
検索値　　　　結果　
2017/7/1　　 Aさん、Cさん

No.5 回答者： tom04 回答日時： 2017/07/19 11:35

こんにちは！

とりあえず「表示例1」の場合です。
元データはSheet1にあり
表示例1の場合はSheet2に表示するとします。

↓の画像のように作業用の列を設けてみてはどうでしょうか？
作業列D2セルに
=IF(AND(COUNTIF(A:A,A2)>1,COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1),ROW(),"")
という数式を入れこれ以上データはない！というくらいまでフィルハンドルで下へコピーしておきます。

Sheet2のA2セルに
=IFERROR(INDEX(Sheet1!A:A,SMALL(Sheet1!$D:$D,ROW(A1))),"")
という数式を入れ、列・行方向にフィル＆コピー！
A列の表示形式を「日付」にすると
画像のような感じになります。

※　表示例2の場合、「結果」は一つのセルに表示させたいのでしょうか？
その場合はやはりVBAで処理する方法になると思います。m(_ _)m

この回答へのお礼

マクロは苦手なので何とか関数で、と思い質問させていただきました。
ネットで調べたり、ここで教えて頂いたり、いろんな関数の組合わせで出来るのですね。
色々試してみて一番自分にあった（分かり易い）関数の組合せを勉強させていただきます。

因みに結果は１つのセルにこだわりはないのですが、マクロは避けたい。
今回は質問（関数）と違ってはいますがＮｏ２さんの方法で一発解決ですのでＮo２さんをBSにさせて頂きました。
当方がBS選択でまごついている間のご回答でしたのでご了承ください。

お礼日時：2017/07/19 13:03

No.4 回答者： mike_g 回答日時： 2017/07/19 11:30

E2; =IFERROR(INDEX($B:$B,SMALL(IF($A:$A=$D2,ROW(A:A),""),E$1)),"")

【お断り】上式は必ず配列数式として入力のこと

この回答へのお礼

有難うございます。
配列数式は２枚のｼｰﾄで使用しているのですが、いづれもすったもんだの結果ですので、今一どころか全く自信なし。
いづれは必要になると思うので、これで練習させていただきます。

BS決定にまごついている間のご回答で、お手数をおかけしました。

お礼日時：2017/07/19 13:10

No.3 回答者： 銀鱗 回答日時： 2017/07/19 10:47

んーと。

それはVLOOKUP関数ではできません。

…ではどうするか。
他の関数を組み合わせて処理を行います。

例えばAA列を作業用の列として、
AA1セルに
　＝IF(A1="2017/7/1",1,0)　
（…この数式は、ちょっと嘘を含みます。"2017/7/1"の部分は検索値の日付を入力しているセル番地を入力してください）
と数式を記入し、必要な行まで複製します。
すると、"2017/7/1"の日付の行には「1」、違う場合は「0」が表示されるようになります。

次、
AB列をさらに作業用の列として
AB1セルに
　＝IF(AA1=1,SUM(OFFSET(AA$1,0,0,ROW(),1)),0)
と数式を記入し、必要な行まで複製します。
すると、AA列に「1」と表示されている行でカウントが始まります。

これであとはMATCH関数でAB列の数字を1から順に上から何番目にあるかをしらべ、
対応するB列の行の値をINDEX関数で参照すれば、＜表示例１＞のように作ることも＜表示例２＞のように作ることもできます。
（質問の例では、AB列に「1」が表示されるのは”Aさん”の行で、「2」が表示されるのは”Cさん”の行です）
最終的に
　＝IFERROR(INDEX(B:B,MATCH(ROW(A1),AB:AB,0),"")
これを必要な行まで複製すれば＜表示例１＞
　＝IFERROR(INDEX(B:B,MATCH(COLUMN(A1),AB:AB,0),"")
これを必要な列まで複製すれば＜表示例２＞
のような形にできます。

※ IF、OFFSET、SUM、MATCH、INDEX、ROW、COLUMN、IFERROR
　の各関数の使い方を確認して理解したうえで使うようにしましょう。


・・・余談・・・
ちょっと難しい使い方の「配列数式」にすることで、作業列を作らずに一つのセル（行、列）でできるのですが、
考え方が分からなければ意味がありませんので、このように処理手順を分けて説明をしました。
※そのままコピーして「うまく動かない」と言う人が稀にいるんです。

難しい使い方をしなくても、基本の関数を使う事ができれば、このように処理ができるという例です。
…ほんとはもっと数式を分解して説明を入れたかった…

この回答へのお礼

早々のご回答ありがとうございました。
配列数式やマクロも使ってはいますが、全く自信がなく、何とか汎用の関数の組合せで出来ないかと思い質問させていただきましたが、こんなに多くの関数を組み合わせられたことにびっくりしています。（やはり聞いてみるものだと。。。）

COLUMN 以外の関数は割とよく使ていますので、教えて頂いた式の意味は十分に理解できます。
但し、当方の場合、組合せたら先ず1回でうまく動くことはなく、試行錯誤の繰り返しで何とかかんとかのレベルです。
教えてもらうと非常に簡単に組合せて解決するのが情けない（とほほ・・・）

教えて頂いた式はコピーを取って、「エクセルテク」のフォルダーに入れていつか使用させてもらいますが、今回のケースに限ってはNo2さんの方法で非常に簡単に解決できましたのでNo2さんをBSにさせて頂きますのであしからず。

こんなにたくさんの関数を組み合わせたことは無いので貴重な例として参考になりました。
今後この式（または1部分）は必ず使用することになりますので感謝！！！！

お礼日時：2017/07/19 11:57

No.2 ベストアンサー 回答者： banzaiA 回答日時： 2017/07/19 10:45

VLOOKUP関数ではなくて、フィルターの詳細設定で表示例１のようにできます。

［データ］→フィルター横にある［詳細設定］で。

この回答へのお礼

なるほど！
フィルターオプションとフィルターの組合せでズバリの結果が得られました。
ありがとうございました！
関数以外の発想が無かったので非常に参考になりました。

お礼日時：2017/07/19 11:20

No.1 回答者： yokomaya 回答日時： 2017/07/19 10:37

基本的に複数を引き当てるという考え方は関数にはありません。

なぜならばその関数を記述するセルが単一だからです。
当然VLOOKUPにおいても最初に引き当てたもの以外は無視されます。

では　表示例１を考えてみましょう。
仮にＣさんの方を出す方法があるとしても
2行目の検索値はどうするのですか？
COUNTIFを使って予め何個あるかを数えることは可能ですが
それを別のセルに適用することはマクロ以外ではかなり煩雑になると思われます。

そういう意味においては表示例２の方がまだ少しは楽でしょうね。
但し、2個なら考えられる可能性はなくもないけど３個以上もとなると
やはりマクロの方が合理的ではないかと。

この回答へのお礼

早々のご回答ありがとうございます。
やはりマクロでしょうか。
頑張って勉強します！

お礼日時：2017/07/19 11:18