INDEX&INDEXと複合検索:作業列なしの検索と45万行の実測

目次(10項目)

💡 この記事で理解すること

Excel 2016でも、作業列を足さずに2条件で検索したり、1回の検索で複数列を取り出したりできます。この記事では数式を部品から順に組み立て、どの式が通常のEnterで動き、どの式にCtrl+Shift+Enterが必要かを整理します。

先に結論
複合キー:=MATCH(G2&"|"&H2,INDEX(A2:A100&"|"&B2:B100,0),0)
複数条件:=MATCH(1,INDEX((A2:A100=G2)*(B2:B100=H2),0),0)
どちらも作業列なしで書けますが、1回の検索ごとに全行分の配列を作ります。今回の45万行の測定では、作業列にMATCHを分けても全体の再計算はほぼ同じで、値のセルを1つ直したときの再計算に大きな差が出ました(約8秒と約5ミリ秒)。

1. INDEX:INDEXとINDEX&INDEXの違い

記号1文字の違いですが、作るものが違います。A列が部署、B列が社員の表で比べます。

INDEX:INDEXとINDEX&INDEXの比較
INDEX:INDEXINDEX&INDEX
作るものセル範囲(参照)文字列(値)
例(Excel 2016以降)INDEX(A2:E2,1,2):INDEX(A2:E2,1,5)INDEX(A2:E2,1,2)&INDEX(A2:E2,1,5)
A2:E2が 10〜50 のときB2:E2(SUMすると 140)文字列「2050」
主な用途集計範囲や検索範囲の切り替え複数の値をつないだ検索キー
数値の型元のセルのまま数値も文字列になる
読みに行くセル範囲を受け取った関数が読む連結する時点で値を読む

以降の複合キーでは、列全体を要素ごとに連結します。1つのセル同士の連結とは次の点が違います。

  • G2&"|"&H2:1つの文字列「12|345」になります。
  • A2:A100&"|"&B2:B100:配列として評価されると、行ごとに連結した99個の文字列になります。数式全体が配列として評価されないと、この結果にはなりません。

2. 複合キー検索を組み立てる

部署コードと社員コードの組み合わせで、名前を探す例です。部署コードと社員コードは文字列として入っています。

図1:使用する表と検索値

部署、社員、名前の表と検索値
A:部署B:社員C:名前G:部署H:社員
212345田中12345
312345鈴木12345

手順1:キーの作り方を1行で確かめる

いきなり列全体に広げず、まず1行分を確かめます。

=A2&"|"&B2

結果は 12|345。検索値の側も同じ形(G2&"|"&H2)にします。

手順2:列全体のキーをINDEXで包む

=INDEX(A2:A100&"|"&B2:B100,2)

結果は 123|45(2番目のキー)。番号を変えると、中身を1件ずつ確認できます。

手順3:MATCHで位置を求める

=MATCH(G2&"|"&H2,INDEX(A2:A100&"|"&B2:B100,0),0)

結果は 1。第2引数の0は「配列全体」、最後の0は「完全一致」です。

手順4:別の列から値を取り出す

=INDEX(C2:C100,MATCH(G2&"|"&H2,INDEX(A2:A100&"|"&B2:B100,0),0))

結果は 田中。下の行へコピーするなら、範囲を $A$2:$A$100 のように固定します。範囲は実際のデータ行数に合わせ、列全体を指定しないでください(全行分の文字列が作られます)。

通常のEnterで動く式、動かない式

確認した結果は次のとおりです。「通常」は旧来の評価方式での結果で、検索値が表の1行目に一致する条件です。

入力方法ごとの数式の動作
数式通常のEnterCtrl+Shift+Enter
=MATCH(G2&"|"&H2,INDEX(A2:A100&"|"&B2:B100,0),0)1(正しい)1
=MATCH(G2&"|"&H2,A2:A100&"|"&B2:B100,0)#VALUE!1
=MATCH(G2&"|"&H2,INDEX(A2:A100,0)&"|"&INDEX(B2:B100,0),0)#VALUE!1
=MATCH(G2&"|"&H2,INDEX(INDEX(A2:A100,0)&"|"&INDEX(B2:B100,0),0),0)1(正しい)1
INDEXで包むと通常のEnterで動く理由
INDEXの第1引数は配列を受け取れるので、その中の式は配列として計算されます。INDEX(A2:A100,0)&INDEX(B2:B100,0) は「&」の外側にINDEXの配列の受け皿がないため、通常のEnterでは1つの値に絞られてしまいます。Microsoft 365で確認すると、この式には @(暗黙的な共通部分)が付きました。
Microsoft 365とExcel 2021では、どの式もEnterだけで配列として計算されますが、Excel 2016との共有を考えるなら、INDEXで包む形にそろえておくと安全です。
INDEXで包んでも、中にIF・IFERRORがあると配列になりません
=MATCH(1,INDEX(IF(A2:A100=G3,1,0)*(B2:B100=H3),0),0) は、旧来の評価方式の通常入力でIFの中に @ が付き、数式を入れた行によって結果が変わりました。IFやIFERRORを内側で使う式は、Excel 2016ではCtrl+Shift+Enterで確定してください。

3. 「*」で複数条件検索を組み立てる

文字列をつながず、条件ごとの判定を掛け算する方法です。こちらは比較を列ごとに行うので、文字列の衝突が起きません。

図2:判定の配列を掛け算する(G3=123、H3=45で探す)

条件判定の配列の掛け算
行A2:A100=G3×B2:B100=H3=結果
2FALSE×FALSE=0
3TRUE×TRUE=1
4FALSE×FALSE=0
=MATCH(1,INDEX((A2:A100=G3)*(B2:B100=H3),0),0)

結果は 2。結果の配列の中で最初に 1 になる位置を探します。条件を増やすときは *(C2:C100=I3) を足します。

=INDEX(C2:C100,MATCH(1,INDEX((A2:A100=G3)*(B2:B100=H3),0),0))

結果は 鈴木。INDEXで包まない =MATCH(1,(A2:A100=G3)*(B2:B100=H3),0) は、Excel 2016ではCtrl+Shift+Enterが必要です。

アンド方式と掛け算方式の比較
「&」の複合キー「*」の条件判定
比較のしかたつないだ文字列を1回で比較列ごとに比較
文字列の衝突起きることがある起きない
数値と文字列区別されない区別される
ワイルドカード検索値の * ? が働く「=」では文字どおりの比較
検索値の長さ300文字で #VALUE!300文字でも比較できた
45万行の実測(1回の検索あたり)約30ミリ秒約40ミリ秒

4. 誤一致と型の注意点

文字列の衝突と区切り文字

部署12・社員345と、部署123・社員45を、区切りなしでつなぐと、どちらも「12345」になります。

図3:区切り文字の有無で変わる結果(部署123・社員45で検索)

区切り文字の有無での比較
行部署社員区切りなし区切りあり
21234512345 ← 誤一致12|345
31234512345123|45 ← 正しい
=MATCH(G3&H3,INDEX(A2:A100&B2:B100,0),0)

結果は 1(誤り)。エラーにならないので気づきにくい例です。区切り文字を入れると 2 になります。

ただし、区切り文字がデータの中に含まれていると、また衝突します。部署「A」・社員「B|C」と、部署「A|B」・社員「C」は、どちらも「A|B|C」です。データに出てこない文字を選ぶか、「*」の方式を使ってください。

確認した動作の一覧

複合キーと複数条件の注意点
項目「&」の複合キー「*」の条件判定対策
数値と文字列
(数値の7と8で検索。文字列の行が先、数値の行が後)
先にある文字列の行に一致数値の行に一致元データの型をそろえる。「*」で型を無視するには A2:A100&""=G2&""
空白セル検索値が両方空白だと、両方空白の行に一致(キーは「|」)同じ検索値の空白を先に判定する
エラー値
(A列に#N/Aの行がある)
その行を飛ばして、後の行に一致同じIFERRORで包むと配列数式が必要になる
大文字・小文字区別しない(ABCでもabcの行に一致)区別しないINDEX(EXACT(A2:A100,G7)*EXACT(B2:B100,H7)=1,0) を MATCH(TRUE,…,0) で探す(通常のEnter)
ワイルドカード検索値「*|X」が任意の部署に一致文字どおり比較記号を含むなら ~ で打ち消す
重複どちらも最初の1件だけを返す複数件が必要ならROW+SMALL+INDEX、再計算が重ければ上行参照の連番+INDEX+MATCH
長い検索値(300文字)#VALUE!エラーにならず比較(一致なしは #N/A)Microsoftの説明では、MATCHの検索値に使える文字列は255文字まで

5. 複数列をまとめて取得する

A列のIDで探して、B〜K列の10列を取り出す場面です。45万行のデータを想定します。

方法1:列ごとにコピーする(通常のEnter)

=INDEX(データ!$B$2:$K$450001,MATCH($A2,データ!$A$2:$A$450001,0),1)

最後の1を列ごとに2、3…と変えます。1行につきMATCHが10回書かれます。

方法2:列番号を0にして配列数式で受ける

=INDEX(データ!$B$2:$K$450001,MATCH($A2,データ!$A$2:$A$450001,0),0)
  1. 結果を出す10セル(例:C2:L2)を横に選択します。
  2. 選択したまま数式を入力します。
  3. Ctrl+Shift+Enterで確定します。数式が {=…} と表示され、10セルに値が入ります。
  4. 下の行へ広げるときは、10セルをまとめて選択してコピーし、貼り付けます。

1行につきMATCHは1回です。確認に使った小さな表(5列)でも、K4で検索して 42, 43, 44, 45, 46 が一度に返りました。

複数列取得で起きること
場面結果
通常のEnterで1セルだけに入力取得する表の列と重ならない位置では #VALUE!。重なる列に置くと、その列の値だけが表示され、エラーにならないので注意が必要です。
取得列より多いセルを選択余ったセルは #N/A
配列の一部だけを変更・削除Microsoftの説明では不可。変更は全体を選び直して行います。
合計だけ必要な場合=SUM(INDEX($B$2:$F$6,MATCH($H$2,$A$2:$A$6,0),0)) は通常のEnterで動きます。INDEXが行全体の参照を返すためです。
Microsoft 365・Excel 2021先頭セルに入力してEnterすると横にスピルします。Excel 2016にスピルはありません。
10個の独立した検索と、1回の一括取得の違い
方法1は数式が独立しているので、列の入れ替えや1列だけの修正がしやすく、セルごとに書式やエラー処理も変えられます。方法2は検索が1回にまとまり、書き間違いの余地が減りますが、列の追加や修正には配列の選び直しが必要です。速度の差は7章の実測をご覧ください。

6. XLOOKUPで同じことをする

XLOOKUPはExcel 2021以降とMicrosoft 365の関数で、Excel 2016では使えません。ここは比較のために載せています。ブックをExcel 2016の人と共有する場合は使わないでください。

=XLOOKUP(A2,データ!A2:A450001,データ!B2:K450001)

戻り範囲を10列にすると、右へスピルして10列が返ります。確認した小さな表でも 42, 43, 44, 45, 46 が返りました。

XLOOKUPも参照を返すので、INDEXと同じように「:」でつなげます。

=SUM(XLOOKUP("K3",A2:A6,C2:C6):XLOOKUP("K5",A2:A6,C2:C6))
=SUM(INDEX(C2:C6,MATCH("K3",A2:A6,0)):INDEX(C2:C6,MATCH("K5",A2:A6,0)))

どちらも結果は 129。Microsoftの説明にもXLOOKUPの戻り値を範囲として使う例があります。

INDEXとXLOOKUPの比較
やりたいことExcel 2016以降Excel 2021以降・Microsoft 365
複数列の取得INDEX(範囲,MATCH(…),0) を配列数式でXLOOKUP(…) がスピル
範囲の作成INDEX(…):INDEX(…)XLOOKUP(…):XLOOKUP(…)
未一致の表示IFERROR や IFNA第4引数で指定
2条件INDEX(…,0) で包むXLOOKUP(G2&"|"&H2,A2:A100&"|"&B2:B100,C2:C100)

7. 45万行での速度の実測

この測定の読み方
Microsoft 365版Excelでの測定です。Excel 2016では測っていません。Microsoftは、未並べ替えデータの完全一致のVLOOKUP・HLOOKUP・MATCHについて、Office 365 version 1809以降で検索列の内部キャッシュを再利用すると説明しています。そのため、Excel 2016では順位や秒数が大きく変わる可能性があります。下の数値をExcel 2016の所要時間として読まないでください。

測定条件

測定条件
ExcelMicrosoft 365版、ビルド16.0.20625、64ビット。測定日2026-11-05
PCCore Ultra 7 258V(8コア8スレッド)、メモリ約32GB、Windows 11。計算スレッド8
データ1万、10万、30万、45万行。A列はランダムな順の8文字の文字列ID(重複なし)、B〜K列は数値、ほかに数式なし
検索A〜Eは2,000回×10列(20,000セル)。Fは100回×位置のみ。検索値はすべて存在するIDをランダムに選択
再計算全体の再計算(Application.CalculateFull)を連続5回測定した中央値。結果の合計も照合済み

方式の比較(全体の再計算)

方式別の再計算時間(ミリ秒、中央値)
方式数式の形1万行10万行30万行45万行
A VLOOKUPを10列VLOOKUP(id,表,列,FALSE)1121,3575,2368,431
B INDEX+MATCHを10列INDEX(表,MATCH(…),列)1241,3585,4828,355
C 作業列+INDEXMATCH を1列、INDEX(表,$H2,列)881,3585,0788,363
D 配列数式(CSE){INDEX(表,MATCH(…),0)}881,3055,2388,898
E1 範囲限定(位置は既知)1,000行の INDEX:INDEX、作業列71444442
E2 範囲限定(端をMATCH)端の検索も作業列の中1711,4355,1479,723
E3 範囲限定(端をMATCH、10列)10列すべてに同じ式2722,2196,1309,260
参考 XLOOKUP(2021以降)1本で10列スピル911,3535,2948,287

単位はミリ秒。E1で1万行の値が大きいのは測定の順序や負荷の影響の可能性があり、原因は確認していません。

実測読み取れることは次の3つです。

  • A〜Dの差は小さい:全体の再計算だけなら、45万行でどれも8秒台でした。この条件では、配列数式が作業列より遅いとも、作業列が配列数式より速いとも言えません。
  • 端の位置が分かっていれば範囲限定は大きく効く:E1は45万行でも約0.04秒です。
  • 端をMATCHで探すと効果が消える:E2・E3は全域検索と同じかそれ以上です。端を探すMATCHが全域を検索するためです。

時間は検索回数に比例するとは限らない

45万行で検索回数を変えました(3回の中央値、ミリ秒)。

検索回数ごとの再計算時間
方式200回2,000回20,000回
B INDEX+MATCHを10列7,4388,2829,463
C 作業列+INDEX8,2657,7448,144

回数を100倍にしても、時間はほとんど増えていません。時間の大半は、検索そのものより、検索列を最初に準備する処理に使われていると考えられます(Microsoftの説明する内部キャッシュと整合しますが、Excelの内部を確認したものではありません)。行数との関係も単純な比例ではなく、方式Aの1万行と45万行では、行数の45倍に対して時間は約75倍でした。

次の測定でも、キーの型と計算スレッドによって時間が変わりました。

  • キーの型:45万行のMATCHで、文字列キーは約6.9秒、数値キーは約2.4秒でした(2,000回、列数の少ない表)。
  • 計算スレッド:全域検索のA〜Dは、8スレッドで8秒台、1スレッドで約3.1〜3.5秒でした。範囲を限定したE1は、8スレッドで約42ミリ秒、1スレッドで約122ミリ秒です。スレッドが多いほど速いとは限りません。

作業列の効果が出るのは編集のとき

全体の再計算では差がなかった作業列ですが、セルを1つ変更した後の再計算(F9に相当、5回の中央値)では結果が大きく変わります。45万行、2,000回×10列です。

セルを1つ編集した後の再計算時間
方式値の列を1セル編集キー列を1セル編集検索値を1セル編集
A VLOOKUPを10列8,0468,1931.6
B INDEX+MATCHを10列8,1978,3621.5
C 作業列+INDEX5.410,9081.0
D 配列数式(CSE)9,2287,7651.1
E1 範囲限定(位置は既知)5.2430.9

単位はミリ秒。

理由は数式の依存関係です。Bの式は1本の中にMATCHとINDEXがあり、B〜K列を参照しています。そのため値の列を1セル直すと式全体が再計算され、MATCHも実行されます。Cでは、MATCHはキー列だけを参照する別のセルにあるので、値の列を直してもMATCHは実行されず、INDEXの再計算だけで済みます。

キー列を編集すると、Cでも作業列のMATCHがすべて再計算されるので差はなくなります。編集するのが検索値だけなら、どの方式でも1行分の再計算で済みます。

実務経験運営者には、45万行規模の検索でMATCHを作業列に分けて速くなった経験があります。ただし、当時の測定条件と秒数は確認できていないため、この測定と同じ原因だと断定はしません。

複合キーは検索回数に比例して重くなる

全域検索と違い、配列を作る方式は検索回数に比例して時間が増えました。45万行、位置だけを求める1列です(3回の中央値、ミリ秒)。

複合キー方式の検索回数別の再計算時間
方式数式の形100回1,000回
F1 文字列の配列INDEX(A&"|"&B,0)2,81828,093
F2 条件判定の配列INDEX((A=x)*(B=y),0)4,23239,652
F3 データ側にキー列(参考)=A2&"|"&B2 を45万行+MATCH7482,421

行数別では、F1が 1万行66、10万行684、30万行2,025、45万行3,138ミリ秒(100回)で、行数にもほぼ比例しました。「&」の方が「*」より速い結果でしたが、これはこの測定のデータでの結果です。

F3はキー列という作業列を増やす代わりに、1,000回でF1の約12分の1でした。Microsoftの性能ガイドも、検索のたびに文字列を連結する方法を、計算の負荷が大きいものとして挙げています。

INDEX:INDEX自体の負荷

  • 同じ全域を INDEX(K,1):INDEX(K,行数) で指定したMATCHは、45万行で約8.1秒で、直接指定(約8.3秒)との差は測定のばらつきの範囲でした。
  • 範囲内の値をつないでいるわけではないので、範囲を作るだけなら大きな負荷にはなりません。時間がかかるのは、その範囲を調べるMATCHや、端を探すMATCHです。

自分の環境で測る方法

VBAが使えなくても、大まかな比較はできます。作業はブックのコピーで行ってください。

  1. 「数式」タブの「計算方法の設定」を「手動」にします。
  2. 全体の再計算:Ctrl+Alt+F9を押し、ステータスバーの「計算中」が消えるまでをストップウォッチで測ります。
  3. 編集後の再計算:普段直す種類のセルを1つ変更し、F9を押して測ります。
  4. それぞれ5回測り、中央値を比べます。ほかのアプリは閉じておきます。

8. 作業列なしと作業列の使い分け

測定はMicrosoft 365版Excelのものなので、下の表は目安です。データが大きいときは、上の手順で実際のブックのコピーを測ってから決めてください。

状況別の方式の選び方
状況向いている方式理由
数千行程度で、検索も少ない作業列なし(INDEX((A=x)*(B=y),0) など)表を変えずに済み、数式1本で完結します。
同じキーで複数列を取り、値の列をよく編集する検索シートに作業列(MATCH)+INDEX編集後の再計算でMATCHが実行されません。
読み込んだデータを見るだけで、編集しない保守しやすい方式全体の再計算では方式の差が小さい結果でした。
データがグループごとにまとまっている小さな位置の表+INDEX:INDEX調べる行数を減らせます。端を全域のMATCHで探さないことが条件です。
大量の行を複合キーで何度も検索するデータ側にキー列(区切り文字あり)配列を毎回作らずに済みます。
元データの形を変えられず、検索も少ない作業列なしの複合キー検索回数に比例する負荷を許容できる場合に限ります。
全員がExcel 2021以降XLOOKUP配列数式が不要になります。Excel 2016には渡せません。

作業列方式の組み方

作業列は元データではなく、検索シートの側に置けます。元の表に手を加えずに済みます。A列に検索するIDがあるとします。

=MATCH($A2,データ!$A$2:$A$450001,0)
=IF(ISNA($H2),"",INDEX(データ!B$2:B$450001,$H2))

列の B$2:B$450001 は「$」を付けないので、右へコピーするとC列、D列に変わります。行は固定します。見つからないIDは #N/A が作業列に出て、結果の列は空欄になります。

段階検索の組み方:範囲限定を活かす

E1のように端の位置を速く求めるには、データが部署ごとにまとまっている場合に、別シートへ小さな位置の表を作ります。

=MATCH($A2,データ!$A$2:$A$450001,0)
=$B2+COUNTIF(データ!$A$2:$A$450001,$A2)-1

部署表のA列に部署コードを入れます。データが増えない運用なら、形式を選択して値にしておくと、再計算の対象から外せます。

=MATCH($B2,部署表!$A$2:$A$451,0)
=INDEX(部署表!$B$2:$B$451,$H2)-1+MATCH($A2,INDEX(データ!$B$2:$B$450001,INDEX(部署表!$B$2:$B$451,$H2)):INDEX(データ!$B$2:$B$450001,INDEX(部署表!$C$2:$C$451,$H2)),0)

データのA列が部署、B列がIDの配置です。求めた位置は、INDEXで各列から値を取り出すのに使います。

実測45万行、450部署、2,000回で、位置の表を値にした場合は約77ミリ秒、位置の表も数式のままだと約3.4秒でした(全域のMATCHは約6.9秒、いずれも1つの値を取得)。

段階検索の前提
同じ部署の行が連続していることが前提です。データを並べ替えたり行を追加したりすると、位置の表を作り直す必要があります。位置の表を値にした場合は古い値のまま残り、エラーにならずに検索を失敗させることがあります。

保守性の比較

方式ごとの保守性の比較
方式良い点注意点
作業列なしの1本の式列を増やさず、コピーしやすい長くなりやすく、途中の結果を確認しにくい
検索シートの作業列途中の位置が見え、エラーの原因を切り分けやすい列が増える。列を隠すなら説明を残す
複数セルの配列数式検索が1回にまとまる一部だけの修正ができず、慣れていない人には直しにくい
データ側のキー列大量の検索で有利データを貼り替えるたびに数式を伸ばす必要がある

複数行を取り出したい場合は、これらとは別の方法になります。ROW+SMALL+INDEXを第1候補に、再計算が重い場合は上行参照の連番+INDEX+MATCHを検討してください。

まとめと出典

  • 「:」は範囲を作り、「&」は文字列を作ります。
  • 複合キーは INDEX(A&"|"&B,0)、複数条件は INDEX((A=x)*(B=y),0) で、Excel 2016でも通常のEnterで書けます(Excel 2016実機は未確認)。
  • IF・IFERRORを内側に入れた式と、INDEXで包まない式にはCtrl+Shift+Enterが必要です。
  • 区切りなしの連結は誤一致の原因になります。
  • 今回の測定(Microsoft 365版、45万行)では、作業列の効果は全体の再計算ではなく編集後の再計算で表れ、範囲限定は端の位置を全域のMATCHで探さないときに効きました。複合キーの配列は検索回数に比例して重くなりました。
  • Excel 2016では同じ結果になるとは限らないので、実際のブックのコピーで測ってください。

出典(Microsoft公式)

🔗 関連記事