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を実行しましょう。

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

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