概要
「コードを入れたら名前や単価が自動で入るようにしたい」
そんな“マスタ参照”で、今いちばんおすすめなのが XLOOKUP です。
VLOOKUP と違って、
- 左にも右にも参照できる
- 列を挿入しても壊れにくい
- 「見つからないとき」の表示も1発で書ける
というメリットがあり、マスタ参照にはほぼこれ一択と言っていいくらいです。
ここでは「商品マスタ・社員マスタ」など、実務でよくある形を例にしながら、
プログラミング初心者でも迷わない“型”を作っていきます。
XLOOKUP の基本形（マスタ参照の型）
書式と意味
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])
マスタ参照でほぼ毎回使うのは、前半の4つです。
検索値
マスタで探したい値（商品コード、社員番号など）。注文側シートのセルを指定します。
検索範囲
マスタ側で「検索値が並んでいる列（または行）」です。
例：商品マスタの「商品コード列」。
戻り範囲
見つかったときに返してほしい値が並んでいる列（または行）。
例：「商品名列」「単価列」など。
見つからない場合
コードが見つからなかったときに表示したい文字。
例：「未登録」「要確認」など。
マスタ参照の“基本形”は、次のように覚えてください。
=XLOOKUP(検索値, マスタのキー列, マスタの欲しい列, "未登録")
同じシートでのマスタ参照
商品マスタから商品名を引く
例として、同じシートに次のようなデータがあるとします。
A列〜C列：商品マスタ
A列：商品コード
B列：商品名
C列：単価
E列〜G列：注文データ
E列：商品コード入力欄
F列：商品名（自動表示したい）
G列：単価（自動表示したい）
このとき、F2 に「商品名」を表示する式はこうなります。
=XLOOKUP(E2, $A$2:$A$100, $B$2:$B$100, "未登録")
意味はこうです。
E2 の商品コードを
A2:A100（マスタのコード列）から探し、
見つかった行の B2:B100（商品名列）の値を返し、
見つからなかったら「未登録」と表示する。
単価 G2 も同じ考え方で書けます。
=XLOOKUP(E2, $A$2:$A$100, $C$2:$C$100, "未登録")
別シートのマスタを参照する
「マスタシート ＋ 伝票シート」の王道パターン
よくある構成はこうです。
Sheet「商品マスタ」
A列：商品コード
B列：商品名
C列：単価
Sheet「受注」
E列：商品コード
F列：商品名（自動）
G列：単価（自動）
このとき、「受注」シートの F2 に書く式はこうなります。
=XLOOKUP(E2,
商品マスタ!$A$2:$A$100,
商品マスタ!$B$2:$B$100,
"未登録")
単価 G2 は
=XLOOKUP(E2,
商品マスタ!$A$2:$A$100,
商品マスタ!$C$2:$C$100,
"未登録")
シート名!範囲 という形で、別シートのマスタを指定しているだけです。
テーブル（構造化参照）と組み合わせる
テーブル機能（Ctrl+T）でマスタを「テーブル化」しておくと、
列が増えても壊れにくくなり、式も読みやすくなります。
例えば、商品マスタをテーブル化して名前を「tbl商品」とした場合：
列「商品コード」
列「商品名」
列「単価」
受注シートの F2 に商品名を表示する式はこう書けます。
=XLOOKUP(E2, tbl商品[商品コード], tbl商品[商品名], "未登録")
単価 G2 は
=XLOOKUP(E2, tbl商品[商品コード], tbl商品[単価], "未登録")
「どのマスタのどの列を見ているのか」が一目で分かるので、
後から見直すときのストレスが一気に減ります。
入力空欄やエラーをきれいに処理する
コード未入力のときは何も表示しない
現場でよく嫌われるのが「まだコードを入れていない行にも『未登録』が並ぶ」状態です。
これを避けるには、IF と組み合わせます。
=IF(E2="","",
XLOOKUP(E2, $A$2:$A$100, $B$2:$B$100, "未登録"))
E2 が空欄なら空文字（何も表示しない）、
それ以外なら XLOOKUP を実行、という流れです。
「未登録」の代わりに空白にしたい場合
見つからないときは空白にして、あとで色だけ付ける、という運用もあります。
=XLOOKUP(E2, $A$2:$A$100, $B$2:$B$100, "")
よくあるつまずきと対策
検索範囲と戻り範囲は“行数・開始行”を必ず揃える
悪い例：
検索範囲：$A$2:$A$100
戻り範囲：$B$3:$B$100（B3から始まっている）
良い例：
検索範囲：$A$2:$A$100
戻り範囲：$B$2:$B$100
必ず「同じ行から同じ行まで」で揃えてください。
完全一致が標準なので、FALSE を書き忘れる心配がない
VLOOKUP では「FALSE を付け忘れて近似一致になった…」という事故が多いですが、
XLOOKUP は第5引数（[一致モード]）を省略すると 完全一致 が標準です。
普通に書くだけで、安全な「完全一致マスタ参照」になります。
同じコードが複数行ある場合は「一番上の1件だけ」
XLOOKUP は上から検索して最初に見つかった1行だけを返します。
「最新の1件（最後の一致）」を取りたい場合は、検索モードに -1 を指定します。
=XLOOKUP(E2, $A$2:$A$100, $B$2:$B$100, "未登録", 0, -1)
例題
問題1: 同じシートに、A2:A100 に商品コード、B2:B100 に商品名があります。E2 の商品コードに対応する商品名を F2 に表示し、見つからない場合は「未登録」と表示する式を書いてください。
=XLOOKUP(E2, $A$2:$A$100, $B$2:$B$100, "未登録")
問題2: シート「商品マスタ」の A2:A100 に商品コード、B2:B100 に商品名があります。シート「受注」の E2 に商品コードが入っているとき、対応する商品名を F2 に表示する式を書いてください。
=XLOOKUP(E2, 商品マスタ!$A$2:$A$100, 商品マスタ!$B$2:$B$100, "未登録")
問題3: 商品マスタをテーブル化して「tbl商品」という名前にし、その中に「商品コード」「商品名」「単価」列があります。受注シートの E2 の商品コードに対応する単価を G2 に表示する式を書いてください。
=XLOOKUP(E2, tbl商品[商品コード], tbl商品[単価], "未登録")
問題4: 問題1の設定で、E2 が空欄のときは何も表示せず、入力されているときだけマスタ参照を行う式を G2 に書いてください。
=IF(E2="","",
XLOOKUP(E2, $A$2:$A$100, $B$2:$B$100, "未登録"))
問題5: 顧客マスタ（A2:A100 に顧客ID、B2:B100 に顧客名）から、E2 の顧客IDに対応する顧客名を F2 に表示します。同じIDが複数行ある場合は「一番下の行（最新）の顧客名」を返すように、XLOOKUP で式を書いてください。
=XLOOKUP(E2, $A$2:$A$100, $B$2:$B$100, "未登録", 0, -1)
まとめ
マスタ参照で XLOOKUP を使うときの“型”は、次のひとつです。
=XLOOKUP(検索値, マスタのキー列, マスタの欲しい列, "未登録")
この型さえ身体で覚えてしまえば、
商品マスタ
社員マスタ
顧客マスタ
単価表
コード表
どんなマスタでも、同じ感覚で安全・確実に参照できるようになります。
あとは「別シート名」「テーブル名」「欲しい列」を入れ替えるだけです。