【奥義】大量データでの高速VLOOKUP

公開日：2013年5月以前　最終更新日：2025-11-07

【奥義】大量データでの高速VLOOKUP

大量データからのVLOOKUPを大量の行に設定すると再計算がなかなか終わらなくなります…
そんな経験したことがある人は、少なからずいると思います、
そんな場合に、高速にVLOOKUPを実行する方法です。

この記事は、当初は2013年頃に公開した記事です。
その後に2020年頃に文章を若干書き直したり体裁を整えたりしています。

以下では、別記事で、現代の最新関数を使った高速VLOOKUP数式（実際にはXLOOKUP）を紹介しています。
大量データで処理時間がかかる関数の対処方法（SumIf）｜XLOOKUP+SORTが最強だった … 後日追加（2025/11/4）

高速VLOOKUPに使用するサンプルデータ

以下の表で説明します。

※Sheet1は、A列で昇順に並び変えておきます。
並べ替えては困る場合は、事前に順序番号を振っておき、後で並べ替えし直して下さい。

Sheet2のA列で、Sheet1のA列を検索し、B列を、Sheet2のB列に取り出します。
それぞれ20万件のデータを入れています。

高速VLOOKUPの数式

Sheet2のB2セルの計算式

行範囲を指定

=IF(INDEX(Sheet1!$A$2:$A$200001,
MATCH($A2,Sheet1!$A$2:$A$200001,1),1)=$A2,
VLOOKUP($A2,Sheet1!$A$2:$B$200001,2,TRUE),NA())

列全体を指定

=IF(INDEX(Sheet1!$A:$A,MATCH($A2,Sheet1!$A:$A,1),1)=$A2,
VLOOKUP($A2,Sheet1!$A:$B,2,TRUE),NA())

この記事を最初に書いた頃はExcelが2003でした。
その頃は、VLOOKUPの近似値検索で列指定をすると長い計算時間がかかりました。
その注意書きをしていましたが、近年のバージョンでは解決されているので列指定で構いません。

高速VLOOKUPの数式解説

少々長い計算式になっています。
列指定の短い方で解説します。

全体としてのロジックの流れ

MATCHで近似値検索する
MATCHの返り値を元に、INDEXで値を取得
INDEXの返り値が、本来の検索値と一致しているかの判定
ここまでが存在確認になります
一致している場合、VLOOKUPを近似値で実行
一致していない場合、#NA

もう少し簡単に書くと、
MATCHの近似値検索→INDEXで値取得→検索値と一致→VLOOKUPを近似値で実行

以下で、個別の関数を分解して説明していきます。

一番外側のIF関数の概要

IF(存在確認,VLOOKUP実行,NA())

最初に検索値の存在確認をしています。
MATCHで近似値の行を取得し、その行の値をINDEXで取得します。
その結果が、検索値と一致しているかを判定しています。NA()は、「#N/A」です。

存在確認

INDEX(Sheet1!$A:$A,MATCH検索値以下,1)=$A2

検索値以下のMATCH関数で探した行の値をINDEXで取得します。
その結果が、本来の検索値と同一かを判定しています。

近似値検索なので、同値がない場合でもMATCHが取得されてしまうので、
完全一致しているかの判定（=$A2）になります。

近似値検索は非常に高速に動作します。
しかし、あくまで近似値検索なので、検索値がない場合の判定が必要になるということです。

MATCH検索値以下

INDEX(Sheet1!$A:$A,MATCH($A2,Sheet1!$A:$A,1),1)=$A2

MATCH($A2,Sheet1!$A:$A,1)
第3引数が1がポイントです、近似値（以下）での検索をしています。
この場合は、検索範囲が昇順に並んでいる必要があります。

この数式を「MATCHの結果」として、次の説明に続きます。

存在確認の完成数式

INDEX(Sheet1!$A$2:$A$200001,MATCH($A2,Sheet1!$A$2:$A$200001,1),1)=$A2

MATCHの結果（何番目か）を使用し、INDEXで値を取り出します。
その結果が、本来の検索値と一致しているかの判定です。
MATCHが近似値検索なので、完全一致の行が存在しない場合は、直前の値が取得されるのです。

VLOOKUP実行

VLOOKUP($A2,Sheet1!$A:$B,2,TRUE)

検索値が存在している場合に、VLOOKUPを実行します。
第4引数がTRUEがポイントです、MATCHと同じく近似値（以下）での検索をしています。
TRUEは1としても構いません。
既に存在確認ができているので、結果として完全一致での検索と同じになります。

高速VLOOKUPの補足

Sheet1の並べ替えを元に戻す必要がある場合は、
Sheet2のB列（計算式を入れた列）は、値の貼り付けで、計算式を削除してから行ってください。

MATCH、VLOOKUPともに、近似値での検索の場合は、二分検索（といわれている）しているので、とても高速に検索されます。
そこで、検索範囲を並べ替えして、近似値のみで実現したのが上記計算式になっています。

上記の処理時間は、私の古いノートPCでも数秒で完了します。
普通にVLOOKUPしたら何分かかるかわかりません。

実際に、やり比べてみれば直ぐに実感できるはずです。

同じテーマ「エクセル関数応用」の記事

セルの参照範囲を可変にする（OFFSET,COUNTA,MATCH）
グラフのデータ範囲を可変にする
 セルの個数を数える関数
【奥義】大量データでの高速VLOOKUP
数値を時刻に変換
 関数のネスト方法
 ワイルドカードが使える関数
 グラフで特定の横軸の色を変更し基準線を引く
 OFFSET関数解説・応用・使用例
 MATCH関数解説・応用・使用例
 選択行の色を変える（条件付き書式,Worksheet_SelectionChange）

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門

ホーム
エクセル全般
エクセル関数応用
【奥義】大量データでの高速VLOOKUP

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.

エクセル関数応用
【奥義】大量データでの高速VLOOKUP