A列とB列を結合して検索する方法:作業列なしのINDEX+MATCH
💡 やりたいこと
「大阪」と「みかん」のように、A列とB列の組み合わせで検索して売上を取り出したい。でも、表に結合用の列(作業列)は足したくない。そんなときは、A列とB列を数式の中で結合して、MATCHで探します。
=INDEX(C2:C6,MATCH(F2,INDEX(A2:A6&B2:B6,0),0))F2に「大阪みかん」と入れると、売上の 60 が返ります。Excel 2016以降で、通常のEnterで確定します。
使用する表
F2に探したい値を1つ入れ、G2に売上を表示します。検索値は「大阪みかん」のように、A列とB列をつないだ文字にします。
| A:支店 | B:商品 | C:売上 | F:検索値 | G:結果 | ||
|---|---|---|---|---|---|---|
| 2 | 東京 | りんご | 120 | 大阪みかん | 60 | |
| 3 | 東京 | みかん | 80 | |||
| 4 | 大阪 | りんご | 200 | |||
| 5 | 大阪 | みかん | 60 | |||
| 6 | 名古屋 | りんご | 150 |
数式の組み立て方
手順1:INDEX&INDEXで1行を結合する
「&」は文字をつなぐ記号です。A列の4番目とB列の4番目をつなぎます。
結果は 大阪みかん。
手順2:全行を結合する
同じことを全行まとめて行います。INDEXの番号を0にすると「全部」という意味になります。
この部分は、計算の中だけに、次のような結合した列を作ります。セルには何も増えません。
図1:数式の中だけにできる結合の列
| 何番目 | A列 | B列 | 結合した結果 | ||
|---|---|---|---|---|---|
| 1 | 東京 | & | りんご | → | 東京りんご |
| 2 | 東京 | & | みかん | → | 東京みかん |
| 3 | 大阪 | & | りんご | → | 大阪りんご |
| 4 | 大阪 | & | みかん | → | 大阪みかん |
| 5 | 名古屋 | & | りんご | → | 名古屋りんご |
手順3:MATCHで何番目かを探す
F2の値が、結合した列の何番目にあるかを探します。最後の0は「完全一致で探す」という指定です。
結果は 4。上から4番目という意味です(シートの行番号の5ではありません)。
手順4:INDEXで売上を取り出す
C列の4番目を取り出せば完成です。
結果は 60。下の行へコピーするときは =INDEX($C$2:$C$6,MATCH($F2,INDEX($A$2:$A$6&$B$2:$B$6,0),0)) のように範囲を固定します。
INDEX&INDEXをMATCHに渡す書き方
INDEX同士をそのまま結合して渡すこともできますが、Excel 2016では入力方法に注意が必要です。
| MATCHの第2引数 | Excel 2016:通常のEnter | Excel 2016:Ctrl+Shift+Enter | Microsoft 365:Enter |
|---|---|---|---|
INDEX(A2:A6&B2:B6,0)(おすすめ) | 4 | 4 | 4 |
INDEX(INDEX(A2:A6,0)&INDEX(B2:B6,0),0) | 4 | 4 | 4 |
INDEX(A2:A6,0)&INDEX(B2:B6,0) | #VALUE! | 4 | 4 |
3つ目はExcel 2016で通常のEnterを押すと動きません。短くて入力方法を気にせず使える1つ目がおすすめです。
作業列と比べると重い
この数式は表を変えずに済みますが、負荷は作業列より高くなります。数式のセルごとに、全行の結合をやり直すからです。作業列なら、結合は各行で1回だけで、MATCHは出来上がった列を探すだけです。
図2:全体を再計算したときの時間(ミリ秒)
| データ行数 | 検索する回数 | 数式の中で結合 | 作業列 |
|---|---|---|---|
| 1万行 | 1回 | 4.1 | 3.6 |
| 1万行 | 1,000回 | 436 | 44 |
| 10万行 | 1回 | 24 | 17 |
| 10万行 | 1,000回 | 4,596 | 468 |
| 45万行 | 1回 | 119 | 52 |
| 45万行 | 100回 | 2,065 | 681 |
| 45万行 | 1,000回 | 20,931 | 2,245 |
実測Microsoft 365版Excel(ビルド16.0.20625、64ビット、8スレッド)、測定日2026-11-05。文字列キーの完全一致で、全体の再計算を5回測った中央値(45万行・1,000回の「数式の中で結合」のみ3回)。Excel 2016では測っていません。Excel 2016では数値や差の大きさが変わる可能性があります。
- 検索が少なければ差は小さい:1万行で1回なら、ほぼ同じでした。
- 検索回数に比例して重くなる:45万行で1,000回では約21秒で、作業列の約9倍でした。
- 検索値を1つ変えるだけでも差が出る:45万行で約50ミリ秒に対し、作業列は約2ミリ秒でした。
| 状況 | 向いている方法 |
|---|---|
| 数千行までで、数式も数個〜数十個 | この記事の数式 |
| 表に列を足せない | この記事の数式。範囲はデータの行数に絞ります |
| データや数式が多い、再計算が気になる | 作業列(結合キー列) |
表に列を足せない場合も、A:A のような列全体の指定は避けてください。全行分の結合が行われます。
うまくいかないとき
| こんなとき | 原因 | 対処 |
|---|---|---|
結果が #N/A | 一致するものがない | 余分な空白、全角と半角を確認します。表示を変えるなら =IFERROR(数式,"該当なし") で包みます |
A列・B列が数値で #N/A | 結合した結果は文字列なので、F2が数値だと一致しない | MATCH(F2&"",…) と書くか、F2を文字列にします |
| 違う行の値が返る | 12と345、123と45は、どちらも「12345」になる | 下の区切り文字を使います |
| 同じ組み合わせが複数ある | 最初の1件だけが返る | 複数件はROW+SMALL+INDEXで抽出します |
| 大文字と小文字を区別したい | MATCHでは区別されない | 応用編のEXACTを使います |
同じ文字になるときは区切り文字を入れる
コードの組み合わせなどでは、記号をはさみます。F2には「12|345」のように入れます。
区切り文字は、データに出てこない記号を選びます。
Microsoft 365版Excel(ビルド16.0.20625、確認日2026-11-05)で結果を確認しました。「Excel 2016」の列は、この版の旧来の評価方式(
Range.Formula と Range.FormulaArray)で入力した結果です。Excel 2016の実機では確認していないため、最初に小さな表で試してから使ってください。
🔗 関連記事
- INDEX + MATCH(Excel 2016以降):基本の使い方
- INDEX + MATCH + 結合キー列:表に結合列を作る方法
- INDEX&INDEXと複合検索:作業列なしの検索と45万行の実測:詳しい応用編
- INDEX:INDEXで可変範囲を作る:「:」でつなぐ別の使い方