Excelで行全体を1セルで取得する方法｜XLOOKUP・INDEX+MATCHを徹底比較
「商品コードを入力したら、その行の全データを自動で表示したい」
こんな場面、Excelを使っていれば必ず一度は遭遇するはずです。多くの人がまず思いつくのはVLOOKUPですが、VLOOKUPには致命的な弱点があります。列ごとに列番号を変えながら何度も式を書かなければならない上に、テーブルに列を追加した途端に列番号がズレて式が壊れる――この繰り返しに、うんざりした経験はないでしょうか。
この記事を読み終えると、1つの数式で行全体を一括取得する3つの方法を使いこなせるようになります。自分のExcelバージョンと用途に合った方法を選んで、もう列番号の管理から解放されましょう。
まず「自分はどれを使うべき？」を確認しよう
詳細を読む前に、まず自分に合う方法を確認してください。
| 状況 | 推奨方法 |
|---|---|
| Excel 365 / 2021を使っている | ① XLOOKUP（最もシンプル） |
| 古いExcelでスマートに書きたい | ② INDEX+MATCH（行一括取得） |
| 他人と共有・互換性最優先 | ③ INDEX+MATCH（列個別取得） |
それぞれの詳細は以降のセクションで解説します。
サンプルデータの確認
今回は以下のテーブル（A1:E5）を使います。「商品コード」を検索キーにして、該当行の全データを別セルに表示することを目標にします。
| 商品コード | 商品名 | カテゴリ | 単価 | 在庫数 |
|---|---|---|---|---|
| A001 | りんご | フルーツ | 150 | 300 |
| A002 | バナナ | フルーツ | 80 | 500 |
| A003 | にんじん | 野菜 | 120 | 200 |
| A004 | 玉ねぎ | 野菜 | 90 | 400 |
検索値は H2 に入力するとして、結果を I2 以降に表示します。
方法①：XLOOKUP で行全体を取得する（Excel 365 / 2021以降）
数式
=XLOOKUP(H2, A2:A5, A2:E5)
H2：検索する商品コードA2:A5：検索対象の列（商品コード列）A2:E5：返す値の範囲（テーブル全体）
出力イメージ
H2 に A002 と入力すると、I2 以降に次のように自動展開（スピル）されます。
→ 結果：A002 ／ バナナ ／ フルーツ ／ 80 ／ 500
I2 に数式を1つ入力するだけで、J2・K2・L2・M2 に残りの値が自動的に広がります。これがスピル機能です。追加でセルを編集する必要はありません。
エラーハンドリング
存在しない商品コードを入力すると #N/A エラーが表示されます。実務では必ず第4引数で対処しましょう。
=XLOOKUP(H2, A2:A5, A2:E5, "該当なし")
まとめ
- ✅ 書き方が最もシンプル
- ✅ スピルで自動展開、メンテナンスが楽
- ✅ エラー処理が直感的に書ける
- ❌ Excel 365 / 2021以降が必要
方法②：INDEX+MATCH で行全体を一括取得する
数式
INDEX の列番号に 0 を指定すると、その行全体を配列で返すという特性を利用します。
=INDEX(A2:E5, MATCH(H2, A2:A5, 0), 0)
MATCH(H2, A2:A5, 0)：検索値が何行目にあるかを数値で返す- 最後の
0：列全体を返す指定
出力イメージ
H2 に A003 を入力した場合：
→ 結果：A003 ／ にんじん ／ 野菜 ／ 120 ／ 200
Excel 365ではスピルで自動展開されます。Excel 2019以前では Ctrl+Shift+Enter で配列数式として入力してください（数式バーに {=INDEX(...)} と波括弧が付きます）。
エラーハンドリング
=IFERROR(INDEX(A2:E5, MATCH(H2, A2:A5, 0), 0), "該当なし")
IFERROR でラップすることで、検索値が見つからない場合も安全に処理できます。
まとめ
- ✅ Excel 2003以降の幅広いバージョンで動作
- ✅ 1つの数式で行全体を取得できる
- ⚠️ 旧バージョンでは配列数式の入力が必要
- ❌ スピル非対応環境では扱いに慣れが必要
方法③：INDEX+MATCH を列ごとに記述する（互換性最優先）
数式
列ごとに個別の INDEX+MATCH を書くパターンです。冗長に見えますが、どのバージョンのExcelでも確実に動作する最も安全な方法です。
=INDEX($B$2:$B$5, MATCH($H$2, $A$2:$A$5, 0)) ← 商品名 =INDEX($C$2:$C$5, MATCH($H$2, $A$2:$A$5, 0)) ← カテゴリ =INDEX($D$2:$D$5, MATCH($H$2, $A$2:$A$5, 0)) ← 単価 =INDEX($E$2:$E$5, MATCH($H$2, $A$2:$A$5, 0)) ← 在庫数
COLUMN関数で横コピーを効率化する
毎回列範囲を書き換えるのが面倒な場合は、COLUMN 関数を使うと式を右方向にコピーするだけで完結します。
=INDEX($A$2:$E$5, MATCH($H$2, $A$2:$A$5, 0), COLUMN(A1))
I2 に入力してそのまま右にコピーすると、COLUMN(A1)→1、COLUMN(B1)→2... と列番号が自動的に増えて5列分を展開できます。
出力イメージ
H2 に A004 を入力した場合：
→ 結果：玉ねぎ ／ 野菜 ／ 90 ／ 400
エラーハンドリング
=IFERROR(INDEX($B$2:$B$5, MATCH($H$2, $A$2:$A$5, 0)), "該当なし")
各列の式を IFERROR でラップしておくと、検索値が空欄のときや存在しないときにエラーが表示されません。
まとめ
- ✅ 全バージョンのExcelで動作・配列数式不要
- ✅ 式が素直で他人も読みやすい
- ✅ COLUMN関数で効率化も可能
- ❌ 列数が多いと式の管理がやや手間
3つの方法を徹底比較
| 方法 | 対応バージョン | 記述量 | スピル対応 | 難易度 |
|---|---|---|---|---|
| ① XLOOKUP | 365 / 2021以降 | 少ない | ◎ | 易しい |
| ② INDEX+MATCH（行一括） | 全バージョン※ | 普通 | △ | 普通 |
| ③ INDEX+MATCH（列個別） | 全バージョン | 多め | × | 易しい |
※旧バージョンでは Ctrl+Shift+Enter による配列数式入力が必要
まとめ
今回は「行全体を取得する」という実務頻出シナリオを通じて、3つのアプローチを解説しました。
- VLOOKUPの限界（列番号管理の煩雑さ・列追加で式が壊れる問題）を解決できる
- XLOOKUPはExcel 365 / 2021なら迷わず第一選択
- INDEX+MATCH（行一括）は古いExcelでもスマートに書きたいときに有効
- INDEX+MATCH（列個別）は互換性・可読性を最優先にしたいときの鉄板
どの方法も IFERROR や XLOOKUP の第4引数でエラー処理を忘れずに。実務のファイルに組み込む際は、必ずエラー時の挙動も確認しておきましょう。
次回は「列全体を取得する」方法についても解説予定です。ぜひお楽しみに！
