VBA×XLOOKUPでシンプルなコードを用意する
XLOOKUP関数は2020年前後から一部のユーザーに開放されていた関数です。
リリース当初は一部の人しか使っていない印象でしたが2024年になり一般ユーザーにも浸透してきた感があります。
よって今回はVBA（マクロ）でもXLOOKUP関数を使ってみることにします。用意した画像をご覧ください。
左の表のB列の情報をもとにCからE列に情報を取得します。データソースは右の表です。
コードはこちらです。
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ワークシートに値を出力すると画像のような状態になります。
値が無いところにも「データ無」という情報が入ってますね。
ワークシートの画像とコードを見て理解できた方はこれで終わりです。お疲れ様でした。
解説が必要な方は記事を読み進めて下さい。XLOOKUP関数の説明のあとにコードの解説を用意しています。
関連記事
VLOOKUP関数を使って表から値を取り出してくるという作業を紹介しています。
VLOOKUP関数を使ってみるVBA-13マクロで関数｜VLOOKUPはエラー対策してから使う
本記事では配列も使います。配列が分からない方はこちらの記事も合わせてご覧ください。
関連書籍
こちらの書籍で配列を勉強することができます。
ポチップ
電子書籍はこちらです。
XLOOKUP関数とは
まずはXLOOKUPの説明を用意しておきます。XLOOKUP関数は「VLOOKUP関数の進化版」です。
VLOOKUP関数の戻り値は1列だけですがXLOOKUPは範囲（複数列）で値を取得することができます。
つまり戻り値として5列分の値を取得・・・なんていう事も可能です。
動画
まずはXLOOKUP関数の使用感をご覧ください。
ご注意下さい動画では戻り値として複数列を取得できていません。以降で具体的な説明を続けます。
関数自体の説明（書き方や引数）
関数自体は以下の様に書きます。
=XLOOKUP(検索値, 検索範囲, 戻り配列, [見つからない場合], [一致モード], [検索モード])
続いて引数（パラメータ）を紹介していきます。
XLOOKUP 関数は、範囲または配列を検索し、最初に見つかった一致に対応する項目を返します。
Microsoftドキュメント：XLOOKUP関数より抜粋、加工
番号 引数 必須 説明 1 検索値* 〇 検索する値は
*省略した場合、XLOOKUPは検索範囲に空白のセルを返します。2 検索範囲 〇 検索する配列または範囲 3 戻り配列 〇 返す配列または範囲 4 [見つからない場合] 有効な一致が見つからない場合は、指定した [見つからない場合] テキストを返します。
有効な一致が見つからず、[見つからない場合] が見つからない場合、#N/A が返されます。5 [一致モード] 一致の種類を指定します。
0 – 完全一致。
見つからない場合は、#N/A が返されます。
これが既定の設定です。6 [検索モード] 使用する検索モードを指定します。
1 – 先頭の項目から検索を実行します。
これが既定の設定です。1から3のパラメータが必須です。[]で用意された4から6の引数は任意で設定してください。
第4引数で値が無い場合の設定もできますよ。
IFERROR関数と組み合わせてVLOOKUP関数を書く必要が無くなりました。
使い方
Microsoftの公式ドキュメントをご覧ください。
XLOOKUPのように関数を入力したセル以外のセルに値を出力する機能のことをスピルと呼びます。
他の関数でも使える機能ですので興味がある方は調べて使ってみてください。
XLOOKUPの考え方や手作業で関数を用意する時の方法を説明しました。以降はVBAの解説に移ります。
コードの解説
コードの解説を進めます。ポイントは3点です。
- Rangeオブジェクト
- WorksheetFunctionオブジェクトのXLookupメソッド
- 配列の取り回し（扱い方）
順番に説明していきます。
1_Rangeオブジェクト
先に検索範囲と戻り値として使いたい範囲をRangeオブジェクトで取得しておきましょう。
理由は「XLOOKUP関数のコードを用意する際にシンプルな作りにできるから」です。
コード内では変数「srg」と「rrg」が該当します。これらをオブジェクト変数として使用していきます。
オブジェクト変数にRangeオブジェクトで指定した範囲の情報を格納する
オブジェクトを使う際は「Setステートメント」を忘れない様にしてください。
2_WorksheetFunctionオブジェクトのXLookupメソッド
ワークシート関数をVBAで使用する際はWorksheetFunctionオブジェクトを使用します。
オブジェクトブラウザーの画像を用意しておきます。
コードを書く時は引数が分かりにくいです
ワークシートで手作業をするときは引数のガイドが出ますがVBAではArg1・・・という様に引数の詳細が分かりません。
よってある程度ワークシート上で関数を理解してからコードを書くことをおすすめします。
3_配列の取り回し（扱い方）
XLOOKUP関数では引数（パラメータ）の設定次第で戻り値が複数列になります。
よって複数の値を受けられる体制を整えることが必要になります。今回は配列を使うことにします。
コード内では変数「rst」です。どんな値も受けられるようにVariant型で定義します。
ローカルウインドウで値を確認する
XLOOKUP関数の戻り値として値を受け取った後に変数の中にどんな状態で値が格納されているのかを確認します。
コードを実行すると最初はAさんの情報を取得する事になります。
画像ではAさんの年齢、出身地、血液型を配列に取得している様子が分かります。
このように変数の中に仕切りを作って必要な情報を格納できるような体制を構築しましょう。
貼り付け時に範囲を指定する必要がある
配列で取得した情報をワークシートに展開する際は範囲を使うことをおすすめします。
配列の値をワークシートに出力する際は出力する情報と同じ量のセル範囲を指定する必要があります。
今回はワークシートのC列からE列に値を用意する為3列目から5列目のセル範囲を用意するコードにしています。
まとめ
XLOOKUP関数を使って表の情報を取得してみました。
私見ですがXLOOKUP関数の方がVLOOKUP関数より引数の指定が分かりやすくなったのではないかと思っています。
よって手作業でワークシートに関数を書く際は使いやすくなった感覚があります。
しかしVBAでは難しくなったと感じています。理由は配列とセットで使うことになるからです。
初心者向けではない関数になったなという印象ですが裏を返せば「配列を学ぶ為の入り口」ができたとも言えます。
配列が苦手な方はこの関数を使って配列を勉強してみてください。
