トップ > 関数 > 検索・照合 > XLOOKUP

商品コードから商品名と単価を自動で引く

XLOOKUP(エックスルックアップ)関数

注文リストに商品コードだけが並んでいて、商品名と単価は別の「商品マスタ」表にある。1件ずつ目で探して転記するのは、件数が多いほど時間がかかって、転記ミスも起きます。XLOOKUPは「コードをマスタから探して、同じ行の商品名を持ってくる」関数です。数式を1行入れて下までコピーすれば、注文リスト全体が一気に埋まります。

✅ これができます:商品コードを入れるだけで、商品名と単価が自動で表示されるようになります

完成形(実行前 → 実行後)

実行前
実行前
実行後
実行後

数式を入れる前(左)は商品名・単価が空欄。入れた後(右)は、コードに合う商品名と単価が自動で入ります。マスタに無いコード(P-999)には「該当なし」と表示されます

これをコピペすれば動きます

商品名を引く数式(注文表の F2 に入れる)

=XLOOKUP($E2,$A$2:$A$6,$B$2:$B$6,"該当なし")

単価を引く数式(注文表の G2 に入れる。変えるのは取り出す範囲だけ)

=XLOOKUP($E2,$A$2:$A$6,$C$2:$C$6,"-")

変えるのはここだけ

変える場所を示した図
数式の「変える場所」を番号で示した図。番号は下の表と対応しています
番号変える場所意味書き方・例
1検索する値
$E2
探したい商品コードが入っているセル自分の表では「注文コードが入っているセル」に読み替える(例:$B2)。下の行にコピーしても列がずれないよう、列の前に $ を付ける
2探す範囲
$A$2:$A$6
マスタ側の「商品コード」の列。XLOOKUPはこの中から検索値を探すマスタが100件なら $A$2:$A$101 のように、最終行を自分のマスタの行数に合わせる
3取り出す範囲
$B$2:$B$6
持ってきたい情報の列。ここでは商品名の列単価が欲しいときは $C$2:$C$6 のように、欲しい情報の列に変える。探す範囲と行の高さを必ずそろえる
4見つからない時の表示
"該当なし"
マスタにコードが無かったときに表示する文字空欄にしたいときは "" にする。「★マスタ未登録」のように現場で気づきやすい言葉にするのも手

組み込み手順

  1. サンプル表を用意します。A〜C列に商品マスタ(商品コード/商品名/単価)、E列に調べたい商品コードを並べます
  2. F2 に1つ目の数式を貼り付けます(このページの数式はコピーボタンでそのままコピーできます)
  3. F2 を選択して、右下の小さい四角(フィルハンドル)を下の行まで引っぱると、全行に数式がコピーされます
  4. G2 に2つ目の数式(単価用)を貼り、同じように下までコピーします
  5. 自分の表で使うときは、「変えるのはここだけ」の①〜④を自分の表の場所に差し替えます

このページのサンプル表は、A2:C6 が商品マスタ(5件)、E2:E5 が注文リスト(4件)です。P-999 は「マスタに無いコード」の例としてわざと入れています。数式を試すときは、自分の Excel/スプレッドシートに表を写して、F2・G2 に数式を貼ってください。

Excelとスプレッドシートの違い

Microsoft 365 と Excel 2021 以降で使えます。数式はこのページのものがそのまま動きます。Excel 2019 以前では XLOOKUP 自体が無いため、入れると #NAME? エラーになります。その場合は VLOOKUP か INDEX+MATCH で同じことができます(関連リンク参照)。

古い Excel での代替:VLOOKUP(右方向の列だけ)/INDEX+MATCH(左方向も可)

Googleスプレッドシートでも XLOOKUP がそのまま使えます。数式・引数の順番は Excel と同じで、このページの数式をそのまま貼れます。カスタマイズ例の「2列まとめて返す」も、スプレッドシートではそのまま動きます。

カスタマイズ例

商品名と単価を、まとめて横に返したい

取り出す範囲(③)を $B$2:$C$6 のように2列に広げると、商品名と単価が横並びで一気に返ります。数式は F2 に1つ入れるだけです(Microsoft 365 と スプレッドシートで使えます)。

見つからないコードを目立たせたい

④を "★マスタ未登録" のような言葉に変えます。あとから検索で拾いやすくなり、マスタへの登録漏れに気づけます。

あいまいなコードで探したい(部分一致)

数式の最後に , ,2 と足して =XLOOKUP($E2,$A$2:$A$6,$B$2:$B$6,"該当なし",2) にすると、* や ? を使ったワイルドカード検索になります(例:検索値に P-10* と入れる)。

つまずきどころ

関連する関数・レシピ

次はこれ:フォルダを一括で作る(VBS)
計算を任せる次は、作業そのものの自動化です。月末のフォルダ作りをダブルクリック1回にするレシピ

よくある質問

Q. VLOOKUP と何が違うの?

A. XLOOKUP は、探す列より左にある列も取り出せる、見つからない時の表示を数式の中で指定できる、表の途中に列を挿しても壊れにくい、の3点が進化しています。新しく覚えるなら XLOOKUP がおすすめです。

Q. Mac でも使える?

A. 使えます。Microsoft 365 の Mac版 Excel と、ブラウザ版の Googleスプレッドシートで動作します。

Q. 「該当なし」ではなく空欄にしたい

A. 数式の4つ目の引数(④)を "" に変えてください。見つからない行は空欄になります。

PR(広告)

もっと体系的に学びたい人へ
独学で体系化するなら、講座で一気に学ぶのが近道です。

Udemy:Excelショートカット講座を見る Udemy:Googleスプレッドシート入門講座を見る