検索関数の速度と参照範囲:VLOOKUP・INDEX+MATCH・XLOOKUPを比較

VLOOKUP・INDEX+MATCH・XLOOKUP ―― 数百行〜数千行の日常業務では、関数の違いより保守性や対応バージョンの方が重要になることがあります。 しかし、数万行を超えるマスタと数千行の数式を組み合わせるような場面では、参照範囲や検索方式の違いが計算時間に表れることがあります。

この記事では、各検索関数で「どの範囲を指定するのか」を軸に、速度特性と参照範囲の違いを解説します。 さらに、ソート済みデータに対するバイナリ検索(二分探索)の負荷を抑える仕組みと、安全に運用するためのSORT関数との組み合わせ方を詳しく掘り下げます。

検証範囲と表現について:
計算速度はデータ量やブック構成によって変わります。本記事の図・順位・数値は、掲載条件での比較と仕組みを理解するための目安です。重い場合は、実際のブックのコピーで比較してください。
🔴 この記事で最も伝えたいこと:「速度が速い関数=最適な関数」ではない
参照したベンチマークではVLOOKUPが速い条件もありますが、別のデータ構造やメモリが逼迫した環境で同じ結果になるとは限りません。 32ビットExcelでは利用可能なアドレス空間が小さいため、大きなブックや複数のアドインを同時に使うと、メモリ不足が計算時間のボトルネックになることがあります。 速度・参照範囲・保守性を総合的に判断することが重要です。

【1】数式で指定する参照範囲の違い

検索関数のパフォーマンスには、指定する参照範囲、検索方式、数式数、キャッシュ、Excelの内部最適化など複数の要因が関係します。以下の図は、数式で明示的に指定している範囲の違いを理解するための概念図であり、Excel内部の実際のメモリ配置を断定するものではありません。

各関数で数式上指定する範囲の比較
▼ INDEX + MATCH ― 検索列と戻り列を個別指定
ABCDE
1社員ID氏名部署役職給与
2 1001 田中営業部主任 350,000
:検索列直接指定しない直接指定しない直接指定しない取得列
=INDEX(E:E, MATCH(検索値, A:A, 0))

数式上は、MATCHの検索列をA列、INDEXの戻り列をE列として別々に指定します。実際のメモリ読み込み方法はExcelの内部実装に依存します。

▼ VLOOKUP ― 検索列から取得列までを連続範囲で指定
ABCDE
1社員ID氏名部署役職給与
2 1001 田中営業部主任 350,000
:数式ではA列〜E列を連続範囲として指定する
=VLOOKUP(検索値, A:E, 5, FALSE)

table_arrayとしてA:Eを指定します。中間列を含む広い範囲指定が計算負荷へ与える影響は、Excelのバージョンとブック構成によって異なります。

▼ XLOOKUP ― 検索列と取得列を独立指定(INDEX+MATCHと同等)
ABCDE
2 1001 田中営業部主任 350,000
:検索配列直接指定しない直接指定しない直接指定しない戻り配列
=XLOOKUP(検索値, A:A, E:E)

数式上は検索配列(A列)と戻り配列(E列)を個別に指定できます。内部のメモリ処理はExcelの実装に依存します。

数式上の比較この違いから実メモリ量は計算できませんINDEX+MATCHとXLOOKUPは検索列と戻り列を個別に指定でき、VLOOKUPは検索列から戻り列までを table_array として指定します。Excel内部の保持方法やメモリ使用量は公開されていないため、列数の比をメモリ比として扱わないでください。
32ビットExcelのメモリ上限問題 ― 関数単体の比較が当てはまらない場合

32ビット版Excelの仮想アドレス空間は約2GBです(LAA有効時でも最大約3〜4GB)。この空間はExcel本体・開いているブック・アドインで共有されます。

32ビットExcelのメモリ使用イメージ
利用可能メモリはExcelの版、OS、アドイン、他のブックで変動
Excel本体
アドイン
ブック・数式・
データ
残り僅か
↓ 複数ブック・大量の数式・アドイン等で使用量が増えると…
Excel本体
アドイン
利用可能メモリが減少
→ ページングの可能性

広い参照範囲と大量の数式を組み合わせたブックでは、関数の種類にかかわらずメモリ使用量が増えることがあります。利用可能なメモリが不足するとページファイルへの読み書きが増え、再計算や画面応答が大幅に遅くなる場合があります。

🔴 ベンチマーク順位が実際のブックに当てはまらない理由
ベンチマークテストは通常、十分なメモリがある環境で行われます。しかし32ビットExcelの実務環境では:

INDEX+MATCH / XLOOKUP:検索範囲と戻り範囲を別々に指定できるため、必要な範囲を明示しやすい構造です。

VLOOKUP:table_arrayとして連続範囲を指定するため、必要以上に広い範囲を指定しないことが重要です。

どの方式でも、列全体参照、大量の数式、他のブックやアドインが重なるとメモリ不足になる可能性があります。関数名だけでスワップ発生の有無や速度順位を判断せず、タスクマネージャーと再計算時間で確認してください。
✅ 対処法
・大規模ブックでは64ビット版Excelへの移行を検討する(アドイン等の互換性確認が必要)
・VLOOKUPの table_array を「業務上必要な最大列範囲」に統一しつつ、不要に広くしすぎない
・VLOOKUP、INDEX+MATCH、XLOOKUPを同じ結果・同じデータで比較する
・列全体参照、限定範囲、テーブル参照を対象関数に合わせて比較する
・テーブル(構造化参照)を活用する(実データ範囲だけを自動参照)
参照範囲のキャッシュと「やりがちなアンチパターン」

Excelの計算エンジンは依存関係や参照範囲に応じた最適化を行いますが、関数ごとのキャッシュ作成単位や実メモリ配置の詳細は公開されていません。同一範囲と異なる範囲で速度差が出ることはありますが、以下は内部実装の断定ではなく、比較テストを設計するための例として見てください。

たとえば、1,000行の数式がすべて =VLOOKUP(…, A:E, 5, FALSE) と同じ table_array(A:E)を参照している場合、 同一範囲を繰り返し参照する形になります。範囲を統一した場合と必要列だけに分けた場合のどちらが速いかは、取得列数やExcelの版で変わるため計測が必要です。

VLOOKUPでの典型例

比較案A:取得列ごとに範囲を変える
B列(氏名)を取得:
=VLOOKUP(X2, A:B, 2, FALSE)

D列(役職)を取得:
=VLOOKUP(X2, A:D, 4, FALSE)

E列(給与)を取得:
=VLOOKUP(X2, A:E, 5, FALSE)

table_arrayは3種類になります。
必要列は絞れますが、異なる参照範囲が増えます。
比較案B:table_arrayを統一する
B列(氏名)を取得:
=VLOOKUP(X2, A:E, 2, FALSE)

D列(役職)を取得:
=VLOOKUP(X2, A:E, 4, FALSE)

E列(給与)を取得:
=VLOOKUP(X2, A:E, 5, FALSE)

table_arrayはすべてA:Eで統一されます。
数式の管理はしやすくなりますが、各式の参照範囲は広がります。
⚠️ どちらが有利かは実測する
参照範囲を狭めると各式の対象セルは減りますが、取得列ごとに数式が異なります。範囲を統一すると管理はしやすくなりますが、各式の対象範囲は広がります。対象ブックで両方を比較し、再計算時間と保守性で選んでください。
💡 MATCH結果を使い回す方法
同じ検索を複数列で繰り返す場合は、MATCHの結果を1セルに保存して複数のINDEXから参照すると、重複計算を避けやすくなります。
さらに、Microsoft公式ドキュメントでは「MATCHの結果を1つのセルに格納し、複数のINDEXで使い回す」テクニックが紹介されています。
H2 =MATCH(X2, A:A, 0) ← MATCHの結果を保存
I2 =INDEX(B:B, H2) ← 氏名を取得
J2 =INDEX(D:D, H2) ← 役職を取得
K2 =INDEX(E:E, H2) ← 給与を取得

COUNTIF / SUMIF は「計算目的」を先に揃える

🔴 行ごとに広がるCOUNTIFは負荷を確認
COUNTIFやSUMIFで開始位置を固定し、終了位置だけを行ごとに広げる式は、後ろの行ほど評価範囲が大きくなります。大量行では再計算時間を確認してください。
比較例A:ここまでの出現回数
A列の重複を「自分より上の行だけ」でカウントしようとして:

B2:=COUNTIF($A$1:$A2, A2)
B3:=COUNTIF($A$1:$A3, A3)
B4:=COUNTIF($A$1:$A4, A4)
︙
B10000:=COUNTIF($A$1:$A10000, A10000)

範囲が行ごとに広がるため、後ろの行ほど評価対象が増える。
データ量や他の数式によっては、再計算時間が大きく伸びることがあります。
別目的の例B:列全体の総件数
列全体で何件あるかが目的の場合:

B2:=COUNTIF($A:$A, A2)
B3:=COUNTIF($A:$A, A3)
B4:=COUNTIF($A:$A, A4)
︙

行ごとの累計ではなく、列全体の総件数を返します。
Microsoft 365の対象版ではCOUNTIF系の同一検索範囲に関する改善が公式に説明されていますが、列全体参照が常に最速という意味ではありません。限定範囲またはテーブル参照も比較してください。
📝 比較時の確認項目
・参照範囲を必要以上に広くしていないか。
・行ごとに参照範囲が拡張していないか。
・テーブル参照、固定範囲、列全体参照のどれが対象ブックで速いか。
・数式変更後も結果件数・合計・エラー件数が一致するか。

【2】掲載条件での速度比較 ― ベンチマークの読み方

「XLOOKUP は最新だから常に軽い」とは限らず、ベンチマーク結果は条件によって変わります。 ただし、ベンチマーク上の速度だけで関数を選ぶのは危険です。対応バージョン、参照範囲、保守性と実測結果を合わせて判断します。

Microsoft公式対象版で完全一致検索を改善
Microsoft公式ドキュメントによれば、Microsoft 365 version 1809以降で、 未ソートデータに対するVLOOKUP・HLOOKUP・MATCHの完全一致検索が 「同じテーブル範囲から複数列を検索する場合に、従来よりはるかに高速」になったと明記されています。

公式資料は検索列の内部キャッシュインデックスを再利用すると説明しています。効果は対象バージョン、検索列、数式構成で変わるため、買い切り版を含むすべてのExcelへ同じ順位を当てはめないでください。
INDEX+MATCH / XLOOKUP の最大の実務メリット ― 列の挿入・削除で壊れない

速度やメモリの議論は大規模データでのみ問題になりますが、 「列の挿入・削除で数式が壊れない」という保守性の利点は、データ規模に関係なく常に効いてきます。 むしろ日常業務では、速度差よりもこちらの方がはるかに重要です。

❌ VLOOKUPの列番号問題
=VLOOKUP(A2, マスタ!A:E, 5, FALSE)

「5列目の給与」を取得。しかし、もし誰かがマスタ表のB列とC列の間に新しい列を挿入すると、 給与は6列目に移動するのに、数式の「5」は自動更新されない。

結果として、役職データが給与として返される ―― エラーにならないため間違いに気づきにくい。
✅ INDEX+MATCH / XLOOKUP は列を直接指定
=INDEX(E:E, MATCH(A2, マスタ!A:A, 0))
=XLOOKUP(A2, マスタ!A:A, マスタ!E:E)

いずれも取得する列を直接セル参照で指定しているため、 列が挿入・削除されてもExcelが参照先を自動追従する。
列の挿入時に参照先が追従しやすく、数値の列番号を直接指定するVLOOKUPより列ずれを防ぎやすい。
⚠️ VLOOKUPの列番号ずれに注意
本人が列を挿入した場合は気づけますが、共有ブックで他の人が列を追加した場合や、 数ヶ月後にマスタ表のレイアウトを変更した場合に発覚することが多く、 レイアウト変更後も式がエラーにならず、誤った列の値を返す可能性があります。
INDEX+MATCHやXLOOKUPへの移行は、この種の事故を減らす対策になります。ただし参照列の削除や数式の編集ミスは別途確認が必要です。
📝 INDEX+MATCHが「Excel 2016以降対応の王道」と呼ばれる理由
INDEX+MATCHは Excel 2007 以前から使える組み合わせで、XLOOKUPが使えない Excel 2019 以前の環境でも 「列番号を数えなくてよい」「左方向にも検索できる」「MATCH結果を再利用できる」という特徴があります。 XLOOKUPが使える環境であればXLOOKUPの方が数式が短く可読性が高いですが、 旧バージョンを含む共有先でも使いやすい点が実務上の利点です。
単一列の完全一致検索 ― 選択時の比較(固定順位ではありません)
関数参照の指定速度の読み方主な選択理由
VLOOKUP 検索列から戻り列までを連続範囲で指定 Microsoft資料の掲載条件ではINDEX+MATCHより約5%速く、メモリ使用量も少ないと説明。固定順位ではない 旧版を含む互換性、式の単純さ。列番号の保守性に注意
INDEX + MATCH 検索列と戻り列を個別に指定 MATCH結果をセルへ保存し、複数のINDEXで再利用すると重複検索を減らせる 旧版対応、左右どちらの列も返せる、列追加時の保守性
XLOOKUP 検索配列と戻り配列を個別に指定 掲載ベンチマークでは差が出るが、Excelの更新・データ形状で変わるため実測 式が読みやすい、複数列返却、検索モードを指定できる
LOOKUP 検索ベクトルと結果ベクトルを指定 並べ替えを前提とする近似一致。完全一致との直接比較には使わない 要件が近似一致に合う場合のみ
なぜ XLOOKUP は単純な検索で VLOOKUP より遅い結果になったのか(推測)

以下はMicrosoftが公開している内部仕様にもとづく説明ではなく、推測です。XLOOKUPは「完全一致がデフォルト」「見つからないときの値の引数」「複数列返却のスピル対応」「6つの引数」など、VLOOKUPにはない機能を持っています。 「1つの列から1つの値を取るだけ」という最もシンプルなケースでは、こうした機能のための処理が差になっている可能性があります。

VLOOKUPは引数が4つだけで構造が極めて単純なため、Excelの内部計算エンジンによる最適化が効きやすいと考えられています。 LOOKUP関数(引数2〜3個・ソート済みデータ前提)が軽い結果になった検証例もあります。

💡 INDEX+MATCHのオーバーヘッドについて
INDEX+MATCHは「2つの関数を組み合わせている」ため、関数呼び出しのオーバーヘッドが理論上2回分発生します。 関数呼び出し回数だけで実際の速度差を説明することはできず、Excelの内部最適化にも依存します。参照資料の差は特定条件での結果として扱い、保守性や柔軟性も含めて選択してください。
XLOOKUP が速度面で有利になるケース

XLOOKUPが「遅い」のは単一列の単純な完全一致検索の場合です。以下のケースでは逆にXLOOKUPが優位に立ちます。

✅ 複数列の一括返却
INDEX+MATCHで3列を取得するにはMATCHを3回実行する必要がある(MATCHの結果をセルに保存して使い回す手もあるが、セルを1つ消費する)。 XLOOKUPは1回の検索で3列をスピル返却できるため、取得列が増えるほどXLOOKUPの効率が勝る。
✅ バイナリ検索モード ― XLOOKUP最大の速度アドバンテージ
ソート済みデータに対してXLOOKUPの第6引数=2を使うと、完全一致のまま二分探索が可能。 VLOOKUPのバイナリ検索(第4引数=TRUE)は近似一致になってしまうため、この組み合わせはXLOOKUP独自の強み。

SORT関数(Excel 2021以降)の登場により、並べ替え済みであることを数式で保てるようになり、 バイナリ検索の前提条件(昇順ソート済み)を満たしやすくなりました。 「SORT関数 + XLOOKUPバイナリ検索」は有力な組み合わせですが、次の条件があります。
・元データが事前に並べ替え済みであること(数式の中で SORT を毎回再計算させると、そのたびに並べ替えのコストがかかる)
・小〜中規模のデータでは、線形検索との差は小さいか、逆に遅くなることがある
・戻り値の列も検索列と同じ順序に並べ替える必要がある
ダブルVLOOKUPテクニックはもう不要 ― XLOOKUPバイナリ検索で代わりに使える場面がある

XLOOKUPが登場する以前、VLOOKUPで「高速かつ安全な完全一致」を実現するためにはダブルVLOOKUPという裏技が広く使われていました。

/* 旧来のダブルVLOOKUPテクニック */
=IF(VLOOKUP(A2, D:E, 1, TRUE)=A2, VLOOKUP(A2, D:E, 2, TRUE), "未登録")

1本目のVLOOKUP:近似一致(TRUE)で高速にバイナリ検索し、D列(検索列自身)を返す。
IF文:返ってきた値がA2と一致するか検証(=完全一致かチェック)。
2本目のVLOOKUP:一致していれば再度近似一致で目的の列(E列)を取得。不一致なら"未登録"。

⚠️ なぜこんな回りくどいことをしていたのか
VLOOKUPの第4引数 TRUE(近似一致)はバイナリ検索で高速ですが、 探している値がデータに存在しなくても「最も近い値」を勝手に返してしまうという致命的な仕様があります。

そのため「まず近似一致で検索 → 結果が本当に一致しているか IF で検算」という2段構えが必要でした。 1万行のマスタで、通常の完全一致(FALSE)より計算時間が短くなった検証例もあります(FastExcel / Decision Models)。倍率はデータと環境によって変わります。

しかし、この方法には多くの問題がありました:
・数式が長く複雑で、VLOOKUPが2回書かれるため可読性が低い
・検索値がデータの最小値より小さい場合にエラーが出るため、追加のIFERROR処理が必要
・ソートされていなければ誤った結果を返すリスクがある
・後任者が仕組みを理解できず属人化しやすい
✅ XLOOKUPバイナリ検索なら、これが1行で済む
=XLOOKUP(A2, D:D, E:E, "未登録", 0, 2)
・完全一致(第5引数=0)とバイナリ検索(第6引数=2)を同時指定 ― ダブルVLOOKUPの2段構えが不要
・見つからなければ第4引数("未登録")を返す ― IFもIFERRORも不要
・数式が短く、意図が明確で保守しやすい

さらにSORT関数と組み合わせれば、ソート前提の心配を減らせます。 ダブルVLOOKUPテクニックを覚える必要は、もうありません。
💬 補足:近似一致(TRUE)自体を「なんとなく怖い」と避けてきた方へ
VLOOKUPの近似一致(TRUE)が高速なのは理屈としてわかっていても、 「万が一ソートが崩れていたら」「意図しない値が返ったら」という不安から、あえて使わずに完全一致(FALSE)で通してきたという方は非常に多いはずです。 実際、近似一致の「勝手に最近傍の値を返す」仕様はサイレントに間違った結果を返すため、 業務上のリスクを考えれば慎重な判断として正しいものでした。

XLOOKUPのバイナリ検索は「完全一致」が前提であり、見つからなければ明示的に第4引数の値を返します。 このように条件が合えば速度と安全性を両立しやすいことが、VLOOKUPの近似一致が抱えていた不安を減らす点であり、 XLOOKUPの速度面における最大の進化と言えます。

【3】ベンチマーク参考値 ― 海外検証サイトのデータから

以下は海外の著名なExcel検証サイト(Ablebits.com)が公開しているベンチマーク結果です。 環境依存が大きいため、絶対値ではなく関数間の相対的な速度差を読み取ることが重要です。 計測は Microsoft 365 の Excel 32ビット(バージョン2011・当時のベータチャネル)で行われ、64ビット版でも追試されています。ビット数によって結果に差があり、たとえば OFFSET+MATCH(範囲指定)は32ビットで約2.9秒、64ビットで約7.5秒でした。 計測は2020年頃(当時のベータ版を含む)のものなので、現在のExcelでは結果が変わる可能性があります。

出典:https://www.ablebits.com/office-addins-blog/vlookup-excel-fastest-formula/

Ablebits.com のベンチマーク(50万行メイン×500行マスタ・32bit Excel環境)
関数範囲指定列全体参照テーブル参照動的配列
VLOOKUP 6.6秒14.2秒2.3秒1.8秒
INDEX+MATCH 8.9秒17.7秒2.6秒4.4秒
XLOOKUP 11.2秒24.1秒3.3秒7.3秒
OFFSET+MATCH 2.9秒3.5秒2.7秒未計測
注目ポイント:
・この比較例では、実データ範囲に追従するテーブル参照が軽い結果でした。
・32ビット版の範囲指定では、OFFSET+MATCH(約2.9秒)がいちばん速い結果でした。ただしOFFSETは揮発性関数のため、ブックの別の場所を編集しても再計算されます。
・列全体参照(D:D)は、必要範囲だけを指定する方法より処理対象が増えやすいため、大量の数式では注意が必要です。
⚠️ このベンチマークの限界
マスタが500行と比較的小さいため、VLOOKUPの table_array が広くてもメモリ上限に達しにくい条件です。 実務でマスタが数万行〜数十万行になる場合は、関数ごとの速度だけでなく、参照範囲、数式数、Excelのビット数、ブック全体のメモリ使用量も確認してください。 ベンチマーク結果は掲載されたPC・Excel版・データ条件での参考値として捉えてください。

【4】通常規模では保守性を先に考える

ここまで詳細に速度差を解説しましたが、小〜中規模のブックでは関数差を体感しにくい場合があります。

マスタ数千行・数式数百行程度では、VLOOKUP・INDEX+MATCH・XLOOKUPの速度差を体感しにくい環境が多いと考えられます。 関数選択では、掲載ベンチマークの順位だけでなく、保守性・可読性・対応バージョンを優先し、重さが問題になった時点で計測します。

✅ 日常業務では保守性を重視
速度差を体感しにくいデータ規模では、保守性は重要な判断基準です。 VLOOKUPは列の挿入・削除で列番号がずれる「サイレントバグ」のリスクがあるため、 INDEX+MATCHまたはXLOOKUPは列番号ずれを避けやすい候補ですが、共有先のExcel版と担当者の理解度も確認します。 次のような条件では、速度を個別に計測する価値があります。

IFERROR が不要になる点も、速度ではなく保守性・読みやすさの利点です。XLOOKUPは第4引数で「見つからないときの値」を指定できるため、IFERRORで包む必要がなく、数式が短くなります。 なお、二重計算になるのは旧来の IF(ISERROR(VLOOKUP(…)), "", VLOOKUP(…)) という書き方で(同じVLOOKUPを2回計算する)、IFERROR(VLOOKUP(…), "") ではVLOOKUPの計算は1回です。

⚡ 速度が問題になるのはこんな場面
・マスタが10万行を超える大規模データ
・数式が数千行以上にコピーされている
・32ビットExcel環境で利用可能メモリが少ない
・ブック内に複数のシートで大量の検索関数が使われている
・INDIRECT や VOLATILE 関数との組み合わせで再計算が頻発する

これらは目安です。関数の変更やバイナリ検索の導入前後で、結果一致と再計算時間を確認してください。

【5】バイナリ検索(二分探索)はなぜ負荷を抑えやすいのか

バイナリ検索とは、ソート済みのデータを「半分に分割しながら」目的の値を絞り込むアルゴリズムです。 電話帳で名前を探すとき、最初のページから1ページずつめくるのではなく、真ん中あたりを開いて「探している名前はこれより前か後か」で判断する ―― これがバイナリ検索の発想です。

線形検索(デフォルト)の動き ― 上から順に1つずつ確認
1行目
確認
→
2行目
確認
→
3行目
確認
→
…
→
N行目
発見!

最悪の場合、全N行を1つずつ確認する必要がある。計算量:O(N)

バイナリ検索の動き ― 半分に分割しながら絞り込む

例:1〜16の昇順データから「11」を探す場合

1
中央値=8 → 11 > 8 なので 後半に絞る
1234 5678 9101112 13141516
2
中央値=12 → 11 < 12 なので 前半に絞る
9101112 13141516
3
中央値=10 → 11 > 10 なので 後半に絞る
9101112
4
中央値=11 → 一致! 発見!
11

16個のデータからたった 4回 の比較で発見。計算量:O(log₂ N)

データ件数ごとの最大比較回数
データ件数 (N)線形検索
(最悪 N 回)
バイナリ検索
(最悪 log₂N 回)
速度差
100100回7回探索回数 約1/14
1,0001,000回10回探索回数 約1/100
10,00010,000回14回探索回数 約1/714
100,000100,000回17回探索回数 約1/5,882
1,000,0001,000,000回20回探索回数 約1/50,000
100万行(シートの上限は1,048,576行)のデータでも、バイナリ検索なら最大たった 20回 の比較で目的の値を見つけ出せます。

【6】バイナリ検索の設定方法 ― XLOOKUPの第6引数

XLOOKUPでバイナリ検索を使うには、第5引数(一致モード)を 0(完全一致)、第6引数(検索モード)を 2(昇順バイナリ)または -2(降順バイナリ)に設定します。

=XLOOKUP(A2, D:D, E:E, "未登録", 0, 2)

第5引数 0:完全一致 / 第6引数 2:バイナリ検索(昇順ソート済みデータ向け)。D列が昇順に並んでいることが前提条件です。

✅ XLOOKUPバイナリ検索の最大の強み ― 「完全一致 × 二分探索」を1つの式で指定できる
VLOOKUPにも高速検索モードがありますが、それは第4引数を TRUE(近似一致)にした場合のみです。 VLOOKUPでは「完全一致」と「バイナリ検索」を同時に使うことができません。 見つからなかった場合に勝手に最近傍の値を返してしまう危険があります。

このためVLOOKUPで「高速かつ安全な完全一致」を実現するには、 「近似一致で検索→結果をIFで検算」というダブルVLOOKUP(2回のVLOOKUPを組み合わせるテクニック)が必要でした。 数式が長く複雑になり、属人化の原因にもなっていました。

XLOOKUPは「完全一致(0)× バイナリ検索(2)」を引数指定だけで両立できます(位置を返す XMATCH も同じ指定ができます)。 見つからなければ第4引数の値を返すため、IFもIFERRORもダブルVLOOKUPも一切不要。
さらに、SORT関数の登場により並べ替え済みであることを数式で保てるようになり、 バイナリ検索の前提条件(昇順ソート済み)を満たしやすくなりました。 これがXLOOKUPの速度面における最大の進化です。
🔴 バイナリ検索の前提条件:データがソート済みであること
データがソートされていない場合、大小の判断が正しく機能せず、存在するはずの値が「見つからない」と誤判定されます。 エラーにはならず間違った値や "未登録" が返るため、サイレントな誤りが最も危険です。
🔴 見落としがちな落とし穴:空白セルやデータ型の混在
バイナリ検索では、検索モードに合った並べ替え順を維持する必要があります。空白、数値、文字列、「数値に見える文字列」が混在すると、想定した並べ替え順にならない場合があります。

そのため、空白を除外する条件を明示し、データ型を揃えたうえで、先頭・中央・末尾・未登録値を含むテストを行ってください。SORTだけでは空白行の除外を保証しないため、必要ならFILTER等で空白除外を明示します。
📝 なぜ空白セルが混入するのか ― 実務でよくあるパターン
・マスタ表から行を削除したが、セルの内容だけ消して行自体は残した(見た目は空行だがセル範囲には含まれる)
・データの途中にメモや区切り用の空行を入れている
・CSVインポート時に末尾に空行が紛れ込んだ
・列全体参照(A:A)を使っているため、データ末尾より下の空白セルもすべて検索範囲に含まれる

いずれも見た目では気づきにくいため、バイナリ検索を使う場合は「ソート済みの中間テーブルをSORT関数で作り、それを参照する」方法が最も安全です。

【7】SORT / SORTBY 関数でソートを保証する ― バイナリ検索を安全に運用する方法

バイナリ検索の最大のリスクは「手動でソートするのを忘れた」「誰かがデータを追加してソート順が崩れた」「空白行が紛れ込んだ」というヒューマンエラーです。 Excel 2021以降では SORT / SORTBY 関数でこのリスクを根本的に排除できます。

パターン①:SORT で並べ直した配列に XLOOKUP を適用(小規模データ向け)
=XLOOKUP(検索値, SORT(A2:A1000), SORTBY(B2:B1000, A2:A1000), "未登録", 0, 2)

元データのソート順に関わらず、常に正しくバイナリ検索できる。

⚠️ 注意:この書き方では、XLOOKUPの数式1つごとにSORT/SORTBYが実行されます。 数式が数十行程度なら問題ありませんが、数百行以上にコピーする場合はパターン③を使ってください。
パターン②:LET + SORTBY + CHOOSECOLS で計算コストを最小化(中規模データ向け)
=LET( sorted, SORTBY(A2:C1000, A2:A1000, 1), XLOOKUP(検索値, CHOOSECOLS(sorted,1), CHOOSECOLS(sorted,2,3), "未登録", 0, 2) )

LET で一度だけソート結果を計算して再利用。計算コストを最小化。ただし数式ごとにLET内のSORTBYは実行される。

パターン③:大量データ ― 作業シートで「1回だけ」SORT ★最も実用的

大量の検索数式がある場合、パターン①②のように各数式内でSORT/SORTBYを呼ぶと、 数式の数だけSORT処理が繰り返されてしまいます。 作業シートにSORT結果をスピルで1回だけ出力し、各XLOOKUPはその結果を参照するのが最も効率的です。

/* 作業シートのA1セルに入力(1回だけ) */
=SORTBY(マスタ!A2:C500000, マスタ!A2:A500000, 1)
/* 各シートのXLOOKUP ― ソート済みスピル範囲を参照 */
=XLOOKUP(A2, 作業!A1#, CHOOSECOLS(作業!A1#, 2, 3), "未登録", 0, 2)
✅ この方法が最も実用的な3つの理由
① SORT処理は1回だけ:XLOOKUPが1,000行あっても、SORTBYの計算は作業シートの更新時の1回のみ。 パターン①のようにXLOOKUP内にSORTを書くと、1,000回SOURTが走ることになる。
② 空白行が自動的に除去される:元のマスタデータに空白行があっても、SORTBY関数の出力には空白行が含まれないため、 バイナリ検索の「空白行で誤判定」問題が自動的に解消される。
③ データ型の不一致も目視確認しやすい:作業シートにソート結果が展開されるため、 「数値のはずが文字列になっている行」などの異常を発見しやすい。

SORTBYの計算は作業シートの更新時の1回だけ。XLOOKUPは整列済み・空白除去済みのデータを読むだけなので高速かつ安全。

【8】最終まとめ ― 速度・メモリ・保守性の総合判断

🔴 繰り返しになりますが:「ベンチマーク上の順位 ≠ 最適な関数」
32ビットExcel環境では、ブック全体のメモリ使用量が利用可能範囲に近づくと、関数単体のベンチマーク順位よりメモリ不足の影響が大きくなる場合があります。 速度だけでなく、参照範囲・環境(32/64bit)・対応バージョン・保守性を総合的に判断してください。
関数選択の判断フロー
① 対応バージョン
→
共有先で使える関数に絞る
② 必要な機能
→
複数列返却・左右検索・近似一致を確認
③ 保守性
→
列追加・引き継ぎ時の読みやすさを確認
④ ソート保証
→
保証できる場合のみバイナリ検索を比較
⑤ 実測
→
同じ結果・同じ範囲で再計算時間を比較
総合比較まとめ
項目VLOOKUPINDEX+MATCHXLOOKUP
(線形)
XLOOKUP
(バイナリ)
速度 掲載ベンチマークは条件付きの参考値。対象ブックで実測
数式上の参照指定 A:Eのような連続範囲 検索列と戻り列を個別指定 検索配列と戻り配列を個別指定 線形XLOOKUPと同じ
複数列の返却 通常は列ごとに式 MATCH結果を再利用可能 戻り配列を複数列にできる 戻り配列を複数列にできる
公式に説明された
検索キャッシュ改善
Microsoft 365 version 1809以降の完全一致が対象 MATCHは対象。INDEXを含む方式全体の保証ではない 本記事では一般化しない 本記事では一般化しない
バイナリ検索 近似一致は可能だが要件が異なる 従来MATCHの近似一致は可能 デフォルトは線形 検索モード2/-2。並べ替え必須
列ずれ耐性 ❌ 列番号がズレる
サイレントバグの元
✅ 列を直接参照
自動追従する
✅ 列を直接参照
自動追従する
✅ 列を直接参照
自動追従する
バイナリ検索の
空白・型混在耐性
近似一致は並べ替えと要件確認が必要 ― (バイナリなし) ― (線形のみ) ⚠ 空白・型混在を含め、並べ替え順と結果を検証
対応バージョン 全ver 全ver 2021以降 2021以降
✅ 結論:
・日常業務(〜数千行):速度差を体感しにくい場合が多いため、保守性と対応バージョンを優先。XLOOKUP(対応環境)またはINDEX+MATCH(旧環境を含む)を候補にします。
・大量データ(1万行超)+ ソート可能:XLOOKUPのバイナリ検索とSORTの組み合わせは有力候補です。ソート前提を満たしたうえで、通常検索との時間差と結果一致を検証してください。
・同じマスタから複数列を取得:XLOOKUPなら戻り配列を複数列指定。INDEX+MATCHならMATCH結果をセルに保存して使い回す方法があります。VLOOKUPを含め、範囲を揃えた比較も行います。
・32ビット環境 + 広い参照範囲:関数の種類だけで判断せず、参照範囲の縮小、不要な数式・書式の削減、64ビット版への移行可否を含めて検討します。
・旧バージョン(2019以前):VLOOKUPとINDEX+MATCHの両方が候補です。式の単純さ、複数列でのMATCH再利用、保守性を比較して選びます。