VBA×XLOOKUP関数|Rangeオブジェクト×配列でシンプルなコードを作る

VBA×XLOOKUPでシンプルなコードを用意する

2020年前後から一部のユーザーに開放されていた関数ですが2024年になり一般ユーザーにも浸透してきた感があります。

よって今回はVBAでもXLOOKUP関数を使ってみることにします。画像（ワークシート）をご覧ください。

左の表のB列の情報をもとにCからE列に情報を取得します。データソースは右の表です。

作業前のワークシート — VLOOKUP関数を使うとデータを取得することができますが今回はXLOOKUP関数を使ってみましょう。

コードはこちらです。

EnjoyExcel

Rangeオブジェクトや配列を使ってできるだけシンプルなコーディングを意識しました。

Sub try_XLookup()

    Dim nme As String 'name　検索値
    Dim rst As Variant 'result　結果を格納する変数
    Dim srg As Range 'searchRange　検索範囲を格納する変数
    Dim rrg As Range 'returnRange 戻り値となる値を格納する変数
    Dim r As Long 'row
    
    ' 検索範囲と返す範囲を設定
    Set srg = Range("I5:I9")
    Set rrg = Range("J5:L9")
    
　　'B列5行目から最終行までループ
    For r = 5 To Cells(Rows.Count, 2).End(xlUp).Row
    
        ' 検索値を指定
        nme = Cells(r, 2)
    
        ' XLOOKUP関数を使って検索
        rst = WorksheetFunction.XLookup(nme, srg, rrg, "データ無", 0, 1)
    
        '値を貼り付け
        Range(Cells(r, 3), Cells(r, 5)) = rst

    Next
    
End Sub

ワークシートに値を出力すると画像のような状態になります。

値が無いところにも「データ無」という情報が入ってますね。

作業後のワークシート — Bさんはデータソースに情報がないので「データ無」が入力されています。XLOOKUP関数内で指示している為です。

ワークシートとコードを見て理解できた方はこれで終わりです。お疲れ様でした。

解説が必要な方は記事を読み進めて下さい。XLOOKUP関数の説明のあとにコードの解説を用意しています。

VLOOKUPを使って表から値を取り出してくるという作業をしています。

VLOOKUP関数を使ってみるVBA-13マクロで関数｜VLOOKUPはエラー対策してから使う

今回は配列も使います。配列が分からない方はこちらの記事も合わせてご覧ください。

配列の考え方【検索にも使える】VBAの2次元配列で作業の高速化を実現させる

XLOOKUP関数とは

まずはXLOOKUPの説明を用意しておきます。XLOOKUP関数は「VLOOKUP関数の進化版」です。

VLOOKUP関数の戻り値は1列だけですがXLOOKUPは範囲（複数列）で値を取得することができます。

つまり戻り値として5列分の値を取得・・・なんていう事も可能です。

動画

まずはXLOOKUP関数の使用感をご覧ください。

ご注意下さい動画では戻り値として複数列を取得できていません。以降で具体的な説明を続けます。

この動画を YouTube で視聴

関数自体の説明（書き方や引数）

関数自体は以下の様に書きます。

=XLOOKUP(検索値, 検索範囲, 戻り配列, [見つからない場合], [一致モード], [検索モード])

続いて引数（パラメータ）を紹介していきます。

XLOOKUP 関数は、範囲または配列を検索し、最初に見つかった一致に対応する項目を返します。

番号引数必須説明
1 検索値* 〇検索する値は
*省略した場合、XLOOKUPは検索範囲に空白のセルを返します。
2 検索範囲 〇検索する配列または範囲
3 戻り配列 〇返す配列または範囲
4 [見つからない場合] 有効な一致が見つからない場合は、指定した [見つからない場合] テキストを返します。
有効な一致が見つからず、[見つからない場合] が見つからない場合、#N/A が返されます。
5 [一致モード] 一致の種類を指定します。
0 – 完全一致。
見つからない場合は、#N/A が返されます。
これが既定の設定です。
6 [検索モード] 使用する検索モードを指定します。
1 – 先頭の項目から検索を実行します。
これが既定の設定です。
1から3のパラメータが必須です。[]で用意された4から6の引数は任意で設定してください。
Microsoftドキュメント：XLOOKUP関数より抜粋、加工

番号	引数	必須	説明
1	検索値*	〇	検索する値は省略した場合、XLOOKUPは検索範囲*に空白のセルを返します。
2	検索範囲	〇	検索する配列または範囲
3	戻り配列	〇	返す配列または範囲
4	[見つからない場合]		有効な一致が見つからない場合は、指定した [見つからない場合] テキストを返します。有効な一致が見つからず、[見つからない場合] が見つからない場合、#N/A が返されます。
5	[一致モード]		一致の種類を指定します。 0 – 完全一致。見つからない場合は、#N/A が返されます。これが既定の設定です。
6	[検索モード]		使用する検索モードを指定します。 1 – 先頭の項目から検索を実行します。これが既定の設定です。