VLOOKUPで複数該当した際にすべてを表示する方法

こんにちは！さて今回はVLOOKUPで該当するデータが複数行あった場合に、そのすべてを抽出し表示します！
VLOOKUPって結果一つしか表示できないじゃん！って思うかもしれませんが、そこは工夫してやってみたいと思います。

まずはこんなフォーマットを用意しました。

年齢層を選択したら、下の表にはデータリストで該当の年齢層データをすべて表示するようにしたいと思います。
普通にVLOOKUPを行ったら該当した一番上のデータしか表示できないですが、ここから工夫していきます。

検索条件に合致したデータに連番をふっていきます

データリストの横の列に「検索条件に合致するデータの場合、連番をふる」といった条件で関数を組んでいきます。
下記図のようになります。

試しに50代を検索したところ、データリストの50代が上から1,2,3と連番がふられているのが確認できます。
関数を見ていきましょう。

=IF($F$3=K4,COUNTIF($K$3:K4,K4),0)

まずはIF文ですが
$F$3(検索値)がK4(このデータ行の年代)と一致していたら、COUNTIFを行い、不一致だったら0
という流れになっています。

問題の連番を行っているCOUNTIFを見てみます。
$K$3:K4の範囲の中に、K4(50代)がいくつあるかカウントしています。

1行目に関してはIF文の時点で不一致でしたので0が入ります。

データリストの1行目に入力したこの関数を、データリストの下の行までコピペしました。
IF文では一致する3行目を見てみましょう。

3行目はIF文が$F$3=K6で一致してますので、COUNTIFを行います。
$K$3:K6の範囲の中でK6(50代)がいくつあるかカウント
ここで、K6には50代がありましたので「1」になります！

次にとばして10行目と18行目ですが

10行目：IF文は一致し、$K$3:K13の範囲には2つあるので「2」
18行目：IF文は一致し、$K$3:K21の範囲には2つあるので「3」

という風に、検索に該当した行から順に連番がふられるようになっています。

次に、データを表示する表にも連番を用意します。

こちらはシンプルに上から1,2,3～と連番をふります。

いよいよVLOOKUPを使っていきます

関数を見ていきましょう。

=IFERROR(VLOOKUP($D11,$I$4:$L$27,2,0),””)

IFERRORはVLOOKUPがエラーだった際に空白を表示するためです。

ではVLOOKUPを見ていきましょう。
検索条件は$D11（1）を使い
先ほど作成したデータリストの連番を検索範囲（$I$4:$L$27）に指定することで条件に合致した1つ目のデータを取得出来ます！

あとはこの関数をコピペすれば完成ですね。（VLOOKUP3つ目の引数は列指定なので、コピペで列をずらす際に3,4と変える必要があります）

これで完成となります！作業工程は下記動画でも確認できます。
※ちなみにすべてキーボードのみの脱マウス操作です。

VLOOKUPで複数該当した際にすべてを表示する方法の紹介でした。