概要
「一致する値を取りたい」「マスタから該当データを引っ張りたい」――そんなときの最強ツールが XLOOKUP です。
VLOOKUP の完全上位互換で、左方向検索・右方向検索・上下検索・部分一致・エラー処理まで全部一式でこなせます。
ここでは「一致値を取得する」という最も基本で最も使う場面に絞って、初心者でも迷わないように丁寧に解説します。
基本の考え方
XLOOKUP の書式は次のとおりです。
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])
最低限必要なのは 検索値・検索範囲・戻り範囲 の3つだけ。
一致値を取得する最も基本の形
=XLOOKUP(E2, A2:A100, B2:B100)
意味をかみ砕くと：
- 「E2 の値を」
- 「A2:A100 の中から探して」
- 「一致した行の B2:B100 の値を返す」
これだけで「一致値の取得」が完了します。
基本の使い方（マスタ参照の定番）
商品コードから商品名を取得
A列：商品コード
B列：商品名
E2 に商品コードを入力したら、F2 に商品名を表示したい場合：
=XLOOKUP(E2, $A$2:$A$100, $B$2:$B$100)
見つからないときにメッセージを出す
=XLOOKUP(E2, $A$2:$A$100, $B$2:$B$100, "未登録")
入力が空欄のときは何も表示しない
=IF(E2="","",XLOOKUP(E2,$A$2:$A$100,$B$2:$B$100,"未登録"))
応用テンプレート
左方向検索（VLOOKUPではできない）
A列：商品名
B列：商品コード
E2 のコードから商品名を取得したい場合：
=XLOOKUP(E2, $B$2:$B$100, $A$2:$A$100)
部分一致（前方一致）
=XLOOKUP(E2 & "*", $A$2:$A$100, $B$2:$B$100, "なし", 2)
一致モード「2」はワイルドカード一致。
最後に一致した値を取得（下から検索）
=XLOOKUP(E2, $A$2:$A$100, $B$2:$B$100, "なし", 0, -1)
検索モード「-1」は「下から上へ検索」。
型（文字列／数値）の違いを吸収して一致させる
=XLOOKUP(TEXT(E2,"00000"), TEXT($A$2:$A$100,"00000"), $B$2:$B$100)
よくあるつまずきと対策
検索範囲と戻り範囲の行数が違うとエラー
XLOOKUP は 検索範囲と戻り範囲のサイズが一致している必要があります。
完全一致が標準なので安全
VLOOKUP と違い、XLOOKUP は完全一致がデフォルト。
FALSE を書き忘れて誤検索する心配がありません。
見つからないときのエラー処理が簡単
第4引数にメッセージを書くだけで OK。
IFERROR を外側に書く必要がありません。
文字列と数値の混在に注意
見た目が同じでも、型が違うと一致しません。
必要に応じて TEXT や VALUE で揃えましょう。
例題
問題1: A2:A100 に商品コード、B2:B100 に商品名があります。E2 の商品コードに一致する商品名を F2 に表示してください。
=XLOOKUP(E2, $A$2:$A$100, $B$2:$B$100)
問題2: 一致する商品コードがない場合は「未登録」と表示してください。
=XLOOKUP(E2, $A$2:$A$100, $B$2:$B$100, "未登録")
問題3: E2 が空欄のときは空表示、それ以外は商品名を表示してください。
=IF(E2="","",XLOOKUP(E2,$A$2:$A$100,$B$2:$B$100,"未登録"))
問題4: 商品コードが B列、商品名が A列にある場合、E2 のコードに一致する商品名を F2 に表示してください（左方向検索）。
=XLOOKUP(E2, $B$2:$B$100, $A$2:$A$100)
問題5: A2:A100 に顧客ID、C2:C100 に金額があります。E2 の顧客IDに一致する「最新の金額（下から検索）」を F2 に表示してください。
=XLOOKUP(E2, $A$2:$A$100, $C$2:$C$100, "なし", 0, -1)
まとめ
XLOOKUP は「一致値を取得する」ための最強関数で、
左方向検索・部分一致・上下検索・エラー処理まで一式でこなせます。
押さえるべきポイントは次の4つ。
- 検索範囲と戻り範囲を自由に指定できる
- 完全一致が標準で安全
- 見つからないときのメッセージを簡単に設定できる
- 左方向検索も上下検索も一行でできる
この型を覚えるだけで、マスタ参照・台帳管理・検索処理が圧倒的に楽になります。