エクセル関数 検索機能

質問

Ａ　　Ｂ
１　山田　２５歳
２　田中　２７歳
３　佐藤　３２歳
４　落合　３４歳
５　田中　４１歳
６　田中　２３歳

という一覧を作成し、Ｄ２セルに名字を入力すると
Ｅ２セルに名字、Ｆ２セルに年齢が表示される
検索機能をつけたいです。たとえば落合さんを探したい時は
Ｅ２は　=VLOOKUP(D2,A1:B6,1,TRUE)
Ｆ２は　=VLOOKUP(D2,A1:B6,2,TRUE)

で検索できましたが、田中さんの様に複数の場合
Ｄ２に名字を入力するだけで
Ｅ２　Ｆ２
Ｅ３　Ｆ３
Ｅ４　Ｆ４
に３人分を表示させる関数はありますか？

回答No.2

tom04 · ベストアンサー

No.1です！
たびたびごめんなさい。

前回の配列数式はデータ量が多い場合はPCに負担がかかりますので
作業列を使う方法も紹介しておきます。

↓の画像でA列を作業用の列とさせてもらっています。
よって、元のデータが1列ずつずれてしまいます。

A2セルを
=IF(B2=$E$2,ROW(A1),"")
としてオートフィルで下へコピーします。

F列の数式が1000行まで対応できるようにしていますので
1000行くらいまでコピーしても構いません。

F2セルに
=IF(OR($E$2="",COUNT($A$2:$A$1000)<ROW(A1)),"",INDEX(B$2:B$1000,SMALL($A$2:$A$1000,ROW(A1))))

という数式を入れオートフィルで列方向と行方向にコピーします
これで画像のようになります。
今回は配列数式ではありませんので
そのままコピーされても問題ありません。

どうも何度も失礼しました。m(__)m

お礼

おかげさまで解決できました！
知識の浅い私でも理解できて、アレンジのしやすい式でした
本当にありがとうございます（＾＾）

回答No.3

imogasi

１つの関数では在りません。
関数を組み合わせたり、作業列を使ったりします。
検索関数には
ＶＬＯＯＫＵＰ
ＭＡＴＣＨ
という関数があるが、該当の最初のものしか返しません。
ーーー
作業列を使うやり方の１例は
例データ　　Ａ－Ｄ列　Ｄ１にには、検索する（Ａ列の）氏名を入れておく
田中
山田 ２５歳 田中 ２７歳
田中 ２７歳 1 田中 ４１歳
佐藤 ３２歳 田中 ２３歳
落合 ３４歳
田中 ４１歳 2
田中 ２３歳 3
ーー
Ｃ列Ｃ２の式　=IF(A2=$D$1,MAX($C$1:C1)+1,"")
下方向に式を複写。　該当行分に上行から連番を振っている。
Ｄ列Ｄ２には、=INDEX($A$1:$B$100,MATCH(ROW()-1,$C$1:$C$100,0),COLUMN()-3)
Ｅ２に式を複写。
Ｄ２：Ｅ２の式を下方向に式を複写。
ーー
結果
上記Ｄ，Ｅ列
ーーー
これを私は「imogasi方式」と名づけている。
Ｇｏｏｇｌｅで「imogasi方式」で照会してもらえれば、私および＃１のご回答、その他のタイプの回答が、たくさん出る。
それほど毎日ように出る質問だ。

お礼

ご回答いただきありがとうございます
いろいろな方法があるのだと参考になりました

回答No.1

tom04

こんばんは！
色々方法はあるかと思いますが・・・
一例です。

↓の画像のE2セルに
=IF(OR($D$2="",COUNTIF($A$2:$A$1000,$D$2)<ROW(A1)),"",INDEX(A$2:A$1000,SMALL(IF($A$2:$A$1000=$D$2,ROW($A$1:$A$999)),ROW(A1))))

これは配列数式になってしまいますので、
この画面からコピー＆ペーストしただけではエラーになると思います。
E2セルに貼り付け後、F2キーを押すか、数式バー内で一度クリックします。
編集可能になりますので
Shift＋Ctrl＋Enterキーで確定します。
数式の前後に｛　｝マークが入り配列数式になります。

E2セルを列方向と行方向にオートフィルでコピーすると
画像のような感じになります。

尚、数式は1000行まで対応できるようにしていますが、
データ量によってアレンジしてみてください。

以上、参考になれば幸いですが、
他に良い方法があれば読み流してくださいね。m(__)m