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

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

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

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

・INDEXとMATCH関数で複数該当の値を取得したいが方法がわからない。
・関数の他にVBAで効率的に行いたいがやり方がわからない。

ですよね。

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

・INDEXとMATCH関数で複数該当の検索結果を取得する方法
・Excel VBAで複数該当の検索結果を出力する

についてまとめます!

INDEX関数とMATCH関数で複数該当の値を取得する方法

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

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

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

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

INDEX関数とMATCH関数出力用の作業セルを用意する

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

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

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

用意ができましたら、おなじみの一つ目の検索結果を出力していきます。
G2のセルへ以下を入力します。

=MATCH($E$2,A$1:A$13,0)

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

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

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

=H2

最後にG2セルへ以下を入力します。

=INDEX(B$1:B$13,I2,1)

はい、検索結果が表示されましたね。

2つ目以降の検索結果を出力する

次に今回のメインの2つ目以降の検索結果を出力していきましょう。

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

=MATCH($E$2,INDIRECT("A"& I2+1 &":A13"),0)

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

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

=SUM(H$2:H3)

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

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

=INDEX(B$1:B$13,I3,1)

G2セルからドラッグしてコピーしてもよいでしょう。

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

最後の仕上げです。

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

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

タカヒロ
タカヒロ
6番目は該当する値がないため「#N/A」と表示されます。気になる方はIF文で空白表示をするとよいでしょう。

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

次はこれまでINDEXと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へ処理対象のデータの範囲を指定します。

strTemp = Range(“A2:B13”)

VBAを実行しましょう。

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

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

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

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

東京都は1件ですね。

神奈川県は2件。

埼玉県は3件です。

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

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

VBAの実装手順

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

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

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

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

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

こちらで完了です。

VBAを実行する

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

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

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

さいごに

いかがでしょうか。

今回は

・INDEXとMATCH関数で複数該当の検索結果を取得する方法
・Excel VBAで複数該当の検索結果を出力する

についてまとめました。

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



この記事の関連キーワード

こちらの記事の関連キーワード一覧です。クリックするとキーワードに関連する記事一覧が閲覧できます。








コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

CAPTCHA ImageChange Image