XLOOKUPの全貌を解説！スプレッドシート・Excelの強力な検索ツールを完全マスター

2024-10-122026-06-14

Sheet基本

XLOOKUP完全ガイド
VLOOKUPとの違い・全引数・実務活用パターン5選

VLOOKUPの後継として設計されたXLOOKUP。左側検索・複数列返し・エラー処理内包など、VLOOKUPの弱点をすべて解消している。

GASとの関係：XLOOKUPの「見つからない場合」処理がGASのコードと同じ発想

XLOOKUPの第4引数でエラーを内包する考え方は、GASの try-catch や null チェックと同じ設計思想です。「失敗を想定して設計する」習慣がここから身につきます。

GASで自動化を学ぶ →

XLOOKUPとVLOOKUPの違い

XLOOKUPはVLOOKUPの後継として設計された関数です。VLOOKUPの主な弱点をすべて解消しています。

比較項目	VLOOKUP	XLOOKUP
左側検索	❌ 検索キーは必ず左端列	✅ 任意の列でキー検索可能
複数列を返す	❌ 1列のみ	✅ 隣接する複数列を一度に返せる
エラー処理	別途IFERRORが必要	第4引数で内包できる
逆順検索	❌ 不可	✅ 末尾から検索可能
列挿入への耐性	❌ 列番号がずれる	✅ 列範囲で指定するのでずれない
横方向検索	❌ HLOOKUP が別途必要	✅ 横方向も同じ関数で対応

基本構文と全引数

=XLOOKUP(検索値, 検索範囲, 戻り値範囲, [見つからない場合], [一致モード], [検索モード])

引数	必須	内容	よく使う値
検索値	必須	探したいキー	A2、”商品001″ など
検索範囲	必須	キーを探す列	マスター!A:A
戻り値範囲	必須	返したい値の列（複数列も可）	マスター!B:B、マスター!B:D
見つからない場合	省略可	#N/A の代わりに返す値	“未登録”、””、0
一致モード	省略可	0=完全一致（デフォルト）/ -1=以下 / 1=以上 / 2=ワイルドカード	省略で0（完全一致）
検索モード	省略可	1=先頭から（デフォルト）/ -1=末尾から / 2=昇順バイナリ / -2=降順バイナリ	省略で1

実務でよく使う5パターン

パターン1：基本的な値の引き取り（VLOOKUPの置き換え）

// 商品コード（A列）で検索して商品名（B列）を返す
=XLOOKUP(E2, マスター!A:A, マスター!B:B)

// VLOOKUPとの対比
// VLOOKUP: =VLOOKUP(E2, マスター!A:B, 2, FALSE)
// XLOOKUP: =XLOOKUP(E2, マスター!A:A, マスター!B:B)

XLOOKUPでは検索列と戻り値列を別々に指定します。列番号を数える必要がなく、列を追加しても式が壊れません。

パターン2：見つからない場合の処理を内包する

// 見つからなければ "未登録" を返す（IFERRORが不要）
=XLOOKUP(E2, マスター!A:A, マスター!B:B, "未登録")

// 空白で返したい場合
=XLOOKUP(E2, マスター!A:A, マスター!B:B, "")

パターン3：左側検索（VLOOKUPでは不可）

マスターのB列がキーで、A列の値を返したい場合。VLOOKUPでは絶対にできない操作です。

// B列のコードで検索してA列のIDを返す（右→左の逆引き）
=XLOOKUP(E2, マスター!B:B, マスター!A:A, "未登録")

パターン4：複数列を一度に返す

商品名・単価・担当者の3列を1つの式でまとめて取得します。

// B列の商品名・C列の単価・D列の担当者を一括取得
=XLOOKUP(E2, マスター!A:A, マスター!B:D, "未登録")

// この式をF2に入れると、F2・G2・H2に自動展開される（スピル）

パターン5：末尾から検索（最新データを取得）

同じキーが複数行あるとき、最後の行（＝最新データ）を取得したい場合に使います。

// 検索モードを -1（末尾から）にして最新レコードを返す
=XLOOKUP(E2, 履歴!A:A, 履歴!B:B, "データなし", 0, -1)

ワイルドカード検索（部分一致）

一致モードを 2 にするとワイルドカードが使えます。

// "田中" を含む名前を検索
=XLOOKUP("*田中*", マスター!B:B, マスター!A:A, "なし", 2)

// 記号の意味
// * = 任意の文字列
// ? = 任意の1文字

XLOOKUP でよくあるミス

ミス	症状	対処
検索範囲と戻り値範囲の行数が違う	#VALUE! または予期しない結果	両方の範囲を同じ行数に揃える
スピルの展開先にデータがある	#SPILL!	展開先のセルを空にする
複数列返しで一部の列だけ欲しい	不要な列まで表示される	INDEX と組み合わせて列を絞り込む

VLOOKUPとXLOOKUPの使い分け

既存シートに大量のVLOOKUPがある場合、無理に書き換える必要はありません。ただし以下の場合はXLOOKUPへの移行を検討してください。

マスターの列構成が頻繁に変わる（列番号ずれが頻発する）
逆引き（右→左）が必要になった
複数列をまとめて取得したい
最新レコードを取得したい（末尾検索）
新規シートを作るとき（最初からXLOOKUPを使う）

この知識をGASで活かす

GASで「検索して取得する」処理を書くとき、XLOOKUPと同じ考え方が出てきます。検索範囲・戻り値・見つからない場合の処理という構造は、GASのコードに直接応用できます。

GAS入門ガイドを読む → 殿堂入り一覧へ

Free Newsletter

AIを業務に活かしたいなら
SMR-Labメルマガ

毎週火曜10時、コピペで使えるChatGPTプロンプト・
GASテンプレートをお届け。登録は1分・完全無料。

▶ 無料で登録する