XLOOKUP関数の使い方｜品番から品名と単価を自動で出す

伝票や日報を作るとき、品番を見ながら品名と単価をマスタから探して手で打ち込んでいませんか。

この作業は XLOOKUP（エックスルックアップ） を使うと、品番を入れた瞬間に品名も単価も自動で出るようになります。打ち間違いも消えます。

この記事で作るもの

商品マスタがこうなっているとします。

伝票シートで、品番を打っただけで残りが埋まる状態にします。

人が打つのは品番と数量だけになります。

XLOOKUPの形

日本語にすると、こうです。

A2の値を、マスタの品番列から探して、見つかった行の品名列を持ってくる。

覚えることはこれだけです。

STEP1：品名を出す

伝票のB2に、次の式を入れます。

まず1行だけで、手で打った値と一致することを確認してください。ここが合っていれば、あとはコピーするだけです。

STEP2：単価も出す

B2の式をC2にコピーして、最後の「取ってくる列」だけを単価の列に変えます。

XLOOKUPは「取ってくる列」を列そのもので指定します。マスタの途中に列が増えても、正しい列を見続けてくれます。ここがこの関数の一番の利点です。

STEP3：品番が空のときは何も出さない

このままだと、品番を入れていない行に「品番が見つかりません」が並んでしまいます。

「A2が空なら空欄、そうでなければ探す」という形にします。

長く見えますが、やっているのは「空なら何もしない」を足しただけです。
IF の詳しい使い方は IF関数の使い方 で扱います。

STEP4：金額も自動にする

単価が出たら、あとは掛け算するだけです。

つまずきやすいところ

作るときの順番

1. マスタを別シートに分ける

   伝票と同じシートにマスタを置かないでください。行の挿入・並べ替えのたびに壊れます。

2. マスタの1行目を見出しにして、2行目からデータを入れる

   途中に空行を入れない。結合セルも使わない。この2つを守るだけで事故がかなり減ります。

3. B2に式を入れて、1行だけで確認する

   手で打った値と一致すればOKです。合わなければ、原因はほぼスペースか全半角です。

4. C2にコピーして、取ってくる列だけ変える

   変えるのは1か所だけです。

5. IFで囲んで、空の行に何も出ないようにする

   ここまでが実務で使う形です。

6. 必要な行数分コピーする

   最後にまとめてコピーします。

いきなり全行に式を入れないでください。1行で合わせてからコピーする。この順番だと、ズレたときにどこが原因かすぐ分かります。

今日やってみること

いま手で転記している表を1つ選んでください。品番と品名、社員番号と氏名、コードと名称。「片方を見てもう片方を探している」作業ならどれでも当てはまります。

マスタになる表を別シートに切り出して、1行だけXLOOKUPを入れてみる。ここまでで、この関数が自社で使えるかどうかが分かります。

次に読むとよい記事

• 古いExcel（2019以前）を使っている場合 → VLOOKUP関数の使い方とXLOOKUPとの違い
• 行と列の両方を指定して取り出したい場合 → INDEXとMATCHの使い方（近日公開）
• 条件に合う行をまとめて抜き出したい場合 → FILTER関数の使い方（近日公開）
• 取ってきた数字を集計したい場合 → SUMIFS関数の使い方

「うちのマスタは項目がバラバラで、そもそも整理されていない」という段階のご相談もお受けしています。現状のヒアリングと課題整理は無料です。