はじめに：「列を追加したら数式が全滅した」を二度と繰り返さない
VLOOKUPやINDEX+MATCHをある程度使いこなしていても、こんな経験はないでしょうか。
「列を1つ追加したら、数式の列番号が全部ズレて修正に1時間かかった…」
列番号を 3 や 5 と直接入力（ハードコーディング）している限り、この問題は避けられません。
この記事を読み終えると、列を追加・削除しても一切手を加えなくてよい数式が書けるようになります。 旧バージョンのExcelから最新のExcel 365まで使える方法を段階的に解説するので、どの環境の方でも実践できます。
前提：サンプルデータと問題の確認
以下のような社員テーブルを例に使います。
| A | B | C | D |
|---|---|---|---|
| 社員ID | 氏名 | 部署 | 給与 |
| E001 | 田中太郎 | 営業部 | 350,000 |
| E002 | 鈴木花子 | 総務部 | 280,000 |
| E003 | 佐藤次郎 | 開発部 | 420,000 |
よくある数式はこうです。
=INDEX(A:D, MATCH(E2, A:A, 0), 3)
この 3（部署列）が問題です。仮に「電話番号」列をC列に追加すると、部署はD列にズレるため、数式の 3 を 4 に手動修正しなければなりません。列が多いほど、修正漏れのリスクも増大します。
4つの方法を選ぶ基準
各方法に入る前に、選択の目安を示します。
| 状況 | 選ぶ方法 |
|---|---|
| 旧バージョン（2016以前）を使っている | 方法① INDEX + MATCH（二重） |
| Excel 365/2019でシンプルに書きたい | 方法② INDEX + XMATCH |
| 直感的な書き方にしたい | 方法③ XLOOKUP |
| 複数列を一括で取得したい | 方法④ XLOOKUP + CHOOSECOLS |
方法①：MATCH で列番号を動的取得する【旧バージョン対応】
こんな場合に使う： Excel 2016以前の環境や、職場共有ファイルでバージョンを選ばず動かしたいとき。
ヘッダー行を活用し、MATCH関数を2つ重ねます。
=INDEX(A:D, MATCH(E2, A:A, 0), MATCH(F1, A1:D1, 0))
- E2：検索したい社員ID（例：E001）
- F1：取得したい列名（例：「給与」と入力）
動作のポイント：
MATCH(E2, A:A, 0)→ 社員IDが何行目にあるか返すMATCH(F1, A1:D1, 0)→ 「給与」がヘッダー行の何列目にあるか返す- F1を「部署」に変えれば部署を、「氏名」に変えれば氏名を自動参照
列を追加しても、ヘッダー行のMATCHが自動で列番号を再計算するため、数式の修正は一切不要です。
方法②：XMATCH でさらに堅牢に【Excel 365/2019以降】
こんな場合に使う： Excel 365または2019以降を使っており、コードをよりシンプル・厳密に書きたいとき。
=INDEX(A:D, XMATCH(E2, A:A), XMATCH(F1, A1:D1))
MATCHとXMATCHの主な違い
| 機能 | MATCH | XMATCH |
|---|---|---|
| デフォルトの一致モード | 要指定（0） | 完全一致がデフォルト |
| 逆順（末尾から）検索 | 不可 | -1 で指定可 |
| 見つからない場合の戻り値 | IFERRORが必要 | 第4引数で指定可 |
重複データがある場合の最新レコード取得：
=INDEX(A:D, XMATCH(E2, A:A, 0, -1), XMATCH(F1, A1:D1))
-1 を指定すると末尾から検索するため、同じ社員IDが複数行ある場合でも最新データを返します。
方法③：XLOOKUP で直感的に書く【Excel 365以降】
こんな場合に使う： 数式の構造をシンプルに保ちたい、VLOOKUPに近い感覚で書きたいとき。
=XLOOKUP(E2, A:A, INDEX(A:D, 0, XMATCH(F1, A1:D1)))
INDEX(A:D, 0, 列番号) のポイント： 行番号に 0 を指定すると、その列全体を返します。これをXLOOKUPの「戻り範囲」として渡すことで、参照列を動的に切り替えられます。
方法④：CHOOSECOLS で複数列を一括取得【Excel 365以降・スピル対応】
こんな場合に使う： 1つの数式で複数列のデータをまとめて取り出したいとき。
まず CHOOSECOLS の基本を押さえましょう。
=CHOOSECOLS(A:D, 1, 3, 4)
これで「A列・C列・D列」だけを抽出した仮想テーブルが生成されます。
XLOOKUPと組み合わせて複数列を返す
=XLOOKUP(E2, A:A, CHOOSECOLS(A:D, MATCH({"氏名","給与"}, A1:D1, 0)))
実行結果のイメージ（スピル表示）：
田中太郎 | 350,000
{"氏名","給与"} のように配列で列名を渡すと、対応する列番号がまとめて計算され、1つのセルに入力するだけで横方向に結果が展開されます。従来なら列ごとに数式を用意していた作業が、これ1本で完結します。
実践例：ドロップダウン連動ダッシュボード
ここまでの技術を組み合わせると、実務で使えるセルフサービス型ダッシュボードが作れます。
設定セル： - G1：検索したい社員ID（手入力） - G2：表示したい列名（データ入力規則でドロップダウンリストを設定）
出力セル H1：
=IFERROR(
INDEX($A:$D,
XMATCH($G$1, $A:$A),
XMATCH($G$2, $A$1:$D$1)
),
"該当なし"
)
G2のドロップダウンで「氏名」「部署」「給与」を切り替えるだけで、H1の表示が自動更新されます。データ管理者でなくても誰でも使えるため、集計依頼の削減や入力ミスの防止に直結します。
よくあるエラーと対処法
#N/A が出る場合
- 検索値の前後にスペースが混入していないか確認 →
TRIM(E2)で除去 - 数値と文字列の型不一致 → セルの書式と実際のデータ型を確認
#REF! が出る場合
- CHOOSECOLS/INDEXで範囲外の列番号を指定していないか確認
- XMATCHが一致しなかった場合の戻り値を明示する
=XMATCH(F1, A1:D1, 0, 0) ' 見つからない場合は0を返す（エラー回避）
今日から使えるアクションステップ
記事の内容を実践に移すための3ステップです。
- 既存シートの
INDEX(..., ..., 3)を探す → 列番号が直書きになっている数式を特定する - 列番号を
MATCH(見出し名, ヘッダー行, 0)に置き換える → まず方法①を試す - Excel 365環境なら
XMATCHに切り替える → エラー処理も引数で完結させ、数式をすっきりさせる
小さな修正でも、積み重ねることでメンテナンスコストは大幅に下がります。まず1つの数式から試してみてください。
まとめ
列番号をハードコーディングする習慣を手放すと、「列を追加するたびに数式を直す」という作業から解放されます。
- 旧バージョンなら INDEX + MATCH の二重使いが最も汎用的
- Excel 365/2019なら XMATCH でより厳密に、XLOOKUPでよりシンプルに
- 複数列をまとめて返したいなら CHOOSECOLS との組み合わせが最強
次回は「複数条件での縦横クロス検索」を解説します。INDEXとXMATCHを使って、行・列ともに動的に指定する応用テクニックを紹介予定です。ぜひブックマークしておいてください。
