【Excel VBA】XLOOKUP関数でモダンな表引き検索をする方法

2025年8月13日

長年にわたりExcelの表引き検索の主役であったVLOOKUP関数ですが、いくつかの弱点（検索列が一番左にないといけない、見つからないとエラーになる等）がありました。

Excel for Microsoft 365およびExcel 2021以降では、これらの弱点をすべて克服した、非常に強力で柔軟な後継関数**XLOOKUP**が登場しました。

もちろん、このXLOOKUP関数もVBAから利用できます。この記事では、VBAからWorksheetFunction.XLOOKUPを使い、エラー処理まで含めたスマートな表引き検索を実装する方法を解説します。

【重要】XLOOKUP関数が使える環境

このテクニックを利用するには、お使いのExcelが**XLOOKUP関数をサポートしているバージョン（Microsoft 365 または Excel 2021以降）**である必要があります。旧バージョンのExcelではこのコードは実行できませんのでご注意ください。

VBAでのXLOOKUP関数の基本構文

VBAからXLOOKUPを呼び出す際の基本的な構文は、シート上で使う場合と同じです。

WorksheetFunction.XLookup(検索値, 検索範囲, 戻り範囲, [見つからない場合の値])

検索値: 探したい値。
検索範囲: 検索値を探す対象の1列または1行の範囲。
戻り範囲: 検索が成功した場合に、値を返してほしい範囲。複数列の指定も可能。
[見つからない場合の値] (任意): 検索値が見つからなかった場合に返す値を指定します。これを指定することで、VLOOKUPで必要だった複雑なエラー処理が不要になります。

完成したVBAコード

以下は、「商品マスタ」シートのテーブルから、指定した商品コードに一致する「商品名」と「価格」の両方を一度に取得するVBAコードです。

Sub XLookupInVBA()

    ' 変数を宣言します
    Dim lookupValue As String
    Dim lookupArray As Range
    Dim returnArray As Range
    Dim result As Variant

    '--- 設定 ---
    lookupValue = "P-003" ' 検索したい商品コード
    
    With ThisWorkbook.Worksheets("商品マスタ")
        ' 検索値を探す列 (B列)
        Set lookupArray = .Range("B2:B100")
        ' 取得したい値が含まれる列 (C列とD列)
        Set returnArray = .Range("C2:D100")
    End With
    '--- 設定ここまで ---
    
    ' --- 1. XLOOKUP関数でデータを取得 ---
    ' 見つからない場合は「該当なし」という文字列を返すように指定
    result = WorksheetFunction.XLookup(lookupValue, lookupArray, returnArray, "該当なし")

    ' --- 2. 取得した結果を判定して表示 ---
    ' 戻り値が配列かどうかで、見つかったかを判断
    If IsArray(result) Then
        ' 見つかった場合 (戻り範囲が複数列なので、結果は配列になる)
        MsgBox "検索結果が見つかりました。" & vbCrLf & vbCrLf & _
               "商品名: " & result(1, 1) & vbCrLf & _
               "価格: " & result(1, 2) & " 円", vbInformation
    Else
        ' 見つからなかった場合（"該当なし"という文字列が返される）
        MsgBox "商品コード「" & lookupValue & "」は見つかりませんでした。", vbExclamation
    End If

End Sub

コードのポイント解説

① `WorksheetFunction.XLOOKUP`

result = WorksheetFunction.XLOOKUP(lookupValue, lookupArray, returnArray, "該当なし")

XLOOKUP関数をVBAから呼び出しています。この関数の最大の利点の一つが、第4引数**[見つからない場合の値]**です。

VLOOKUPでは、見つからない場合に実行時エラーが発生するため、On ErrorやIsErrorといったエラー処理が必須でした。しかしXLOOKUPでは、この引数を指定しておけば、見つからなかった場合にエラーを発生させることなく、指定した値（この例では文字列"該当なし"）を返してくれます。