VLookupより速いIndex関数とMatch関数を使ったエクセルの検索

2020年3月21日2023年6月3日

大量のデータを検索する時はVLookup関数より、Index関数とMatch関数を使って検索した方が速く処理できます。VLookup関数で処理に時間がかかる又は遅いと感じたときはIndex関数とMatch関数の組み合わせで検索処理を実行してみて下さい。

Index関数とMatch関数を使った検索アプリの説明

■ サンプルアプリの処理内容
　右の表のNo を検索して、該当する氏名を左の表にセットします。

「Index関数とMatch関数の検索」ボタンをクリックすると右表のNoを検索してヒットした名前を左表の赤枠に名前をセットします。

サンプルアプリのソースコード　　コードの作成手順はこちら

Sub ボタン1_Click()

    'ワークシートを指定する
    Dim Sh1 As Worksheet
    Set Sh1 = ThisWorkbook.Worksheets("Sheet1")

    'Match用検索範囲(1列)を指定する
    Dim Rng1 As Range
    Set Rng1 = Range(Sh1.Cells(6, 6), Sh1.Cells(20, 6))
    Dim MatchHitRow As Long

    'Index用検索範囲(2列)を指定する
    Dim Rng2 As Range
    Set Rng2 = Range(Sh1.Cells(6, 6), Sh1.Cells(20, 7))

   '変数を定義する
    Dim workEndR1, workTmpR1 As Long, tmpStr1 As String
   'データの最終行を取得する
    workEndR1 = Sh1.Cells(Rows.Count, 2).End(xlUp).Row
    
    '6行目からデータの最終行までループ
    For workTmpR1 = 6 To workEndR1
    
        '検索キーをセット
        tmpStr1 = Sh1.Cells(workTmpR1, 2).Value
        
        '検索でヒットした行が範囲内で何行目の位置か取得
        MatchHitRow = WorksheetFunction.Match(tmpStr1, Rng1, 0)
        '上でヒットした位置の2列目を取得してセルにセット
        Sh1.Cells(workTmpR1, 3).Value = Application.WorksheetFunction.Index(Rng2, MatchHitRow, 2)
           
    Next
    
End Sub

MatchメソッドとIndexメソッドの説明

MATCH関数は、検索範囲内を指定した検索ワードで検索して、ヒットした値がある位置を上から数えた行番号で返す関数です。
　　WorksheetFunction.Match( 検索値 , 検索範囲 , 検索方法)

　　検索方法
　 0：検索範囲から完全一致で検索
　 1：検索範囲から近似値以下で最大値を検索
　-1：検索範囲から近似値以下で最小値を検索

　　第3引数の検索方法で「1」、「-1」を選択する場合、
　　「1」の場合はデータを昇順、「-1」の場合はデータを降順に並べ替えておく必要があります。

INDEX関数は、範囲内から指定した行と列にある値を取得する関数です。
　WorksheetFunction.Index(範囲, 行番号, 列番号)

MatchメソッドとFindメソッドとの違いについては、下記リンク先をご覧ください。
　https://scodebank.com/?p=1306