折れ線は［図形のフリーフォーム］で作る｜頂点の編集はドラッグでかんたん修正
k.w
SEへの道
はじめてでも迷わない！XLOOKUP関数をやさしく深掘り――実務で使える設定・応用まで徹底ガイド
k.w
Excelでの検索・参照作業をもっと確実に、もっと速く。そんなニーズに応えるのがXLOOKUP（エックスルックアップ）です。ここでは基礎から一歩踏み込み、実務でつまずきやすいポイントや高速化の工夫まで、まとめて解説します。
Contents
はじめにXLOOKUPとは？
XLOOKUPは、探す値を指定範囲から見つけ、その行（または列）の対応値を返す関数です。VLOOKUP/HLOOKUPの弱点（左側検索不可・列挿入に弱い・未一致時のIFERROR併用が必要…）を解消し、柔軟で読みやすい式が書けます。
引数と書式の理解
一般形：
＝XLOOKUP(検索値，検索範囲，戻り範囲，未一致時，一致モード，検索モード)
| 引数 | 意味 | 要点 |
|---|---|---|
| 検索値 | 探したい値（セル参照/文字列/数値） | 前後空白や型ズレに注意 |
| 検索範囲 | 検索対象の1列（または1行） | 戻り範囲と高さ/幅を揃える |
| 戻り範囲 | 返したい列（または行） | 複数列OK（スピル） |
| 未一致時 | 見つからない時の表示 | “該当なし”など任意文字列 |
| 一致モード | 一致方法の指定 | 0=完全一致（既定）, −1/1=近似, 2=ワイルドカード |
| 検索モード | 検索方向/方式 | 1=先頭から, −1=末尾から, 2/−2=バイナリ |
未一致時はIFERROR不要
VLOOKUPではIFERRORでラップしがちですが、XLOOKUPは第4引数「未一致時」で直接指定できます（式が読みやすく、計算も軽くなります）。
一致モードと検索モードの早見表
| 一致モード | 動作 | 注意点 |
|---|---|---|
| 0 | 完全一致 | 最も安全。既定値 |
| −1 | 以下で最も近い | 検索範囲は昇順に並べる |
| 1 | 以上で最も近い | 検索範囲は昇順に並べる |
| 2 | ワイルドカード（* ?） | 「一致モード=2」を必ず指定 |
| 検索モード | 動作 | 用途 |
|---|---|---|
| 1 | 先頭→末尾へ線形検索 | 通常の検索 |
| −1 | 末尾→先頭へ線形検索 | 最後の一致を取りたい時 |
| 2 | 昇順のバイナリ検索 | 大規模データ高速化（要昇順） |
| −2 | 降順のバイナリ検索 | 大規模データ高速化（要降順） |
基本の使い方商品コードから商品名を検索
- 商品コード：A2:A100、商品名：B2:B100、検索値：E2とします。
- 商品名をF2に表示：
＝XLOOKUP(E2，A2:A100，B2:B100，”該当なし”) - 未一致時は「該当なし」と表示され、#N/Aが見出しや集計に伝播しません。
応用編複数条件・2次元参照・複数列返却
複数条件（部署×役職）
- D列に複合キー：D2＝B2&”｜”&C2 を作成し下方へコピー（「｜」は区切り記号）。
- 検索キー：G2＝E2&”｜”&F2（E2=部署、F2=役職）。
- 氏名を取得：
＝XLOOKUP(G2，D2:D100，A2:A100，”該当なし”)
2次元（行×列）で値を取り出す
- 左に品名（行ラベル）、上に月（列ラベル）のクロス表で売上を取得：
- ＝XLOOKUP(H2，A2:A100，XLOOKUP(I2，B1:Z1，B2:Z100，”該当なし”)，”該当なし”)
- 外側で行を、内側で列を選ぶイメージです（INDEX/MATCHの代替）。
複数列をまとめて返す（スピル）
- 商品コードから「商品名・単価・在庫」を横に同時取得：
＝XLOOKUP(E2，A2:A100，B2:D100，”該当なし”) - F2以降に横方向へ自動スピルします（表計算の整形が楽に）。
部分一致・最後の一致・高速検索
前方一致・部分一致（ワイルドカード）
- 「食」で始まる商品名のコードを返す：
＝XLOOKUP(“食“，B2:B200，A2:A200，”該当なし”，2) - は任意の文字列、? は任意の1文字です。
最後の一致（下から上へ探す）
- 同一コードが複数回出現、最新（最後）の行を取得：
＝XLOOKUP(E2，A2:A100，B2:B100，”該当なし”，0，−1)
大規模データを高速に（バイナリ検索）
- 検索範囲が昇順に並んでいるとき：
＝XLOOKUP(E2，A2:A100000，B2:B100000，”該当なし”，0，2) - 降順なら検索モードは −2。並び順が条件なので注意。
よくあるエラーと対処
型の不一致（数値と文字列）
- 数字に見えて文字列のコード → VALUE/– で数値化、TEXTで文字列化。
- 例：検索値を文字列化して合わせる：
＝XLOOKUP(TEXT(E2,”0″)，A2:A100，B2:B100，”該当なし”)
前後の空白・不可視文字
- TRIM/CLEANで整形：
＝XLOOKUP(TRIM(E2)，TRIM(範囲) … ) は配列変換が重いことも。
入力元でデータクレンジングするのが最善。
日付の一致
- 見た目が同じでもシリアル値が違う/時刻付きでズレる場合あり。
INTで日付に丸める、またはTEXT(E2,”yyyy-mm-dd”)でキー化。
実務で役立つテクニック
LETで式を読みやすく軽くする
- 同じ範囲を何度も参照しない：
＝LET(k,TRIM(E2), keyRange,TRIM(A2:A100), retRange,B2:B100, XLOOKUP(k,keyRange,retRange,”該当なし”))
CHOOSECOLS/TAKEで戻り範囲を整形
- 広い戻り範囲から必要列だけ：
＝XLOOKUP(E2，A2:A100，CHOOSECOLS(B2:H100,1,3,5)，”該当なし”)
テーブル（構造化参照）で壊れない式
- 表をテーブル化しておけば、列の増減でも式が自動追随：
＝XLOOKUP([@コード]，Table1[コード]，Table1[商品名]，”該当なし”)
よくある質問や疑問
Q1: 複数ヒットをすべて取得できますか？
XLOOKUP単体は最初（または最後）の1件を返します。複数行を返すなら FILTER を使います。
- ＝FILTER(B2:B100，A2:A100=E2,”該当なし”)
- 条件が複数なら、(条件1)*(条件2)… でAND、(条件1)+(条件2)… でOR。
Q2: 大文字小文字を区別できますか？
標準のXLOOKUPは大文字小文字を区別しません。厳密一致なら、EXACT と FILTER/XMATCH を組み合わせます。
- ＝FILTER(B2:B100，EXACT(A2:A100,E2),”該当なし”)
Q3: XLOOKUPが#N/Aになる原因は？
- 前後空白・全角/半角違い・型違い（文字列/数値）・日付の時刻混在。
- 未一致時引数でユーザー向けメッセージを返しつつ、元データの整備を。
今すぐ解決したい！どうしたらいい？
数式の見直しやデータ整形の相談、大量データの高速化など、状況に合わせて最短ルートをご提案します。気軽にご連絡ください。
まとめ
XLOOKUPは「探す→返す」をシンプルかつ堅牢にする強力な関数です。未一致時の表示・ワイルドカード・最後の一致・高速検索・複数列返却まで、一通り押さえれば、VLOOKUP時代の悩みはほぼ解消します。さらに LET/CHOOSECOLS/テーブルを組み合わせて、読みやすく壊れにくい設計を目指しましょう。運用で詰まったら、いつでも相談してください。
ABOUT ME
