INDEX＋XMATCHとスピルで複数データを一括抽出する方法【数式コピー不要】

Excelの行列検索の最終形としてINDEXと2つのMATCH（またはXMATCH）を組み合わせる方法を学びました。しかし、Microsoft 365の登場で、このテクニックはさらなる進化を遂げました。

それが「動的配列（スピル）」機能です。これにより、従来であれば一つずつ数式をコピー＆ペーストしていた作業が、たった一つの数式を入力するだけで、関連するセルすべてに結果を自動で表示できるようになりました。今回は、この革新的な機能を使った一括データ抽出の方法を解説します。

今回のゴール：複数の氏名・複数の項目を「単一の数式」で一気に取り出す

左側の「名簿」から、右側の「名簿抜粋」にリストアップされた3人分の「年齢」と「都道府県」を抽出します。この時、

H3セルに一度だけ数式を入力するだけで、H3からI5までの6つのセルすべてに結果を表示させることが目標です。

解決策：動的配列に対応した INDEX & XMATCH

手順：H3 に INDEX と XMATCH の式を入れて Enter

G3:G5 の 3 人について、年齢と都道府県を名簿から取り出します。H3 に 1 回入れるだけで、H3:I5 の 6 セルが埋まります。 H3 に次の式を入力して、Enter キーを押します❶。

確認：3 人 × 2 項目が 1 つの式で埋まった

答えは 3 行 × 2 列の 6 つの値 になりました❷。式は、次の順に計算しています。

1. XMATCHで、3 人が名簿の何行目かを調べる
   ⇒ 3・1・5 行目
2. XMATCHで、年齢・都道府県が何列目かを調べる
   ⇒ 1・3 列目
3. INDEX が、その行と列が交わる値をまとめて取り出す
   ⇒ H3:I5 の 6 つ

▶ H3の式を、練習ツールで試す

サンプルデータ

以下は、上記画像のExcelデータですので、ダウンロードして練習として使用ください。

練習用Excelファイル（無料ダウンロード）

この記事の解説で使うExcelデータです。数式と練習データが入っているので、本文の式を同じ表で試せます。

無料Excelサンプルファイルダウンロード（.xlsx・14KB）

Excelファイル（.xlsx）／登録不要／数式・練習データ入り

この機能を試すために、以下のサンプルデータをExcelシートにコピーしてください。

まず動かしてみよう｜この記事のサンプルをその場で試せます

練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。

数式の解説（なぜ1つの数式で全てが埋まるのか？）

この数式が動的配列として機能する秘訣は、XMATCH関数の引数に単一のセルではなく、セル範囲を渡している点にあります。

1. XMATCHで行番号の「配列」を取得する

XMATCH(G3:G5, B3:B8)

• 従来はG3のように単一のセルを検索値としていましたが、ここではG3:G5という範囲を指定しています。
• これにより、XMATCHはG3, G4, G5の各氏名が、名簿B3:B8の何番目にあるかを一度に計算し、結果を配列（値のリスト）として返します。
  • 例：「神保 明成」→3番目、「本堂 成重」→1番目、「笠間 直忠」→5番目
  • 結果として {3; 1; 5} のような縦の配列が生成されます。

2. XMATCHで列番号の「配列」を取得する

XMATCH(H2:I2, C2:E2)

• 同様に、列の検索値もH2:I2という範囲で指定します。
• これにより、XMATCHは「年齢」と「都道府県」が、見出しC2:E2の何番目にあるかを一度に計算します。
  • 例：「年齢」→1番目、「都道府県」→3番目
  • 結果として {1, 3} のような横の配列が生成されます。

3. INDEXが配列を受け取り、結果をスピルさせる

最終的に、INDEX関数は以下のような命令を受け取ったことになります。

=INDEX(C3:E8, {3; 1; 5}, {1, 3})

• INDEX関数は、行番号と列番号に配列が渡されると、それらの組み合わせからなる行列（マトリクス）の結果を返そうとします。
• 「3行目の1列目と3列目」「1行目の1列目と3列目」「5行目の1列目と3列目」…という全ての組み合わせの結果を計算し、出力先のセルに自動で展開します。これが「スピル」です。

【実機で検証】見つからない・同じ名前があるときの動き

氏名や項目が見つからない場合など、1 つの式で広げたときの動きを Excel の実機で試しました。

場面	結果	対処
氏名が見つからない	その行がすべて #N/A	IFERROR(式,”なし”) で置き換える
項目が見つからない	その列がすべて #N/A	見出しの文字を確かめる
氏名と項目を両方とも縦に指定	2 個しか返らない（エラーなし）	氏名は縦、項目は横に指定する
元のセルが空	0 と表示される	式の最後に &”” を付ける
同じ氏名が 2 行ある	最初の行だけ返る	CHOOSECOLS(FILTER(A2:D4,A2:A4=”田中”),2,3) で全部出す

氏名と項目は、縦と横に分けて指定します。同じ向きにすると、「田中の部署」と「鈴木の年齢」のように 2 個だけが返り、4 個の表になりません。エラーが出ないので、結果が足りないときはここを確かめます。

まとめ

Microsoft 365の動的配列機能は、Excelの数式の概念を大きく変えました。INDEXとXMATCH（またはMATCH）の組み合わせにこの機能を用いることで、複数の検索をたった一つの数式で、瞬時に、そしてエラーなく実行できます。

• 効率性: 数式をコピー＆ペーストする必要が一切ない。
• 保守性: 修正はH3セルの数式一つで済む。
• 可読性: 処理の内容が単一の数式に集約されているため、分かりやすい。

これからのExcelの標準となるこのパワフルな機能をぜひマスターして、日々のデータ集計作業を劇的に効率化してください。

動的配列・スピル系の関数の全体像は、こちらのまとめで整理しています。

縦に並んだリストを表の形にするときは、縦に並んだリストを表にする方法もあわせてご覧ください。