【Excel】「VLOOKUP」と「INDEX/MATCH」はどちらが速い?パフォーマンスの基本

【Excel】「VLOOKUP」と「INDEX/MATCH」はどちらが速い?パフォーマンスの基本
🛡️ 超解決

VLOOKUPをINDEXとMATCHへ書き換えれば、どのブックも速くなるわけではありません。検索範囲、完全一致か近似一致か、返す列数、式の個数、Excelの版によって結果は変わります。まず同じ答えを返す条件をそろえ、そのうえで待ち時間を比べる必要があります。

この記事では商品コードから商品名と単価を取り出す例を使い、検索位置を再利用する組み方と、実際のブックで比較する手順を説明します。関数の名前だけで速さを決めず、検索の繰り返し方と保守のしやすさを一緒に見ていきましょう。

ADVERTISEMENT

1. 速度を比べる前に検索条件をそろえる

商品コードの検索なら、通常は指定したコードと同じ行を取り出します。VLOOKUPでは第4引数をFALSE、MATCHでは第3引数を0にして完全一致を指定します。この指定を省略した近似一致と、完全一致の計算時間を比べても、同じ作業の比較にはなりません。

近似一致は、段階別の料金表などに使う検索方法です。並び順などの前提があります。速度を上げる目的だけでTRUEへ変えると、存在しない商品コードに別商品の情報を返す恐れがあります。検索方法は速さより先に、必要な答えから決めてください。

Microsoftは、Microsoft 365の特定の版以降で、同じ表から複数列を探す完全一致検索の性能が改善されていると説明しています。古い測定例の比率を現在のすべての環境へ当てはめることはできません。利用中の版と、検索する行数・式の数を記録してから比較します。

※ お探しの解決策が見つからない場合は、こちらの「Excelトラブル完全解決データベース」で他のエラー原因や解決策をチェックしてみてください。

2. 同じ表でVLOOKUPとINDEX/MATCHを組む

A2:A4にP10、P20、P30、B2:B4にペン、ノート、封筒、C2:C4に120、240、180を入力します。F2に検索したいP20を入れ、単価を取り出す2つの式を比べます。

VLOOKUPでは =VLOOKUP(F2,$A$2:$C$4,3,FALSE) です。表の左端A列からP20を探し、その範囲の3列目にある240を返します。列番号の3はシート全体の列番号ではなく、指定した表の左端から数えた位置です。

INDEX/MATCHでは =INDEX($C$2:$C$4,MATCH(F2,$A$2:$A$4,0)) です。MATCHが検索範囲内の位置2を返し、INDEXが単価範囲の2番目の240を返します。位置2はワークシートの行番号2を意味しません。実際の商品P20は3行目にあります。

検索範囲A2:A4と戻り範囲C2:C4は、対応する同じ行でそろえます。C3:C5へずらすと、位置の数は正しくても別の商品を返します。まずP10、P20、P30の3件で両式が同じ答えになるか確認し、その後に範囲を広げます。

3. 複数列を返すならMATCHの結果を再利用する

商品名と単価を別々の列へ返す場合、MATCHを各式の中に繰り返して書く方法だけでなく、位置を補助セルへ出す方法があります。G2に =MATCH(F2,$A$2:$A$4,0) を置くと、この例では2です。

  • H2の商品名: =INDEX($B$2:$B$4,$G2) → ノート
  • I2の単価: =INDEX($C$2:$C$4,$G2) → 240

G列には「検索位置」と見出しを付けます。同じコードで複数の項目を取り出す際に、検索した位置を使い回せる構成です。これがINDEX/MATCHを検討する理由の一つですが、VLOOKUP側にも版による最適化があるため、必ず何倍速くなるとは言えません。

補助セルの結果が#N/Aなら、商品名と単価も取得できません。先にG2でコードの有無や型を調べられるので、調査場所を分けられる利点もあります。逆に、1列しか返さない小さな表では、補助列を増やす手間よりVLOOKUP一つで説明できる分かりやすさを優先しても構いません。

A2:A4の商品コードP10、P20、P30からP20をMATCHで探すと位置2。位置2をB2:B4の商品名とC2:C4の単価に使い、ノートと240を返す
MATCHの「2」は検索範囲の2番目という意味です。開始行をそろえた戻り範囲へ同じ位置を使います。

4. 範囲の広さと表の変更に対する違いを見る

必要なデータが数千行なら、A:Aなどの列全体を無条件に指定する前に、実際の範囲やテーブル参照を検討します。検索対象を必要な範囲に絞ることは性能改善の候補ですが、将来追加した行を範囲外にしない設計も必要です。

観点 VLOOKUP INDEX/MATCH
検索列の位置 指定表の左端 検索範囲を別に指定
戻す列 表内の列番号で指定 戻り範囲を直接指定
複数項目の取得 式を各列へ配置できる 補助セルの位置を再利用できる
速度の判断 版・範囲・式の構成をそろえて実測する

VLOOKUPの列番号3は固定した数値なので、表の途中へ列を挿入したときは戻す列を見直します。INDEXで戻り列を直接参照している場合は、通常の列挿入で参照が調整される場面がありますが、戻り列自体の削除まで防げるわけではありません。どちらも表の変更後には代表コードで答えを照合してください。

5. 実際のブックで同じ操作時間を比べる

  1. 本番ブックを複製し、比較用のVLOOKUP版とINDEX/MATCH版を作ります。
  2. 元データ、検索値、結果の列数、計算方法を同じにします。比較に不要なブックは閉じます。
  3. 先頭付近、末尾付近、見つからないコードで結果を検算します。
  4. 同じ入力変更から計算が終わるまでの時間を数回記録します。
  5. 結果の一致を保ったまま、待ち時間と式の管理のしやすさを比較します。

最初に開いたときだけの遅さと、日常の入力後の再計算は分けます。読み込みや外部データ更新が支配的なブックでは、検索式の変更だけでは全体の待ち時間が変わらない場合があります。手作業で測れないほど小さな差なら、無理に優劣を付けず、保守しやすい式を選びます。

試す変更は一度に一つにします。範囲の縮小と関数の置き換えを同時に行うと、どちらが効いたのか分かりません。検証のために計算設定を変えた場合は、終了後に元の設定へ戻して、通常の作業で結果が更新されることも確認します。

6. XLOOKUPは互換性と検索方法を確認して使う

Microsoft 365やExcel 2021以降など、XLOOKUPが使える環境なら、=XLOOKUP(F2,$A$2:$A$4,$C$2:$C$4) と書けます。検索範囲と戻り範囲を別々に指定でき、既定は完全一致です。Excel 2016や2019など、使えない版へ渡す場合は互換性を確認します。

書き方が短いことと、すべての条件で最速であることは別です。とくに検索モードを変更する場合は注意が必要です。二分探索のモードには並び順の前提があるため、未整列のコード表へ速度対策として指定しないでください。

同じコードが複数ある場合、ここで説明する通常の検索例は最初の一致を返します。請求明細のように同じコードが何行も現れる表を合計する用途とは異なります。検索前に元表が商品ごとに1行なのかを確認します。重複行の問題を関数の高速化で解決しようとしないことも、表を正しく保つために必要です。

7. 置き換えは一部から行い、戻せる式を残す

書き換えた式は、正常なコードだけでなく、未登録、空欄、数値と文字列が混ざるコードでも比較します。見つからないエラーをすべて空白へ変えると、移行で生じた問題が見えなくなるので、先にエラーの件数と原因を確認します。

同じシートの空き列に新しい結果を出し、旧結果と並べて照合すると、どの行で違うかを調べられます。検算中に表へ列を挿入して比較条件を変えないよう、補助列の場所は先に決めます。数式を大量に入れ替える前に、元の列やブックのコピーを残してください。

結果が違う、期待したほど速くない、担当者が管理できないと分かったら、保存した式へ戻します。検索範囲を適切にする、同じ検索の位置を再利用する、表の更新に対応させる、といった改善を順に評価すれば、関数名だけに頼らず自分のブックに合う方法を選べます。

📊
Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。
この記事の監修者
📈

超解決 Excel・Word研究班

企業のDX支援や業務効率化を専門とする技術者チーム。20年以上のExcel・Word運用改善実績に基づき、不具合の根本原因と最短の解決策を監修しています。ExcelとWordを使った「やりたいこと」「困っていること」「より便利な使い方」をクライアントの視点で丁寧に提供します。

🏆
超解決 Excel検定 あなたのExcel実務能力を3分で測定!【1級・2級・3級】