Excelでデータ検索や突合をしていると、「VLOOKUPでは検索列より左側の値を取得できない」「途中に列を挿入したら参照位置が変わってしまい、#REF!エラーになった」といった問題に一度は悩まされます。
従来はVLOOKUPが定番でしたが、現在のExcelでは、より柔軟で安全なXLOOKUP関数が強力な選択肢です。また、XLOOKUPを利用できない古いExcel環境では、INDEX関数とMATCH関数の組み合わせを覚えておけば、VLOOKUPより壊れにくい検索処理を作れます。
この記事では、XLOOKUP・VLOOKUP・INDEX/MATCHの違いから、Excelシート上での具体的な数式、さらにVBAのApplication.WorksheetFunction.XLookupやIndex・Matchを使った検索処理まで、実務でそのまま使える形で詳しく解説します。
「数式だけではなく、最終的にはマクロで大量のデータを自動突合したい」という方も、ぜひ最後まで確認してみてください。
【比較表】VLOOKUP vs XLOOKUP vs INDEX/MATCH の違い
まずは、代表的な3つの検索方法の違いを整理しておきましょう。
| 項目 | VLOOKUP | XLOOKUP | INDEX/MATCH |
|---|---|---|---|
| 基本的な使いやすさ | 簡単 | 非常に簡単 | やや複雑 |
| 左方向への検索 | 不可 | 可能 | 可能 |
| 列追加・削除への強さ | 弱い | 強い | 強い |
| 見つからない場合の指定 | IFERRORなどを併用 | 第4引数で直接指定可能 | IFERRORなどを併用 |
| 検索方向 | 基本的に上から下 | 先頭・末尾から選択可能 | MATCHの指定方法による |
| 対応環境 | 幅広いExcelバージョン | Microsoft 365、Excel 2021以降など | 幅広いExcelバージョン |
| おすすめ用途 | 既存ファイルの保守 | 新規作成する検索・突合処理 | 旧Excelとの互換性が必要な処理 |
新しくファイルを作成するのであれば、基本的にはXLOOKUPを第一候補にするとよいでしょう。
一方、社内にExcel 2019以前などの環境が残っており、複数バージョンで同じファイルを使用する場合には、INDEX/MATCHの互換性が大きなメリットになります。
まず押さえておきたいVLOOKUP関数の基本
XLOOKUPやINDEX/MATCHを理解する前に、Excelの検索関数として長く使われてきたVLOOKUP関数についても押さえておきましょう。
VLOOKUPは、「商品コードから商品名や単価を調べる」「社員番号から所属部署を取得する」といった、表の中から特定の値を探して、同じ行にある別の値を取得するための関数です。
Excelを使ったことがある方なら、一度は「VLOOKUP」という名前を聞いたことがあるかもしれません。現在ではXLOOKUPという新しい関数も登場していますが、既存のExcelファイルでは今でもVLOOKUPが非常によく使われています。
VLOOKUPの基本構文
VLOOKUP関数の基本構文は次のとおりです。
=VLOOKUP(検索値, 範囲, 列番号, 検索方法)4つの引数を順番に見てみましょう。
| 引数 | 意味 | 今回の例 |
|---|---|---|
| 検索値 | 何を探すのかを指定します。 | F2セルの商品コード |
| 範囲 | 検索する表全体を指定します。ただし、検索する列を一番左側に含める必要があります。 | B2:D6 |
| 列番号 | 指定した範囲の左端から数えて、何列目の値を取得するかを指定します。 | 2 |
| 検索方法 | 完全一致か近似一致かを指定します。 | FALSE＝完全一致 |
商品コードから単価を検索してみる
先ほどの商品マスタを使って考えてみましょう。
商品コードがB列、単価がC列にあり、F2セルに検索したい商品コード「P003」が入力されているとします。
このとき、VLOOKUPで単価を取得する式は次のようになります。
=VLOOKUP(F2,B2:D6,2,FALSE)初心者のうちは、この式を見ても「なぜ2なのか」「FALSEとは何なのか」が分かりにくいかもしれません。
一つずつ分解すると、次の意味になります。
- F2：F2セルに入力されている商品コードを探す
- B2:D6：B2からD6までの表を検索対象にする
- 2：指定範囲の左端であるB列から数えて2列目、つまりC列の値を返す
- FALSE：検索値と完全に一致するデータを探す
F2が「P003」の場合、VLOOKUPはまずB列から「P003」を探します。
「P003」が見つかったら、その行の指定範囲内で2列目にある値を取得します。B2:D6という範囲では、
- B列＝1列目（商品コード）
- C列＝2列目（単価）
- D列＝3列目（在庫数）
となるため、結果としてC列の単価250が返されます。
「列番号」はシート全体の列番号ではない
VLOOKUP初心者が特に間違えやすいポイントが、第3引数の列番号です。
たとえばC列はExcelシート全体では「3列目」ですが、次のVLOOKUPでは列番号に2を指定します。
=VLOOKUP(F2,B2:D6,2,FALSE)これは、列番号がExcelシート全体のA列から数えるのではなく、第2引数で指定した範囲の一番左から数えるためです。
今回の検索範囲はB2:D6なので、B列が1列目、C列が2列目、D列が3列目となります。
この仕組みはVLOOKUPを使ううえで非常に重要なので、必ず覚えておきましょう。
VLOOKUPの第4引数には、主にFALSEまたはTRUEを指定します。
| 指定 | 意味 | 数値での指定 |
|---|---|---|
| FALSE | 完全一致 | 0でも指定可能 |
| TRUE | 近似一致 | 1でも指定可能 |
つまり、次の2つの式は同じ意味です。
=VLOOKUP(F2,B2:D6,2,FALSE)
=VLOOKUP(F2,B2:D6,2,0)どちらも完全一致で検索します。
実務では0や1を使った数式もよく見かけますが、初心者のうちはFALSE＝完全一致、TRUE＝近似一致と書いたほうが意味を理解しやすいため、本記事ではFALSE・TRUEを基本として説明します。
見つからないと「#N/A」になる
VLOOKUPで指定した検索値が表の中に存在しない場合、通常は#N/Aエラーが表示されます。
たとえばF2に存在しない商品コード「P999」が入力されている状態で、次の式を実行した場合です。
=VLOOKUP(F2,B2:D6,2,FALSE)このままでは#N/Aと表示されるため、実務ではIFERROR関数と組み合わせることもよくあります。
=IFERROR(VLOOKUP(F2,B2:D6,2,FALSE),"該当なし")この式なら、商品コードが見つかった場合は単価を表示し、見つからなかった場合は「該当なし」と表示できます。
一方、後ほど紹介するXLOOKUPでは、「見つからなかった場合」の表示を関数の引数として直接指定できます。
VLOOKUPの大きな弱点は「左側を取得できない」こと
VLOOKUPで特に重要なのが、検索する列は指定範囲の一番左に置かなければならないというルールです。
今回の商品マスタは、次の並びになっています。
- A列：商品名
- B列：商品コード
- C列：単価
- D列：在庫数
商品コードがB列にあり、その右側にあるC列の単価を取得するのであれば、VLOOKUPで問題ありません。
=VLOOKUP(F2,B2:D6,2,FALSE)しかし、商品コードがあるB列から検索して、左側のA列にある商品名を取得したい場合、通常のVLOOKUPではそのまま実現できません。
VLOOKUPは基本的に、
「左側の列で検索して、その右側にある値を取得する関数」
と考えると分かりやすいでしょう。
この制限を解消してくれるのが、これから紹介するXLOOKUPやINDEX/MATCHです。
VLOOKUPは今でも覚える価値がある？
「XLOOKUPがあるなら、もうVLOOKUPは覚えなくてもよいのでは？」と思う方もいるかもしれません。
新しく検索式を作るのであれば、対応しているExcel環境ではXLOOKUPを優先するのがおすすめです。
ただし、実務では過去に作成されたExcelファイルや、別の担当者が作ったファイルを修正する機会が多くあります。その中にはVLOOKUPが大量に使われているケースも珍しくありません。
そのため、少なくとも次の形は読めるようにしておくと安心です。
=VLOOKUP(検索値, 検索する表, 取得する列番号, FALSE)まずVLOOKUPの仕組みを理解したうえでXLOOKUPを見ると、「なぜXLOOKUPの方が便利なのか」も非常に分かりやすくなります。
それでは次に、VLOOKUPの弱点を改善したXLOOKUP関数を詳しく見ていきましょう。
XLOOKUP関数の基本構文と使い方
XLOOKUP関数の基本構文は次のとおりです。
=XLOOKUP(検索値, 検索範囲, 戻り値範囲, [見つからない場合], [一致モード], [検索モード])特に重要なのは、最初の3つの引数です。
| 引数 | 意味 | 例 |
|---|---|---|
| 検索値 | 探したい値 | 商品コード「P003」 |
| 検索範囲 | 検索値を探す範囲 | B2:B6 |
| 戻り値範囲 | 一致した行から取得したい範囲 | C2:C6 |
| 見つからない場合 | 検索値がない場合に返す文字列など | “該当なし” |
| 一致モード | 完全一致・近似一致などを指定 | 0＝完全一致 |
| 検索モード | 先頭・末尾など検索方向を指定 | 1＝先頭から、-1＝末尾から |
実務サンプル表でXLOOKUPを試す
次のような商品マスタがあるとします。
| A列：商品名 | B列：商品コード | C列：単価 | D列：在庫数 |
|---|---|---|---|
| ノート | P001 | 120 | 50 |
| ボールペン | P002 | 80 | 120 |
| ファイル | P003 | 250 | 35 |
| 付箋 | P004 | 150 | 75 |
| クリアケース | P005 | 300 | 20 |
たとえばF2セルに商品コード「P003」が入力されており、その商品の単価を取得したい場合は、次の式を使用します。
=XLOOKUP(F2,B2:B6,C2:C6)検索範囲B2:B6から「P003」を探し、同じ行にあるC列の値を返すため、結果は250となります。
XLOOKUPなら左側の列も取得できる
ここがVLOOKUPとの大きな違いです。
商品コードが入っているB列を検索して、その左側にあるA列の商品名を取得したい場合でも、XLOOKUPならそのまま指定できます。
=XLOOKUP(F2,B2:B6,A2:A6)F2が「P003」であれば、結果はファイルです。
VLOOKUPでは検索対象となる列が表の一番左にある必要があります。一方XLOOKUPでは、検索範囲と戻り値範囲を独立して指定できるため、右方向・左方向を意識する必要がありません。
「見つからない場合」を第4引数で指定できる
通常、検索値が存在しなければXLOOKUPは#N/Aを返します。
しかし、XLOOKUPでは第4引数に「見つからなかった場合の値」を直接指定できます。
=XLOOKUP(F2,B2:B6,C2:C6,"該当なし")F2の商品コードがマスタに存在しない場合には、「該当なし」と表示されます。
従来のVLOOKUPでは、次のようにIFERRORを組み合わせるケースが一般的でした。
=IFERROR(VLOOKUP(F2,B2:C6,2,FALSE),"該当なし")XLOOKUPなら、エラー時の表示まで1つの関数内で完結するため、数式をシンプルに保てます。
一致モードの指定方法
XLOOKUPの第5引数では、一致方法を指定できます。
| 一致モード | 意味 |
|---|---|
| 0 | 完全一致。省略時の既定値 |
| -1 | 完全一致、なければ次に小さい値 |
| 1 | 完全一致、なければ次に大きい値 |
| 2 | ワイルドカード一致 |
商品コードや社員番号などのマスタ検索では、通常は完全一致を利用します。XLOOKUPでは完全一致が既定値であるため、VLOOKUPのように「FALSE」を毎回書く必要がない点も便利です。
末尾から検索することもできる
XLOOKUPの第6引数「検索モード」を使うと、データを後ろから検索できます。
たとえば同じ社員番号について複数の履歴があり、一番新しい記録を取得したい場合には、末尾から検索する方法が便利です。
=XLOOKUP(F2,B2:B100,D2:D100,"該当なし",0,-1)最後の-1を指定することで、検索範囲の末尾から先頭に向かって検索します。
従来のExcelでも動く！INDEX/MATCH関数の組み合わせ技
XLOOKUPは非常に便利ですが、XLOOKUPを利用できないExcel環境との互換性が必要な場合には、INDEX関数とMATCH関数の組み合わせが有力です。
1列から値を取得する基本形は次のとおりです。
=INDEX(取得したい範囲,MATCH(検索値,検索範囲,0))MATCH関数は「何番目にあるか」を調べる
MATCH関数の役割は、検索値そのものを返すことではありません。
検索対象が範囲内の何番目に存在するかを返します。
先ほどの商品マスタで、F2に「P003」が入力されている場合を考えてみましょう。
=MATCH(F2,B2:B6,0)B2:B6の中で「P003」は3番目にあるため、結果は3です。
最後の「0」は完全一致を意味します。商品コードや社員番号などを検索する場合には、原則として0を指定すると覚えておくとよいでしょう。
INDEX関数は「指定した位置の値」を取得する
INDEX関数は、指定した範囲の中から何行目の値を取得するかを指定する関数です。
=INDEX(C2:C6,3)C2:C6の3番目、つまりC4セルの値である250が返されます。
INDEXとMATCHを組み合わせる
MATCHで「何番目か」を調べ、その結果をINDEXに渡せば検索処理が完成します。
=INDEX(C2:C6,MATCH(F2,B2:B6,0))処理の流れは次のようになります。
- MATCH関数がB2:B6からF2の商品コードを検索する
- 「P003」が3番目にあるため、MATCHが3を返す
- INDEX関数がC2:C6の3番目の値を取得する
- 結果として「250」が返る
INDEX/MATCHでも左方向に検索できる
商品コードを検索して、その左側にある商品名を取得する場合は次のように書きます。
=INDEX(A2:A6,MATCH(F2,B2:B6,0))INDEXの取得範囲をA2:A6に変更するだけです。
つまりINDEX/MATCHもXLOOKUPと同様、検索する列と取得する列を別々に指定できます。
INDEX/MATCHがVLOOKUPより列追加・削除に強い理由
VLOOKUPでは、次のように「表の左から何列目を返すか」を数値で指定します。
=VLOOKUP(F2,B2:D6,2,FALSE)この「2」は、指定範囲B2:D6の2列目を返すという意味です。
そのため、表の構成変更や数式の修正方法によっては、意図した列と「何列目」という指定の対応関係が崩れ、誤った値や参照エラーの原因になります。
一方、INDEX/MATCHでは取得範囲そのものをC2:C6のように明示します。
=INDEX(C2:C6,MATCH(F2,B2:B6,0))「検索範囲」と「戻り値範囲」が分離しているため、列番号に依存しにくく、表レイアウトの変更に強いのがINDEX/MATCHのメリットです。
【VBA連携】WorksheetFunctionを使った検索・突合コード
ここからは、Excelシートの数式ではなく、VBAマクロからXLOOKUPやINDEX/MATCHを実行する方法を解説します。
サンプルでは、アクティブブックに「商品マスタ」というシートがあり、次のようにデータが配置されているものとします。
| A列 | B列 | C列 | D列 |
|---|---|---|---|
| 商品名 | 商品コード | 単価 | 在庫数 |
| ノート | P001 | 120 | 50 |
| ボールペン | P002 | 80 | 120 |
| ファイル | P003 | 250 | 35 |
WorksheetFunction.XLookupを使う完全サンプル
まずは、Application.WorksheetFunction.XLookupを使って商品コードから単価を取得するコードです。
'==================================================
' 機能：XLOOKUPを使って商品コードから単価を検索する
' 前提：「商品マスタ」シート
' B列＝商品コード、C列＝単価
'==================================================
Sub SearchPriceByXLookup()
' ワークシートを格納する変数
Dim ws As Worksheet
' 検索する商品コード
Dim productCode As String
' 検索結果の単価を格納する変数
Dim price As Variant
' 商品マスタシートを取得
Set ws = ThisWorkbook.Worksheets("商品マスタ")
' 検索したい商品コードを指定
productCode = "P003"
'--------------------------------------------------
' WorksheetFunction.XLookupは、対象が見つからないと
' VBAの実行時エラーになるため、いったんエラーを無視する
'--------------------------------------------------
On Error Resume Next
price = Application.WorksheetFunction.XLookup( _
productCode, _
ws.Range("B2:B100"), _
ws.Range("C2:C100"))
' エラーが発生したか確認する
If Err.Number <> 0 Then
' エラー情報をクリアする
Err.Clear
' 通常のエラー処理に戻す
On Error GoTo 0
' 商品が存在しなかったことを通知
MsgBox "商品コード「" & productCode & "」は見つかりませんでした。", vbExclamation
' ここで処理を終了
Exit Sub
End If
' エラー処理を通常状態に戻す
On Error GoTo 0
' 検索結果を表示
MsgBox "商品コード：" & productCode & vbCrLf & _
"単価：" & price & "円", vbInformation
End SubWorksheetFunction.XLookupの引数
VBAでも、基本的な考え方はシート関数のXLOOKUPと同じです。
Application.WorksheetFunction.XLookup(検索値, 検索範囲, 戻り値範囲)- 検索値：今回の例では変数productCodeに格納された「P003」です。
- 検索範囲：商品コードが入っているB2:B100です。
- 戻り値範囲：単価が入っているC2:C100です。
注意したいのが、WorksheetFunction経由で呼び出した関数がExcel上のエラーになると、VBAでは実行時エラーとして扱われる点です。
そのため、「検索対象が存在しない可能性がある」という実務の検索処理では、エラー処理を用意しておく必要があります。
On Error Resume Nextを使うときの重要ポイント
On Error Resume Nextを指定すると、その後にエラーが発生してもVBAが停止せず、次の処理へ進みます。
便利な一方で、広い範囲に適用してしまうと、本来気づくべきプログラムミスまで無視してしまいます。
そのため実務では、次のようにエラーが想定される処理の直前だけで有効にし、確認が終わったら必ず解除するのがおすすめです。
On Error Resume Next
' エラーになる可能性がある処理
result = Application.WorksheetFunction.XLookup(...)
If Err.Number <> 0 Then
Err.Clear
End If
On Error GoTo 0On Error Resume NextをSub全体にかけっぱなしにしないことが、エラーを見逃さないための重要なポイントです。
Application.XLookupならエラー値として判定できる
検索結果が存在しないケースをより自然に扱いたい場合には、WorksheetFunction.XLookupではなくApplication.XLookupを使う方法もあります。
Application経由で関数を実行すると、検索失敗時にVBAを即停止させるのではなく、結果をExcelのエラー値として受け取り、IsError関数で判定できるケースがあります。
検索失敗が業務上よく発生する処理では、こちらの書き方が扱いやすいことがあります。
'==================================================
' 機能：Application.XLookupを使い、
' 検索失敗をIsErrorで判定する
'==================================================
Sub SearchPriceByApplicationXLookup()
' ワークシート
Dim ws As Worksheet
' 検索する商品コード
Dim productCode As String
' 正常値・エラー値の両方を受け取れるようVariant型にする
Dim result As Variant
' 商品マスタシートを取得
Set ws = ThisWorkbook.Worksheets("商品マスタ")
' 検索値を設定
productCode = "P999"
' XLOOKUPを実行する
result = Application.XLookup( _
productCode, _
ws.Range("B2:B100"), _
ws.Range("C2:C100"))
' 結果がExcelのエラー値か判定する
If IsError(result) Then
MsgBox "商品コード「" & productCode & "」は見つかりませんでした。", vbExclamation
Else
MsgBox "単価は " & result & " 円です。", vbInformation
End If
End Subこの方法では、検索失敗を想定した処理についてOn Error Resume Nextに頼らず、戻り値そのものを判定できます。
なお、XLOOKUP自体を利用できないExcel環境では、VBAからXLOOKUPを呼び出す方法も利用できません。複数の古いExcel環境への配布を前提にする場合は、次に紹介するINDEX/MATCH方式を検討してください。
VBAでINDEX + MATCHを使う完全サンプル
次は、Application.WorksheetFunction.IndexとApplication.WorksheetFunction.Matchを組み合わせた検索処理です。
XLOOKUPが使えない環境との互換性を考える場合に有効です。
'==================================================
' 機能：INDEX + MATCHを使って
' 商品コードから商品名と単価を取得する
'==================================================
Sub SearchProductByIndexMatch()
' ワークシート
Dim ws As Worksheet
' 検索する商品コード
Dim productCode As String
' MATCHで見つけた位置
Dim matchPosition As Variant
' 取得する商品名
Dim productName As String
' 取得する単価
Dim price As Variant
' 商品マスタシートを取得
Set ws = ThisWorkbook.Worksheets("商品マスタ")
' 検索したい商品コード
productCode = "P003"
'--------------------------------------------------
' Application.Matchを使うことで、
' 見つからない場合も実行時エラーではなく
' Excelのエラー値として受け取る
'--------------------------------------------------
matchPosition = Application.Match( _
productCode, _
ws.Range("B2:B100"), _
0)
' MATCHの結果がエラーか判定する
If IsError(matchPosition) Then
MsgBox "商品コード「" & productCode & "」は見つかりませんでした。", vbExclamation
' 商品が存在しないため処理を終了する
Exit Sub
End If
'--------------------------------------------------
' INDEXで商品名を取得
' A2:A100のうち、MATCHで見つけた位置の値を返す
'--------------------------------------------------
productName = Application.WorksheetFunction.Index( _
ws.Range("A2:A100"), _
CLng(matchPosition))
'--------------------------------------------------
' INDEXで単価を取得
' C2:C100の同じ位置から値を取得する
'--------------------------------------------------
price = Application.WorksheetFunction.Index( _
ws.Range("C2:C100"), _
CLng(matchPosition))
' 検索結果をまとめて表示する
MsgBox "商品コード：" & productCode & vbCrLf & _
"商品名：" & productName & vbCrLf & _
"単価：" & price & "円", _
vbInformation
End SubVBAコードで使用した主要な関数・メソッドを詳しく解説
1. Application.WorksheetFunction.XLookup
WorksheetFunction.XLookupは、Excelシート上のXLOOKUP関数をVBAから呼び出すための方法です。
基本形は次のようになります。
Application.WorksheetFunction.XLookup(検索値, 検索範囲, 戻り値範囲)- 検索値：検索したい商品コードや社員番号などです。
- 検索範囲：検索値が格納されているRangeオブジェクトを指定します。
- 戻り値範囲：一致した位置から取得したいRangeオブジェクトを指定します。
注意点：検索結果が存在しない場合など、シート上なら#N/Aになるケースで、WorksheetFunction経由ではVBAの実行時エラーになることがあります。そのため、エラー処理をセットで考える必要があります。
2. Application.XLookup
Application.XLookupはWorksheetFunctionを付けずにXLOOKUPを実行する方法です。
検索に失敗した際、戻り値をVariant型で受け取り、IsErrorで判定する設計にしやすい点がメリットです。
If IsError(result) Then
' 見つからなかった場合の処理
End If検索対象がないこと自体が珍しくない「マスタ突合」では、エラー値を戻り値として扱う設計は非常に実用的です。
3. Application.Match
Application.Matchは、指定した値が範囲内の何番目に存在するかを検索します。
Application.Match(検索値, 検索範囲, 0)第3引数の0は完全一致を意味します。
商品コード、社員番号、伝票番号など、完全に同じ値を探したい場合は基本的に0を指定します。
また、WorksheetFunction.MatchではなくApplication.Matchを利用すると、見つからなかった場合の結果をエラー値として受け取り、IsErrorで判定できます。
4. Application.WorksheetFunction.Index
Indexは、指定範囲の中から指定位置にある値を取得します。
Application.WorksheetFunction.Index(取得範囲, 行番号)今回のコードでは、MATCHで取得した位置をIndexの行番号に渡しています。
検索範囲B2:B100で3番目に見つかったのであれば、A2:A100の3番目を取得すれば商品名、C2:C100の3番目を取得すれば単価、という仕組みです。
5. IsError関数
IsErrorは、Variant型の値がExcelのエラー値かどうかを判定する関数です。
If IsError(matchPosition) Then
' エラーだった場合の処理
End IfApplication.MatchやApplication.XLookupと組み合わせることで、「見つからなかった」という状態を安全に分岐処理できます。
6. CLng関数
CLngは、値をLong型へ変換する関数です。
Application.Matchの戻り値をVariant型で受け取った後、正常な検索結果であることを確認してからIndexへ渡す際に、次のように使用しています。
CLng(matchPosition)エラー値の状態でCLngを実行すると型変換エラーになるため、必ずIsErrorで正常値であることを確認した後に使用してください。
実務で使う際の注意点・エラー対策
検索範囲と戻り値範囲のサイズをそろえる
XLOOKUPを利用する際は、検索範囲と戻り値範囲の行数をそろえておきましょう。
たとえば、次の指定であれば問題ありません。
検索範囲：B2:B100
戻り値範囲：C2:C100一方、検索範囲がB2:B100なのに戻り値範囲がC2:C50といった設計にすると、正しい対応関係で検索できません。
商品コードの「文字列」と「数値」の違いに注意する
実務で非常によくあるのが、見た目は同じなのに検索できないケースです。
たとえば一方のデータが数値の「12345」、もう一方が文字列の「12345」になっていると、期待どおりに一致しない場合があります。
また、「00123」のように先頭ゼロを含む社員番号や商品コードは、数値に変換すると「123」になってしまいます。
コード類は基本的に計算対象ではないため、商品コード・社員番号・郵便番号などは文字列として統一しておくと安全です。
前後の空白にも注意する
CSVや外部システムから取り込んだデータでは、
- 「P003」
- 「P003 」
のように、末尾に見えないスペースが含まれていることがあります。
人間の目では同じように見えてもExcel上では別の文字列です。
こうしたケースでは、検索処理そのものではなくデータクレンジングが必要になります。TRIM関数やSUBSTITUTE関数などを使って、検索前にデータを整えることが重要です。
WorksheetFunctionとApplicationの違いを意識する
VBAからExcel関数を呼び出す場合、次の2パターンがあります。
| 書き方 | 検索失敗時の扱い | 向いているケース |
|---|---|---|
| Application.WorksheetFunction.XLookup | 実行時エラーとして扱われるケースがある | 結果が必ず存在する前提、または明示的にエラー処理する場合 |
| Application.XLookup | エラー値をVariantで受け取りやすい | 検索失敗をIsErrorで分岐したい場合 |
マスタ突合のように「対象が存在しない」という状態が正常に起こり得る処理では、Application.XLookup + IsErrorの考え方を覚えておくと便利です。
XLOOKUPを使えないPCへの配布に注意する
XLOOKUPを利用できるExcelと利用できないExcelが社内に混在している場合、作成者のPCでは正常に動いても、別のPCでは利用できない可能性があります。
不特定多数の社員へ配布するマクロでは、利用環境を確認したうえで、必要に応じてINDEX/MATCH方式へ切り替えるのが安全です。
数万件・数十万件の繰り返し検索ではDictionaryも検討する
XLOOKUPやINDEX/MATCHは非常に便利ですが、VBAのループ内でWorksheetFunctionを何万回も呼び出すような処理では、速度面で不利になることがあります。
たとえば、10万件の売上データそれぞれについて商品マスタを検索する場合、毎回ワークシートへアクセスして検索するよりも、最初にマスタをDictionary（連想配列）へ読み込み、メモリ上で検索した方が高速です。
目安として、
- シート上の通常検索：XLOOKUP
- 古いExcelとの互換性：INDEX/MATCH
- 大量データをVBAで繰り返し突合：配列 + Dictionary
と使い分けると、実務で判断しやすくなります。
まとめ
今回は、Excelの検索・突合処理で重要なXLOOKUP、INDEX/MATCH、VBAからのWorksheetFunction連携について解説しました。
ポイントを整理すると、次のとおりです。
- 新しいExcel環境では、基本的にXLOOKUPがおすすめ
- XLOOKUPなら検索列より左側・右側を問わず値を取得できる
- 第4引数を使えば「見つからない場合」を指定でき、IFERRORを省略できる
- INDEX/MATCHは古いExcelとの互換性に優れ、列番号にも依存しにくい
- VBAではWorksheetFunction.XLookupを利用できるが、検索失敗時の実行時エラーに注意する
- Application.XLookupやApplication.Match + IsErrorを使うと、検索失敗を安全に判定しやすい
- 数万件・数十万件を繰り返し突合する場合は、Dictionaryを使った高速化も検討する
検索・突合処理は、Excel実務の中でも特に利用頻度の高いテクニックです。VLOOKUPだけに頼るのではなく、XLOOKUPとINDEX/MATCHを状況に応じて使い分けられるようになると、壊れにくく保守しやすいファイルを作れるようになります。
複数条件での集計処理もあわせて身につけたい方は、【Excel】SUMIFS・COUNTIFS関数の使い方完全ガイド！も参考にしてください。
また、数万件以上の大量データをVBAで高速に突合したい場合は、【VBA】VLOOKUPより100倍速い！配列とDictionary（連想配列）で大量データを一瞬で突合する決定版コードで、WorksheetFunctionとは異なる高速化アプローチを詳しく解説しています。
検索したデータを別シートや別ブックへ自動転記する処理まで発展させたい方は、【VBA】転記処理の決定版！別シート・別ブックへデータを高速コピーする自動化コードもあわせて確認してみてください。
そして、データ突合で意外と多いのが「値は同じに見えるのに一致しない」という問題です。原因の多くは、余分な空白、不要な文字、表記ゆれなど検索前のデータ品質にあります。
次回は、TEXT・TRIM・SUBSTITUTEなどの文字列操作関数を使った「データクレンジングの極意」として、検索・集計の精度を高めるための実務テクニックを詳しく解説します。
