MATCHで検索
MATCHは「範囲の中で、検索値が何番目にあるか」を返します。VLOOKUPのように値そのものを返す関数ではなく「位置」を返すので、INDEXと組み合わせると柔軟で速い検索ができます。初心者でも迷わないように、最短コードから安全テンプレ、例題まで整理します。
基本：WorksheetFunction.Matchの使い方
Sub Match_Basic_Exact()
Dim pos As Variant
'A列の中から「検索値」を完全一致(0)で探して、何番目かを取得
pos = Application.WorksheetFunction.Match("検索値", Range("A1:A100"), 0)
MsgBox "位置 = " & pos
End Sub
- 検索方法の指定:
- 0（完全一致）を基本に使う。
- 1（近似一致・昇順前提）、-1（近似一致・降順前提）。近似は並べ替え必須なので初心者は避けるのが安全。
- 返り値: 範囲の先頭を1として数える相対位置。行番号や列番号そのものではない点に注意。
例外に強い安全テンプレ（未一致で止めない）
Sub Match_Safe()
Dim pos As Variant
On Error Resume Next
pos = Application.WorksheetFunction.Match("検索値", Range("A1:A100"), 0)
On Error GoTo 0
If IsError(pos) Or IsEmpty(pos) Then
MsgBox "見つかりませんでした", vbExclamation
Else
MsgBox "位置 = " & pos
End If
End Sub
- ポイント:
- 見つからないとエラーになるため、On Errorでガード。
- 結果がエラーなら「未一致」と判断して分岐。
値を取りたいならINDEX＋MATCH（定番コンボ）
Sub IndexMatch_GetValue()
Dim pos As Variant, result As Variant
'D列でキーを探して位置を取り、F列の同じ行の値を取得
On Error Resume Next
pos = Application.WorksheetFunction.Match(Range("A2").Value, Range("D2:D1000"), 0)
On Error GoTo 0
If Not IsError(pos) Then
result = Application.WorksheetFunction.Index(Range("F2:F1000"), pos)
Range("B2").Value = result
Else
Range("B2").Value = "" '未一致時の代替
End If
End Sub
- ポイント: XLOOKUPと違い、検索列と戻り列を自由に指定でき、左方向検索も可能。VLOOKUPの制約（キーが左端）に縛られない。
行番号や列番号に変換して使う（選択・オフセット）
Sub Match_ToRowSelect()
Dim pos As Variant, rowNo As Long
On Error Resume Next
pos = Application.WorksheetFunction.Match("営業A", Range("B2:B1000"), 0)
On Error GoTo 0
If Not IsError(pos) Then
'B2が基準点なので、実際の行番号 = 2 + pos - 1
rowNo = 2 + CLng(pos) - 1
Rows(rowNo).Select
End If
End Sub
- ポイント: 返るのは「範囲内の位置」。実際のシート行番号に換算するには「範囲の開始行」を足す。
複数列・横方向でも使う（列見出しを探す）
Sub Match_ColumnHeader()
Dim pos As Variant, colNo As Long
pos = Application.WorksheetFunction.Match("金額", Range("A1:Z1"), 0)
If Not IsError(pos) Then
colNo = CLng(pos) 'A1:Z1の中で何番目の列か
Cells(2, colNo).Value = "ここに計算結果"
End If
End Sub
- ポイント: 行方向だけでなく列方向の見出し検索にも便利。
大量データは配列＋MATCHが速い（値化して一括）
Sub Match_Bulk_IndexMatch()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Dim keys As Range: Set keys = Range("A2:A50000") '探す値の列
Dim look As Range: Set look = Range("D2:D50000") '検索列
Dim ret As Range: Set ret = Range("F2:F50000") '戻り列
Dim out As Range: Set out = Range("B2:B50000") '出力列
Dim i As Long, pos As Variant
For i = 1 To keys.Rows.Count
On Error Resume Next
pos = Application.WorksheetFunction.Match(keys.Cells(i, 1).Value, look, 0)
On Error GoTo 0
If Not IsError(pos) Then
out.Cells(i, 1).Value = Application.WorksheetFunction.Index(ret, pos)
Else
out.Cells(i, 1).Value = ""
End If
Next
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
- ポイント: 行ごとにINDEX/MATCHを回すときは、画面更新・再計算・イベントを止めるだけでも体感速度が変わる。さらに高速化したいなら辞書化が有効。
MATCHの代替・使い分け（迷ったら）
- XLOOKUP（関数）を式で一括→値化: 簡単だが環境依存。古いExcelでは不可。
- Range.Find: 部分一致や大小文字の制御が可能。大量一括は不向き。
- Dictionary: 大量データは爆速。キー→行（または値）を保持してO(1)検索。
よくある落とし穴と対策
- 未一致でエラー停止する: On Errorでガードし、IsErrorで分岐する。
- 近似一致の並べ替え忘れ: 1/-1は必ず事前に並べ替え。基本は0（完全一致）を使う。
- 数値と文字列の不一致（00123 vs 123）: キーをCStrで統一して比較すると安定。
- 位置と行番号の取り違え: 返るのは範囲内の位置。開始行・開始列を加味して実行行/列へ換算する。
例題で練習
'例1：商品名の行を見つけて、同じ行の「数量」をB列へ
Sub Example_Match_Index()
Dim pos As Variant
pos = Application.WorksheetFunction.Match("商品A", Range("A2:A1000"), 0)
If Not IsError(pos) Then
Range("B2").Value = Application.WorksheetFunction.Index(Range("C2:C1000"), pos) '数量列
Else
Range("B2").Value = ""
End If
End Sub
'例2：列見出し「単価」の列番号を取得して計算結果を書き込み
Sub Example_Match_HeaderToWrite()
Dim pos As Variant, colNo As Long
pos = Application.WorksheetFunction.Match("単価", Range("A1:Z1"), 0)
If Not IsError(pos) Then
colNo = CLng(pos)
Cells(2, colNo).Value = 1234 '任意の結果を書き込む
End If
End Sub
'例3：未一致時に「N/A」を返す安全版
Sub Example_Match_SafeOutput()
Dim pos As Variant
On Error Resume Next
pos = Application.WorksheetFunction.Match("存在しない値", Range("A1:A50"), 0)
On Error GoTo 0
Range("B2").Value = IIf(IsError(pos), "N/A", pos)
End Sub