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 | 見つからない時の表示"該当なし" | マスタにコードが無かったときに表示する文字 | 空欄にしたいときは "" にする。「★マスタ未登録」のように現場で気づきやすい言葉にするのも手 |
このページのサンプル表は、A2:C6 が商品マスタ(5件)、E2:E5 が注文リスト(4件)です。P-999 は「マスタに無いコード」の例としてわざと入れています。数式を試すときは、自分の Excel/スプレッドシートに表を写して、F2・G2 に数式を貼ってください。
Microsoft 365 と Excel 2021 以降で使えます。数式はこのページのものがそのまま動きます。Excel 2019 以前では XLOOKUP 自体が無いため、入れると #NAME? エラーになります。その場合は VLOOKUP か INDEX+MATCH で同じことができます(関連リンク参照)。
古い Excel での代替:VLOOKUP(右方向の列だけ)/INDEX+MATCH(左方向も可)
取り出す範囲(③)を $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回にするレシピ
A. XLOOKUP は、探す列より左にある列も取り出せる、見つからない時の表示を数式の中で指定できる、表の途中に列を挿しても壊れにくい、の3点が進化しています。新しく覚えるなら XLOOKUP がおすすめです。
A. 使えます。Microsoft 365 の Mac版 Excel と、ブラウザ版の Googleスプレッドシートで動作します。
A. 数式の4つ目の引数(④)を "" に変えてください。見つからない行は空欄になります。