ExcelのXLOOKUP関数は、検索範囲から値を探し、同じ位置にある戻り範囲の値を返します。VLOOKUPと違って戻り列が検索列の左側でも使え、完全一致が既定で、見つからない場合の表示も引数で指定できます。基本構文は=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])です。ただし、重複キー、数値と文字列の違い、余分な空白、近似一致、旧Excelとの互換性を理解しないと、見た目だけ正しい誤結果を作ります。本記事では基本から複数列、末尾検索、エラー調査まで実務向けに解説します。
最小構成は三つの必須引数
商品コードがA2:A100、商品名がB2:B100、検索するコードがE2にある場合、=XLOOKUP(E2,A2:A100,B2:B100)と入力します。E2の値をA列で上から探し、最初に一致した行のB列を返します。XLOOKUPは完全一致が既定なので、VLOOKUPの第4引数FALSEを省略したときのような誤解が起きにくい構文です。
=XLOOKUP(E2,A2:A100,B2:B100)検索範囲と戻り範囲は対応する行数または列数をそろえます。A2:A100に対してB3:B101を指定すると一行ずれた値を返し、エラーにならない場合もあるため危険です。数式を作る前に範囲の先頭・末尾、見出しを含めていないか、空白行、結合セルを確認します。コードやIDは重複しない設計かも確認します。
見つからない場合の表示を第4引数で指定する
一致しないと既定では#N/Aが返ります。利用者向け表示を分かりやすくするなら、第4引数へ"未登録"などを指定します。=XLOOKUP(E2,A2:A100,B2:B100,"未登録")とすれば、検索失敗時だけ指定文字列を返します。データ処理では空文字にすると未登録と本当に空の戻り値を区別しにくいため、用途に合う表示を選びます。
=XLOOKUP(E2,A2:A100,B2:B100,"未登録")数式全体をIFERRORで包むと、検索失敗だけでなく範囲不整合、壊れた参照、計算上の別エラーまで隠す場合があります。XLOOKUPの第4引数は「有効な一致がない」場合を扱うため、まずこちらを使います。それでもエラーが出る場合は、数式、参照、データ型、シート保護、外部リンクを調査します。監査用シートでは#N/Aを残して未登録件数を集計する方法もあります。
左方向の検索でも列番号は不要
検索範囲と戻り範囲を別々に指定するため、戻り列が左側でも使えます。社員名がB列、社員番号がD列にあり、社員番号から氏名を返すなら、検索範囲をD列、戻り範囲をB列にします。VLOOKUPのように検索列を表の左端へ移動したり、列番号を数えたりする必要がありません。列を挿入しても、適切な参照なら列番号ずれを避けられます。
=XLOOKUP(G2,D2:D500,B2:B500,"該当なし")左右どちらへも返せることと、どの列を返しても安全ということは別です。社員番号から個人情報や権限情報を表示するブックでは、シート非表示だけをアクセス制御とみなしません。必要な列だけを戻り範囲にし、ブックの共有権限を設定します。列全体参照は大規模ブックで計算負荷を増やす場合があるため、Excelテーブルや実データ範囲を使います。
複数列を一度に返す
戻り範囲を複数列にすると、XLOOKUPは一致行の複数項目を動的配列として横へ返せます。例えば商品名、単価、在庫がB:D列なら、=XLOOKUP(G2,A2:A100,B2:D100,"未登録")で三列分がスピルします。数式は左端セルに一つだけ置き、右側の出力先を空けます。
=XLOOKUP(G2,A2:A100,B2:D100,"未登録")
出力先に値、結合セル、テーブル境界などがあると#SPILL!になる場合があります。スピル範囲へ個別の値を上書きせず、元の数式またはデータを修正します。複数列を返すときも検索範囲と戻り範囲の行数をそろえます。古いExcelでは動的配列やXLOOKUP自体を利用できないため、共有相手の版を先に確認します。
重複があると最初の一致が返る
既定の検索モードでは先頭から末尾へ探し、重複する検索値がある場合は最初の一致を返します。商品コードや社員IDのように一意であるべきキーに重複があると、数式はエラーにならず一件を返すため、データ品質問題を見落とします。COUNTIFで重複数を確認し、入力規則、Power Query、元システムで一意性を管理します。
=COUNTIF(A2:A100,E2)「最新行を返したい」という要件なら、表が時系列で並んでいることを確認し、第6引数の検索モードへ-1を指定して末尾から検索できます。並べ替え順が変わると結果も変わるため、日付列を基準に最新を定義する方が明確な場合があります。重複を業務ルールとして許すのか、エラーとして修正するのかを決めます。
一致モード0・-1・1・2を使い分ける
第5引数の一致モードは、0が完全一致、-1が完全一致または次に小さい値、1が完全一致または次に大きい値、2がワイルドカード一致です。省略時は0です。価格帯、評価区分、送料表などの境界検索では-1または1を使えますが、境界値の意味と表の並びを文書化します。単純なコード検索では明示的に0を使うと意図が伝わります。
=XLOOKUP(E2,A2:A10,B2:B10,"範囲外",-1)
近似一致を使う表では、下限表なのか上限表なのかを明確にし、最小値未満、最大値超過、境界ちょうど、空白、負数をテストします。誤った一致モードはエラーではなく近い値を返すため、発見が遅れます。給与、税率、評価、料金など重要計算では、公式規程と境界表を別担当者が照合し、テストケースを残します。
ワイルドカードと検索モードを理解する
一致モード2では、アスタリスクが任意の文字列、疑問符が任意の一文字、チルダがワイルドカード文字のエスケープに使われます。部分一致は便利ですが、「ABC」を探して「ABC旧」や「新ABC」も一致するなど、意図しない候補を返す可能性があります。候補が複数なら既定では最初の一致です。識別コードには完全一致を使い、説明文検索だけに限定します。
第6引数の検索モードは、1が先頭から、-1が末尾から、2と-2が昇順・降順を前提にしたバイナリ検索です。バイナリ検索は正しく並んでいないデータで無効な結果を返す可能性があるため、性能目的で安易に指定しません。一般的な表では既定の先頭検索を使い、件数が非常に多い場合はテーブル設計やPower Query、データモデルも検討します。
数値・文字列・空白の違いを調べる
見た目が同じ00123でも、片方が数値123、片方が文字列”00123″なら一致しないことがあります。先頭ゼロが意味を持つ商品コードや郵便番号は文字列として統一します。余分な半角・全角空白、改行、全角数字、不可視文字も#N/Aの原因です。セルの表示形式を変えるだけでは値の型が変わらないため、データ取り込み時に正規化します。
TRIM、CLEAN、VALUE、TEXTなどで変換できますが、元データを理解せず数式内で強制変換すると、先頭ゼロや長い識別番号を壊す可能性があります。まずLEN、ISTEXT、ISNUMBER、EXACTなどで差を確認し、コピーした数件だけでなく列全体の品質を調べます。外部システムのIDはExcelの浮動小数点精度の制約も考慮し、文字列で保持するのが安全な場合があります。
Excelテーブルの構造化参照で範囲を安定させる
データ範囲をExcelテーブルに変換すると、=XLOOKUP([@商品コード],商品表[商品コード],商品表[商品名],"未登録")のように列名で参照できます。行を追加するとテーブル範囲が自動拡張され、A2:A100の固定範囲から新しい行が漏れる問題を減らせます。テーブル名と列名は、業務上の意味が分かる短い名前にします。
=XLOOKUP([@商品コード],商品表[商品コード],商品表[商品名],"未登録")
テーブルを拡張しても、キーの重複、空白、型不一致は自動的に解消されません。総計行や見出しを戻り範囲に含めないよう構造化参照を確認します。別ブックのテーブル参照は、ファイルが閉じていると関数や機能によって制約が出る場合があります。重要な参照は同じブックまたは管理されたデータ接続へまとめ、リンク切れを監視します。
Excel 2016・2019との互換性を確認する
MicrosoftのXLOOKUP公式資料では、XLOOKUPはExcel 2016とExcel 2019では利用できないと説明されています。新しいExcelで作ったブックを旧版で開くと、関数名に_xlfn.が付く、#NAME?になる、保存後に互換性問題が生じる場合があります。共同利用者、RPA、サーバー上のExcel、閲覧アプリの版を確認してから採用します。
旧版も対象ならINDEXとMATCH、または完全一致を明示したVLOOKUPなどで代替します。数式を二系統保守する場合は、同じテストデータで結果が一致することを確認します。CSVへ出力すると数式ではなく値だけになる一方、更新性を失います。互換性のために値貼り付けする場合は、更新日時、元データ、作成版を明記した配布用コピーを作ります。
仕上げ前の確認チェックリスト
- 検索範囲と戻り範囲の先頭・末尾・行数が一致しているか
- 完全一致が既定であることと、見つからない場合の表示を確認したか
- 重複キーがある場合に最初の一致を返すことを理解しているか
- 近似一致の境界、最小値未満、最大値超過をテストしたか
- 数値と文字列、先頭ゼロ、余分な空白を正規化したか
- 複数列のスピル先に値や結合セルがないか
- Excel 2016・2019を含む共有先の版互換性を確認したか
完成版では、使用したOfficeの版、OS、編集言語、フォント、確認した表示環境を記録します。共同編集では元ファイルを保持し、書式や数式を大きく変更する前に別名コピーで試します。個人名、社員番号、売上、顧客情報などを含む例は匿名化し、共有先の権限を確認します。表示や計算が期待と違う場合も、保護設定やセキュリティ機能を弱めず、公式仕様と実際の環境を照合します。
コメント