脱VBA初心者!VLOOKUP関数の基礎の基礎をわかりやすく解説!

Excelの表でコードや区分の横に対応する名称を出すときなど、VLOOKUP関数を使います。

しかし、初心者にとっては

「VLOOKUP関数の使い方がいまいちわからない…」
なんて方も多いのではないでしょうか?

そこで、本記事では、

  • VLOOKUP関数とは何か
  • VBAでVLOOKUP関数を使う方法
  • といった基本的な内容から、

  • エラーへの対処法
  • 別シート、別ブックを参照する方法
  • 参照範囲を配列で置き換える
  • VLookupの高速化
  • という応用的な内容までお伝えします。

    VBAのVLOOKUP関数についてわかりやすく解説していますので、ぜひ参考にしてください!

    本記事を読み終える頃には、表においてVLOOKUP関数を使えるだけでなく、VBAからも自在に使えるようになっていますよ!

    目次

    VLOOKUP関数とは

    最初に、そもそもVLOOKUP関数とは何なのかを解説します。冒頭で触れたとおり、コードや区分から対応する値を取得するものです。

    伝票を例にとると、商品コードから商品名や単価を取得すると考えてください。その具体例は以下です。

    以下の例では、商品コードと数量のみ手入力で、商品名と単価はVLOOKUP関数を使用して取得しています。金額は単価と数量の掛け算、合計はSUM関数を使って自動で計算結果を表示しています。

    Excel_VBA_VLOOKUP_伝票

    [書式]

    =VLOOKUP(検索値, 範囲, 列番号, 検索方法の指定)

    検索値は検索したい値、範囲は対応する名称がある範囲、列番号は何列目に欲しい値があるかの指定です。

    検索方法は完全一致かあいまい検索を指定しますが、通常は完全一致で使用するため、FALSEを指定すればよいでしょう。

    [使用例]

    Excel_VBA_VLOOKUP_VLOOKUP解説

    商品名の取得を文章で説明しましょう。

  • B3セルの商品コードに対応する商品名を、C3セルに表示する。
  • 商品名は範囲H3セルからJ8セルまでにある。
  • 範囲の中で商品名は範囲の2列目にある。
  • あいまい検索はしないのでFALSE。
  • 結果、図のオレンジ色の枠にある式ができます。となりのD3セルに単価も取得して設定してみました。オレンジ色と青色の枠内の式を比較すると、動作イメージが明確になるでしょう。

    これが、Excelの表におけるVLOOKUP関数です。

    VBAでVLookupを使う方法

    ExcelVBAにおいて、コードや区分から値を取得する場合は以下のようにします。

    [書式]

    WorksheetFunction.VLookup(検索値, 範囲, 列番号, 検索方法の指定)

    次の例では、B3セルに入力した商品コードに対応する商品名を、表から取得してC3セルに表示しています。

    [使用例]

    Sub vlookup_sample1()
    
      Range("C3") = WorksheetFunction.VLookup(Range("B3"), Range("E3:G8"), 2, False)
    
    End Sub

    [実行結果]

    Excel_VBA_VLOOKUP_VBAでVLOOKUP

    いかがでしょうか?

    キーとなる値からそれに対応する値を取り出す、といった関係データベース的な使い方ができますね。

    エラーへの対処法

    VBAでVLOOKUP関数を使ったコーディングをするのは、今まで解説してきたことで十分可能です。実は、それよりもエラーとその対処方法のほうが難しいのです。

    「WorksheetFunction クラスの VLookup プロパティを取得できません。」このエラーは特によく起こります。

    よくある理由を順に解説していきます。

    参照と型が異なる場合

    検索したい値のあるセルと、範囲で指定するセルの型が異なるときに起こります。

    以下の例では、B3セルを文字列にして型を合わせるとエラーは解消します。

    Excel_VBA_VLOOKUP_プロパティ取得不可1

    参照範囲に値が存在しない

    検索範囲に値が存在しないときにエラーが発生します。

    Excel_VBA_VLOOKUP_プロパティ取得不可2

    VBAの一般的なエラーの対処法を解説します。

    次のサンプルコードでは、実行時エラー1004が発生した場合に、C3セルに「値取得不可!」と入れています。

    Sub vlookup_sample2()
    
    On Error GoTo err:
    
        Range("C3") = WorksheetFunction.VLookup(Range("B3"), Range("E3:G8"), 2, False)
    
        Exit Sub
    
    err:
    
        If err.Number = 1004 Then
            Range("C3") = "値取得不可!"
        End If
    
    End Sub

    最初の方にある「On Error Goto err:」は、エラーが発生したら下の方の「err:」というラベルまでジャンプせよという意味です。

    ただしエラーは1004以外にもありえるので、1004の場合のみ「値取得不可!」と返すようにしているのが「err:」の後のIf文です。

    実行時エラー1004だけでも「範囲に値が存在しない」以外にもさまざまな原因があるので、あえてザックリと「値取得不可!」としています。

    VBAからセルに記述してデバッグする方法

    以下のコードを実行してみましょう。

    Sub vlookup_sample3()
    
        Range("C3") = WorksheetFunction.VLookup(Range("B3"), Range("E3:G8"), 4, False)
    
    End Sub

    1004エラーが発生します。(環境によっては、実行時にエラーが出るケースもあります。)

    検証方法を解説します。コード中のVLOOKUP関数の部分を、セルに式で記述してみましょう。

    Excel_VBA_VLOOKUP_プロパティ取得不可3

    商品名は「#REF!」と表示されており、値が取得できていません。これはVLOOKUP関数の第3引数である列番号が4となっているのが原因です。

    範囲はE3セルからG8セルまで3列しかないのに、4列目を指定してしまっています。ここでは商品名がほしいので、列番号を2と修正すれば問題は解消できます。

    想定する最終形で実際に式として記述してみる、これはとても役に立つデバッグ方法です。そして、このデバッグ方法をもう一歩さらに進めてみましょう。

    ここまでは、ExcelシートのセルにVLOOKUP関数を直接入力していましたが、セルに入力せずにVBAからVLOOKUP関数をセルに入力することもできます。

    それはRangeオブジェクトのFormulaプロパティを使う方法です。

    Rangeオブジェクト.Formula = “=数式”

    これで指定したRangeオブジェクトにVBAから関数式を入力することができます。

    Sub vlookup_sample4()
        Range("C3").Formula = "=VLOOKUP(B3, E3:G8, 4, False)"
    End Sub

    実行結果:
    VLookup01

    これでExcelシートとVBAの開発環境(VBE)を行ったり来たりせずに、VBAの開発環境だけでデバッグをすることが可能です。

    ぜひ覚えてください。

    Formulaプロパティを使ってVBAからセルに直接関数を入力する方法については、こちらで詳しく解説しています。ぜひ参考にしてください。

    別シート、別ブックを参照する方法

    ここまでは同じシート内の参照範囲を検索してきました。参照範囲が大きくなったり、勝手に変更されたくない場合などは検索結果と参照範囲を別シートや別ブックに分ける場合も考えられます。

    そんな場合は、参照範囲の指定を以下のように記述することで対応できます。

    Workbooks("ブック名.xlsx").Worksheets(“シート名”).Range((参照範囲)

    参照先が同じブック内の場合はWorkbooks(“ブック名.xlsx”).は省略可能です。

    これは参照先のブックが開いていることが前提になります。

    Sub vlookup_sample5()
        With Worksheets("入力")
            .Range("C3") = WorksheetFunction.VLookup(.Range("B3"), Worksheets("データ").Range("A3:C8"), 2, False)
        End With
    End Sub

    実行結果:
    VLookup02

    参照範囲を配列で置き換える

    これまでは参照範囲がExcelシート上のあるセル範囲でした。VBAを使っていると、Excelシート上のセル範囲ではなく、配列を参照範囲に指定したい場合も出てきます。

    検索する参照範囲を配列にした場合についてみてみましょう。

    Sub vlookup_sample6()
        Dim arr() As Variant
        arr = Range("E3:G8")
        
        '参照範囲を配列に変更
        Range("C3") = WorksheetFunction.VLookup(Range("B3"), arr, 2, False)
    End Sub

    実行結果:
    VLookup03

    まず、配列arrにセル範囲”E3:G8″の値を格納しています。VLookup関数の参照範囲に配列arrを指定しています。

    なお、配列とセル範囲の値を共有する方法については、こちらで詳しく解説していますので、ぜひ参考にしてください。

    VLookupの高速化

    これまでは、比較的小さいデータをVLookupで参照して検索してきました。

    しかし、例えば1万件や10万件を超える大きなデータから検索を数千回繰り返したい場合もあります。そんな場合はVLookup関数の処理速度も考慮しなければ、時間がかかりすぎてしまうことになります。

    時間がかかりすぎてしまえば、代わりの手段も考えなければなりません。

    ExcelではVLOOKUP関数の代わりに、INDEX関数とMATCH関数を組み合わせて使わうこともよくあります。

    INDEX関数は

    =INDEX(範囲,縦位置)

    “縦位置”に”範囲”内の位置を数値で入力するとその値を返します。配列のインデックス番号を指定したら、要素の値が返ってくるのと使い方が似ていますね。

    MATCH関数は

    =MATCH(検索値,範囲,一致か近似値か)

    ”検索値”に値を入力すると”範囲”の中の位置を数値で返します。

    MATCH関数で検索値の位置を出力して、その位置を使ってある範囲内の値を抽出します。これでVLOOKUP関数と同じ結果が得られるようになります。

    Sub vlookup_sample7()
        Range("C3").Formula = "=INDEX(F3:F8, MATCH(B3, E3:E8))"
    End Sub

    実行結果:
    VLookup04

    また、VBAでは検索にはFindメソッドをよく使います。

    これらで処理速度を比較してみましょう。使うデータは以下のVBAを使って作成しています。

    Sub vlookup_sample9()
        Const num = 10000
        
        Dim i As Long
        For i = 1 To num
            Range("B" & i + 2) = "C" & Format(CStr(Round(num * Rnd) + 1), "00000")
            Range("E" & i + 2) = "C" & Format(CStr(i), "00000")
            Range("F" & i + 2) = "商品" & Format(CStr(i), "00000")
        Next i
    End Sub

    VLookup06

    1万個のデータリストから商品名を抽出する作業を1万回繰り返す時間で比較します。

    Declare Function GetTickCount Lib "KERNEL32.DLL" () As Long
    
    Sub vlookup_sample8()
        Const num = 10000
        
        Dim newTimer As Long
        Dim t1 As Long, t2 As Long, t3 As Long
        
        'VLookup関数
        newTimer = GetTickCount '計測開始
        Dim i As Long
        For i = 1 To num
            Range("C" & i + 2) = WorksheetFunction.VLookup(Range("B" & i + 2), Range("E3:F" & num + 2), 2, False)
        Next i
        t1 = GetTickCount - newTimer '処理時間算出
        
        'Index関数 & Match関数
        newTimer = GetTickCount '計測開始
        For i = 1 To num
            Range("C" & i + 2) = WorksheetFunction.Index(Range("F3:F" & num + 2), _
                                    WorksheetFunction.Match(Range("B" & i + 2), Range("E3:E" & num + 2)))
        Next i
        t2 = GetTickCount - newTimer '処理時間算出
        
        'Findメソッド
        newTimer = GetTickCount '計測開始
        Dim myObj As Range
        For i = 1 To num
            Set myObj = Range("E3:E" & num + 2).Find(Range("B" & i + 2))
            Range("C" & i + 2) = Range("F" & myObj.Row)
        Next i
        t3 = GetTickCount - newTimer '処理時間算出
        
        MsgBox "VLookup関数: " & t1 & "ミリ秒" & vbCrLf & _
                "Index関数 & Match関数: " & t2 & "ミリ秒" & vbCrLf & _
                "Findメソッド: " & t3 & "ミリ秒"
    End Sub

    実行結果:
    VLookup05

    実行結果では

  • VLookup関数で約12.5秒
  • Index関数とMatch関数で約2.7秒
  • Findメソッドで約39.3秒
  • という結果になりました。この結果からは、VLookup関数よりも代わりにIndex関数とMatch関数を使った方が処理時間を短縮できます。

    数千や数万個のデータリストから数千個のデータを検索して抽出する場合は、Index関数とMatch関数の組み合わせを使用することをオススメします。

    ちなみに、Findメソッドは処理時間がかなり遅い結果となりました。Findメソッドの使い方については、こちらのサイトで詳しく解説していますので、ぜひ参考にしてください。

    まとめ

    本記事では、VLOOKUP関数について解説しました。

    セルに式で記述する、VBAでも実装できる、この両方をマスターするといろんなことができるようになります。

    ぜひ、頑張ってマスターしてくださいね!

    この記事を書いた人

    関西在住のITエンジニアです。普通の会社に勤務しながら、侍エンジニアのインストラクター、ライターとして活動しています。

    目次