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 | |
|---|---|---|
| 作るもの | セル範囲(参照) | 文字列(値) |
| 例(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:社員 | ||
|---|---|---|---|---|---|---|
| 2 | 12 | 345 | 田中 | 12 | 345 | |
| 3 | 123 | 45 | 鈴木 | 123 | 45 |
手順1:キーの作り方を1行で確かめる
いきなり列全体に広げず、まず1行分を確かめます。
結果は 12|345。検索値の側も同じ形(G2&"|"&H2)にします。
手順2:列全体のキーをINDEXで包む
結果は 123|45(2番目のキー)。番号を変えると、中身を1件ずつ確認できます。
手順3:MATCHで位置を求める
結果は 1。第2引数の0は「配列全体」、最後の0は「完全一致」です。
手順4:別の列から値を取り出す
結果は 田中。下の行へコピーするなら、範囲を $A$2:$A$100 のように固定します。範囲は実際のデータ行数に合わせ、列全体を指定しないでください(全行分の文字列が作られます)。
通常のEnterで動く式、動かない式
確認した結果は次のとおりです。「通常」は旧来の評価方式での結果で、検索値が表の1行目に一致する条件です。
| 数式 | 通常のEnter | Ctrl+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の第1引数は配列を受け取れるので、その中の式は配列として計算されます。
INDEX(A2:A100,0)&INDEX(B2:B100,0) は「&」の外側にINDEXの配列の受け皿がないため、通常のEnterでは1つの値に絞られてしまいます。Microsoft 365で確認すると、この式には @(暗黙的な共通部分)が付きました。Microsoft 365とExcel 2021では、どの式もEnterだけで配列として計算されますが、Excel 2016との共有を考えるなら、INDEXで包む形にそろえておくと安全です。
=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 | = | 結果 |
|---|---|---|---|---|---|
| 2 | FALSE | × | FALSE | = | 0 |
| 3 | TRUE | × | TRUE | = | 1 |
| 4 | FALSE | × | FALSE | = | 0 |
結果は 2。結果の配列の中で最初に 1 になる位置を探します。条件を増やすときは *(C2:C100=I3) を足します。
結果は 鈴木。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で検索)
| 行 | 部署 | 社員 | 区切りなし | 区切りあり |
|---|---|---|---|---|
| 2 | 12 | 345 | 12345 ← 誤一致 | 12|345 |
| 3 | 123 | 45 | 12345 | 123|45 ← 正しい |
結果は 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)
最後の1を列ごとに2、3…と変えます。1行につきMATCHが10回書かれます。
方法2:列番号を0にして配列数式で受ける
- 結果を出す10セル(例:C2:L2)を横に選択します。
- 選択したまま数式を入力します。
- Ctrl+Shift+Enterで確定します。数式が
{=…}と表示され、10セルに値が入ります。 - 下の行へ広げるときは、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にスピルはありません。 |
方法1は数式が独立しているので、列の入れ替えや1列だけの修正がしやすく、セルごとに書式やエラー処理も変えられます。方法2は検索が1回にまとまり、書き間違いの余地が減りますが、列の追加や修正には配列の選び直しが必要です。速度の差は7章の実測をご覧ください。
6. XLOOKUPで同じことをする
XLOOKUPはExcel 2021以降とMicrosoft 365の関数で、Excel 2016では使えません。ここは比較のために載せています。ブックをExcel 2016の人と共有する場合は使わないでください。
戻り範囲を10列にすると、右へスピルして10列が返ります。確認した小さな表でも 42, 43, 44, 45, 46 が返りました。
XLOOKUPも参照を返すので、INDEXと同じように「:」でつなげます。
どちらも結果は 129。Microsoftの説明にも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の所要時間として読まないでください。
測定条件
| Excel | Microsoft 365版、ビルド16.0.20625、64ビット。測定日2026-11-05 |
|---|---|
| PC | Core 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) | 112 | 1,357 | 5,236 | 8,431 |
| B INDEX+MATCHを10列 | INDEX(表,MATCH(…),列) | 124 | 1,358 | 5,482 | 8,355 |
| C 作業列+INDEX | MATCH を1列、INDEX(表,$H2,列) | 88 | 1,358 | 5,078 | 8,363 |
| D 配列数式(CSE) | {INDEX(表,MATCH(…),0)} | 88 | 1,305 | 5,238 | 8,898 |
| E1 範囲限定(位置は既知) | 1,000行の INDEX:INDEX、作業列 | 71 | 44 | 44 | 42 |
| E2 範囲限定(端をMATCH) | 端の検索も作業列の中 | 171 | 1,435 | 5,147 | 9,723 |
| E3 範囲限定(端をMATCH、10列) | 10列すべてに同じ式 | 272 | 2,219 | 6,130 | 9,260 |
| 参考 XLOOKUP(2021以降) | 1本で10列スピル | 91 | 1,353 | 5,294 | 8,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,438 | 8,282 | 9,463 |
| C 作業列+INDEX | 8,265 | 7,744 | 8,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セル編集 |
|---|---|---|---|
| A VLOOKUPを10列 | 8,046 | 8,193 | 1.6 |
| B INDEX+MATCHを10列 | 8,197 | 8,362 | 1.5 |
| C 作業列+INDEX | 5.4 | 10,908 | 1.0 |
| D 配列数式(CSE) | 9,228 | 7,765 | 1.1 |
| E1 範囲限定(位置は既知) | 5.2 | 43 | 0.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,818 | 28,093 |
| F2 条件判定の配列 | INDEX((A=x)*(B=y),0) | 4,232 | 39,652 |
| F3 データ側にキー列(参考) | =A2&"|"&B2 を45万行+MATCH | 748 | 2,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が使えなくても、大まかな比較はできます。作業はブックのコピーで行ってください。
- 「数式」タブの「計算方法の設定」を「手動」にします。
- 全体の再計算:Ctrl+Alt+F9を押し、ステータスバーの「計算中」が消えるまでをストップウォッチで測ります。
- 編集後の再計算:普段直す種類のセルを1つ変更し、F9を押して測ります。
- それぞれ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があるとします。
列の B$2:B$450001 は「$」を付けないので、右へコピーするとC列、D列に変わります。行は固定します。見つからないIDは #N/A が作業列に出て、結果の列は空欄になります。
段階検索の組み方:範囲限定を活かす
E1のように端の位置を速く求めるには、データが部署ごとにまとまっている場合に、別シートへ小さな位置の表を作ります。
部署表のA列に部署コードを入れます。データが増えない運用なら、形式を選択して値にしておくと、再計算の対象から外せます。
データの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公式)
- INDEX 関数:配列形式とセル範囲形式、0の指定
- MATCH 関数:相対位置、最初の一致、大文字小文字、ワイルドカード
- XLOOKUP 関数:複数列の取得、範囲としての利用
- 配列数式のガイドラインと例:複数セルの配列数式の規則
- 暗黙的なインターセクション演算子: @
- Range.Formula vs Range.Formula2:旧来の評価方式(英語)
- Tips for optimizing performance obstructions:MATCHの再利用、文字列連結の負荷(英語)
- Excel performance and limit improvements:検索キャッシュの対象バージョン(英語)