概要
「左方向に検索したい」「VLOOKUP だと右側しか取れない…」
そんな悩みを一発で解決するのが XLOOKUP です。
XLOOKUP は 検索列と返す列を自由に指定できる ため、
「右→左」「左→右」「上下どちらでも」自在に検索できます。
さらに、エラー処理・部分一致・複数条件など、VLOOKUP の弱点をすべて克服した“次世代LOOKUP関数”です。
基本の使い方
書式
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])
最低限必要なのは 検索値・検索範囲・戻り範囲 の3つだけ。
左方向検索の基本形
=XLOOKUP(E2, B2:B100, A2:A100)
意味をかみ砕くと：
- 「E2 の値を」
- 「B2:B100 の中から探して」
- 「見つかった行の A2:A100（左側の列）を返す」
VLOOKUP では不可能だった「右→左」の検索が、XLOOKUP なら簡単にできます。
具体例
商品コード（右側）から商品名（左側）を取得
マスタの構造がこうだとします：
- A列：商品名
- B列：商品コード
注文シートで、E2 に商品コードを入力したら、F2 に商品名を表示したい。
=XLOOKUP(E2, $B$2:$B$100, $A$2:$A$100)
見つからないときにメッセージを表示
=XLOOKUP(E2, $B$2:$B$100, $A$2:$A$100, "コード未登録")
入力が空欄のときは何も表示しない
=IF(E2="","",XLOOKUP(E2,$B$2:$B$100,$A$2:$A$100,"コード未登録"))
応用テンプレート
部分一致（前方一致）で検索
=XLOOKUP(E2 & "*", $B$2:$B$100, $A$2:$A$100, "なし", 2)
一致モード「2」はワイルドカード一致。
最後に一致した値を返す（下から検索）
=XLOOKUP(E2, $B$2:$B$100, $A$2:$A$100, "なし", 0, -1)
複数条件で検索（LET＋FILTER併用）
=LET(
key, (C2:C100=H2) * (D2:D100=I2),
FILTER(A2:A100, key)
)
XLOOKUP 単体では複数条件はできないため、FILTERと組み合わせるのが定石。
数値と文字列の型違いを吸収して検索
=XLOOKUP(TEXT(E2,"00000"), TEXT($B$2:$B$100,"00000"), $A$2:$A$100)
よくあるつまずきと対策
検索範囲と戻り範囲の「行数」が違うとエラー
XLOOKUP は 検索範囲と戻り範囲のサイズが一致している必要があります。
VLOOKUP のように「列番号」は不要
戻り範囲を直接指定するため、列挿入でズレる心配がありません。
完全一致がデフォルト
VLOOKUP と違い、XLOOKUP は 完全一致が標準。
FALSE を書き忘れて誤検索する心配がありません。
見つからないときのエラー処理が簡単
第4引数にメッセージを書くと、IFERROR が不要になります。
例題
問題1: 商品コードが B列、商品名が A列のマスタから、E2 の商品コードに対応する商品名を F2 に表示してください。
=XLOOKUP(E2, $B$2:$B$100, $A$2:$A$100)
問題2: 上記検索で、見つからない場合は「未登録」と表示してください。
=XLOOKUP(E2, $B$2:$B$100, $A$2:$A$100, "未登録")
問題3: E2 が空欄のときは空表示、それ以外は商品名を表示してください。
=IF(E2="","",XLOOKUP(E2,$B$2:$B$100,$A$2:$A$100,"未登録"))
問題4: B列の商品コードに対して、最後に一致した商品名を A列から取得してください。
=XLOOKUP(E2, $B$2:$B$100, $A$2:$A$100, "未登録", 0, -1)
問題5: 商品コードが部分一致（前方一致）する商品名を取得してください。
=XLOOKUP(E2 & "*", $B$2:$B$100, $A$2:$A$100, "未登録", 2)
まとめ
XLOOKUP は VLOOKUP の完全上位互換であり、
左方向検索・エラー処理・部分一致・上下検索 がすべて簡単にできます。
押さえるべきポイントは次の4つ。
- 検索範囲と戻り範囲を自由に指定できる
- 完全一致が標準で安全
- 見つからないときのメッセージを簡単に設定できる
- 左方向検索が一行でできる
これを理解すれば、マスタ参照・台帳管理・検索処理が一気にスマートになります。