最新！Excel VBAでXLOOKUP関数を呼び出して柔軟なデータ検索を行う

Excel VBAでXLOOKUP関数の使い方をお探しですね。

1. VBAでXLOOKUP関数を使うメリットと使える環境について

XLOOKUP関数は、探したい値を指定して、それに対応する値を別の範囲から取り出してくれる検索関数です。

VLOOKUP関数と似ていますが、検索する列が結果を返す列の左側になくてもOKで、縦方向でも横方向でも使いやすいのが大きな違いです。

VBAで検索処理を書く場合、For文で1行ずつ調べていく方法もありますが、XLOOKUPを呼び出せば、Excelの検索機能をそのまま使えるので、短いコードで目的の値を取得できます。

特に便利なのは、表の列の並びが変わっても修正が少なくて済むことです。

VLOOKUPでは「範囲の中の何列目を返すか」を数字で指定するため、列を追加したり削除したりすると番号がズレてしまいます。

でもXLOOKUPなら、「どこを探すか」と「どこから返すか」を別々に指定するので、商品名をA列から探して価格をD列から返す場合でも、商品名をD列から探して商品コードをA列から返す場合でも、同じ考え方で書けます。

これは、後で修正しやすいマクロを作るうえでとても重要なポイントです。

ただし、XLOOKUPはすべてのExcelで使えるわけではありません。

Microsoft 365やExcel 2021以降など、比較的新しいバージョンでないと使えません。

古いExcelでは関数自体が存在しないので、VBAから呼び出そうとしてもエラーになってしまいます。

会社で複数のExcelバージョンが混在している場合は、XLOOKUPを使う前に対象の環境を確認して、必要に応じてVLOOKUPやINDEX＋MATCH、Dictionaryオブジェクトを使った別の方法も検討しておくと安心です。

2. WorksheetFunction.XLookupを使った基本の書き方

VBAからXLOOKUPを呼び出す代表的な方法は、`Application.WorksheetFunction.XLookup`を使う書き方です。

ワークシート上で入力する`=XLOOKUP(検索値, 検索範囲, 戻り範囲, 見つからない場合, 一致モード, 検索モード)`とほぼ同じ感覚で使えます。

たとえば、A列に商品コード、B列に商品名、C列に価格があって、E2セルの商品コードに対応する価格をF2セルに出力したい場合は、こんな風に書きます。

“`vba
Sub XLookupBasic()
Dim code As String
Dim result As Variant

code = Range(“E2”).Value

result = Application.WorksheetFunction.XLookup( _
code, _
Range(“A2:A100”), _
Range(“C2:C100”), _
“該当なし” _
)

Range(“F2”).Value = result
End Sub
“`

このコードでは、E2セルの値をA2:A100から探して、同じ行にあるC2:C100の値を返しています。

4つ目の引数に「該当なし」を指定しているので、検索値が見つからない場合でも、いきなりエラーで止まるのではなく、分かりやすい文字列を返してくれます。

実務のVBAでは、処理が途中で止まると後の集計や転記に影響するので、見つからなかったときの値をあらかじめ指定しておくのがコツです。

複数行をまとめて処理したい場合は、検索値のセルをループしながらXLOOKUPを呼び出します。

たとえば、E列に複数の商品コードが並んでいて、F列に価格を返したい場合は、最終行を取得してから1行ずつ検索していきます。

検索する件数がものすごく多い場合は配列やDictionaryを使ったほうが速くなることもありますが、数百行から数千行くらいの普通の業務表なら、読みやすいXLOOKUP呼び出しで十分実用的です。

“`vba
Sub XLookupLoop()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long

Set ws = Worksheets(“一覧”)
lastRow = ws.Cells(ws.Rows.Count, “E”).End(xlUp).Row

For i = 2 To lastRow
ws.Cells(i, “F”).Value = Application.WorksheetFunction.XLookup( _
ws.Cells(i, “E”).Value, _
ws.Range(“A2:A100”), _
ws.Range(“C2:C100”), _
“該当なし” _
)
Next i
End Sub
“`

3. あいまい検索・後ろから検索・別シート参照で柔軟にデータを探す

XLOOKUPの強みは、完全一致だけでなく、ワイルドカードを使ったあいまい検索や、後ろから探す処理にも対応できることです。

たとえば、商品名に「ノート」を含む最初の商品の価格を取得したい場合は、一致モードに`2`を指定します。

`*`は任意の文字列を表すワイルドカードで、検索値の前後に付けることで「この文字列を含む」という条件を表現できます。

“`vba
Sub XLookupWildcard()
Dim result As Variant

result = Application.WorksheetFunction.XLookup( _
“*ノート*”, _
Range(“B2:B100”), _
Range(“C2:C100”), _
“見つかりません”, _
2 _
)

Range(“E2”).Value = result
End Sub
“`

また、同じ商品コードが何度も出てくる履歴データでは、一番新しい行を取得したいケースがあります。

この場合は検索モードに`-1`を指定すると、範囲の一番下から上に向かって検索できます。

たとえば、A列に商品コード、B列に更新日、C列に最新価格が記録されている表で、同じコードの最後の価格を取りたい場合に便利です。

従来のVLOOKUPでは最初に見つかった値を返すので、最新データを取得するには並べ替えや補助列が必要でしたが、XLOOKUPなら引数の指定だけで実現できます。

別のシートを参照する場合も、普通のRange指定と同じようにワークシートを明示すればOKです。

集計シートで入力した社員番号を、社員マスタシートから検索して氏名を返す例を見てみましょう。

シートを明示せずに`Range`だけを書くと、アクティブシートに依存して思わぬ結果になることがあります。

VBAでは、検索元のシートと出力先のシートを変数に入れてから処理するのが安全です。

“`vba
Sub XLookupOtherSheet()
Dim wsOut As Worksheet
Dim wsMaster As Worksheet
Dim empId As String

Set wsOut = Worksheets(“集計”)
Set wsMaster = Worksheets(“社員マスタ”)

empId = wsOut.Range(“A2”).Value

wsOut.Range(“B2”).Value = Application.WorksheetFunction.XLookup( _
empId, _
wsMaster.Range(“A2:A500”), _
wsMaster.Range(“B2:B500”), _
“未登録” _
)
End Sub
“`

このように、VBAでXLOOKUP関数を呼び出すと、検索範囲と戻り範囲を自由に組み合わせられます。

左側の列を検索したり、あいまい検索したり、一番下から探したり、別シートを参照したりといったことが、同じ関数でできるので、マクロの構造が分かりやすくなります。

後から別の人がコードを読んだときにも、「何を探して、どこから返しているのか」がすぐ分かるのは、実務でとても大きなメリットです。

4. エラー対策と実務で使いやすくするコツ

VBAでXLOOKUPを使うときは、エラー対策も大切です。

4つ目の引数に「見つからない場合」の値を指定しておけば、検索値が存在しないケースには対応しやすくなります。

ただし、検索範囲と戻り範囲のサイズが違ったり、ExcelのバージョンがXLOOKUPに対応していなかったり、対象のシート名が間違っていたりすると、別のエラーが発生する可能性があります。

実務のマクロでは、検索結果だけでなく、処理全体が止まらない設計を意識する必要があります。

基本的な対策としては、`On Error GoTo`でエラー処理を用意して、原因が分かるメッセージを出す方法があります。

単に`On Error Resume Next`でエラーを無視すると、どこで問題が起きたのか分からなくなるので、後々困ることになります。

特に、複数の人が使うマクロでは、エラーが出たときに「検索範囲を確認してください」「XLOOKUP対応のExcelか確認してください」のように、次にどうすればいいかが分かる表示にしておくと親切です。

“`vba
Sub XLookupWithErrorHandler()
On Error GoTo ErrHandler

Range(“F2”).Value = Application.WorksheetFunction.XLookup( _
Range(“E2”).Value, _
Range(“A2:A100”), _
Range(“C2:C100”), _
“該当なし” _
)

Exit Sub

ErrHandler:
MsgBox “XLOOKUPの実行中にエラーが発生しました。

検索範囲、戻り範囲、Excelの対応状況を確認してください。

“, vbExclamation
End Sub
“`

大量のデータを扱う場合は、XLOOKUPをセルごとに何千回も呼び出すより、データを配列に読み込む方法やDictionaryを使う方法のほうが速くなることがあります。

XLOOKUPは読みやすくて実装しやすい反面、VBAから何度もワークシート関数を呼び出すと、その分だけ処理時間が増えてしまうからです。

少量から中規模の検索はXLOOKUP、数万行以上の照合や繰り返し処理は配列・Dictionaryも検討する、という使い分けが現実的でしょう。

最後に、VBAでXLOOKUPを安定して使うには、表の作り方も重要です。

検索範囲に余計な空白や表記のゆれがあると、完全一致では見つからないことがあります。

商品コードや社員番号のようなキーになる項目は、文字列と数値が混ざらないように形式を統一して、前後のスペースも取り除いておきましょう。

XLOOKUPはとても柔軟な検索関数ですが、正確な検索結果を得るには、検索元のデータをきちんと整えておくことが欠かせません。

Excel VBAでXLOOKUP関数を呼び出せるようになると、VLOOKUPでは書きにくかった検索処理を短く、分かりやすく実装できます。

完全一致の基本検索から、あいまい検索、後ろから検索、別シート参照、見つからないときの代わりの表示まで対応できるので、日々の集計マクロやマスタ参照処理にとても便利です。

対応バージョンとエラー処理に気をつけながら導入すれば、柔軟で修正しやすいデータ検索マクロが作れます。

ぜひ活用してみてください。

最新！Excel VBAでXLOOKUP関数を使って簡単にデータを探そう

1. VBAでXLOOKUP関数を使うメリットと使える環境について

2. WorksheetFunction.XLookupを使った基本の書き方

3. あいまい検索・後ろから検索・別シート参照で柔軟にデータを探す

4. エラー対策と実務で使いやすくするコツ

関連記事