VLOOKUP関数の複数該当の値をすべて取得する方法！VBAで同じ処理も可！

VLOOKUP関数で複数該当の検索結果をすべて取得したいときはないでしょうか。

通常VLOOKUP関数は検索値の1つ目しか取得できませんが、2つ目、3つ目と取得したいときなどです｡

けど、そんな中で悩むことは、

ですよね。

今回はそんなお悩みを解決する

についてまとめます！

VLOOKUP関数の複数該当の値を取得する方法

Excel のVLOOKUP関数で複数該当の値を取得する手順について説明をします。

今回のサンプルデータと実行結果について

今回のサンプルデータは以下の通り、A列に都道府県、B列に名前が入力されています。

処理結果はE2セルに入力した都道府県の「千葉県」に該当する名前すべてを抽出しG列へ出力していきます。

VLOOKUP関数の1つ目の検索結果を出力する

こちらはVLOOKUP関数の基本機能ですね。
以下の数式を入力し、1つ目の検索結果を出力しましょう。

VLOOKUP関数の2つ目の検索結果を出力する

VLOOKUP関数の2つ目の検索結果を取得する方法はMATCH関数を使います。

数式は以下の通りです。

MATCH関数は検索値に該当する検索位置を返す関数です。

INDIRECT関数は参照したいセルのアドレスを文字列で指定することができる関数です。

MATCH関数で得た一つ目の検索位置をINDIRECT関数でアドレス指定することにより一つ目以降の検索範囲をVLOOKUP関数へ指定することができ、2番目がわかるというわけです。

2番目の検索結果が表示されましたね。

VLOOKUP関数の3つ目以降の検索結果を出力する

仕上げに3つ目以降の検索結果を出力してみましょう。

2つ目と同じやり方で3つ目以降を表示させるとなると、順番の数分MATCH関数を繰り返し実施しなければならず非効率的です。

そこで、作業列を追加し、順番ごとに検索結果とセルの位置を出力し、セルの位置を参照しながらVLOOKUP関数の検索範囲を設定していきたいと思います。

VLOOKUP関数出力用の作業セルを用意する

H列にVLOOKUP関数の出力結果である検索位置、I列に検索値があるセルの番号を出力する値を入力しますので、空欄のセルを用意します。

1行目の項目名は「検索位置」、「セル番号」とします。

続いて、作業列に数式を入力していきます。

G2のセルへ以下を入力します。

E2の「千葉県」をキーにA列を検索し、一つ目の位置番号を出力します。

結果は1行目から3つ目を意味する3となりました。

続いてI2セルへ以下を入力します。

H3セルへ以下を入力します。

MATCH関数の検索開始位置を1つ目のセル番号+1番目から開始して2つ目を探していくというわけです。

I3セルへ以下を入力します。

1番目と2番目の検索位置を合計し、2番目の値が存在する行番号を算出します。

最後の仕上げです。

2番目のH-Iセルを選択し、6番目までドラッグし、数式をコピーします。

G4に以下の数式を入力します。

6番目までドラッグします。

「千葉県」に該当する名前がすべて抽出されましたね！

タカヒロ

なお、VLOOKUP以外にINDEX関数だけでも同じ結果を出力することができます。ちなみにINDEX関数のほうが繰り返し処理が少ないので処理速度は速いです。詳細は以下の記事をご覧ください。

Excel VBAで複数の検索結果を出力する

次はこれまでVLOOKUPとMATCH関数の組み合わせで行ってきた複数の検索結果の出力をVBAで一気に処理していきます。

サンプルデータと処理結果は関数でおこなったものと同様です。
E2セルへ検索値を入力し、G列へ検索結果すべてを出力します。

タカヒロ

関数の時に作成したH、Iの作業列は不要で、スッキリしていますね。

VBAは以下の通りです。

Sub 複数の検索結果を出力する()

    Dim strTemp As Variant
    Dim i As Integer
    Dim k As Integer
    
    '出力開始行数指定
    k = 2
    
    '検索値を取得します。
    strSerchKey = Range("E2").Value
    
    
    '出力セルの値のみをクリアします。
    'https://extan.jp/?p=3525#%E3%82%BB%E3%83%AB%E3%81%AE%E5%80%A4%E3%81%AE%E3%81%BF%E3%82%92%E3%82%AF%E3%83%AA%E3%82%A2%E3%81%99%E3%82%8B
    Range("F2:G7").ClearContents
    
    '処理対象のセルをレンジ指定します。
    strTemp = Range("A2:B13")
    
    '配列に格納された値すべてを対象に処理します。
    For i = LBound(strTemp, 1) To UBound(strTemp, 1)
        If strTemp(i, 1) = strSerchKey Then
            Cells(k, 7) = strTemp(i, 2)
            Cells(k, 6) = k - 1 & "番目"
            k = k + 1
        End If
    Next

End Sub

E2に「千葉県」を入力し、

VBAへ処理対象のデータの範囲を指定します。

VBAを実行しましょう。

マクロから「複数の検索結果を出力する」を選択し実行します。

はい、「千葉県」に該当する名前が出力されましたね。

他の検索値で検索結果を出力する

他の検索値で検索結果を出力してみましょう。

東京都は1件ですね。

神奈川県は2件。

埼玉県は3件です。

検索値に応じた検索結果が出力されることが確認できましたね。

タカヒロ

関数の場合入力したセルごとに処理を繰り返すことになりますが、VBAの場合はテーブルやシート単位で処理できますので、件数があるデータの場合は処理時間を短縮することができますね。

VBAの実装手順

実装手順は以下の通りです。

今回はExcel側にこのVBAを実装します。

①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」＋「F11」でもOKです。

②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。

③右ペインのウインドウに上記のVBAを入力します。

こちらで完了です。

VBAを実行する

では早速VBAの実行をしてみましょう。

①「開発」タブの「VBA」をクリックし実行したいマクロを選択し、「実行」をクリックします。

②処理がされたことが確認できれば完了です。
※完了メッセージやステータス管理など必要に応じて実装してもらえばと思います。

さいごに

いかがでしょうか。

今回は、

についてまとめました。

また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。