1. ホーム
  2. ブログ
  3. XLOOKUP関数の使い方|品番から品名と単価を自動で出す

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

#

この記事は 事務でよく使うExcel関数まとめ|目的から探せる一覧 の1本です。他の関数もまとめて見られます。

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

XLOOKUP(エックスルックアップ)を使うと、品番を入れた瞬間に品名も単価も出ます。 打ち間違いが消えます。

使う表

商品マスタのシート。品番・品名・単価の3列に4件
「マスタ」という別のシートに置く。伝票と同じシートには置かない。
伝票シート。品番と数量だけ入っていて、品名・単価・金額が空の状態
伝票シート。人が打つのは品番と数量だけにする。

XLOOKUPの形

=XLOOKUP(探す値, 探しに行く列, 取ってくる列, 見つからないときの表示)
  1. 探す値手元にある値(伝票のA2の品番)
  2. 探しに行く列マスタの中で、その値が並ぶ列(品番の列)
  3. 取ってくる列欲しい値が並ぶ列(品名の列)
  4. 見つからないときの表示無かったときに出す文字。省略できるが、書いておく

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

STEP1:品名を出す

伝票のB2に =XLOOKUP($A2,マスタ!$A:$A,マスタ!$B:$B,"品番が見つかりません") を入力し、「みかん」と表示された画面
B2 に式を入れると、品番 H-200 の品名「みかん」が出る。

まず1行で、手で打った値と一致するか確認します。

!

$ の意味

$A2 の $ は「A列から動くな」。式を右にコピーしても、探す値が B2、C2 とずれません。マスタ!$A:$A も同じです。セルを選んで F4 を押すと $ が付きます。くわしくは 絶対参照($)の使い方。

STEP2:単価も出す

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

伝票のC2に =XLOOKUP($A2,マスタ!$A:$A,マスタ!$C:$C,"品番が見つかりません") を入力し、3,800と表示された画面
変えたのは マスタ!$B:$B → マスタ!$C:$C の1か所だけ。

XLOOKUPは取ってくる列を列そのもので指定します。マスタの途中に列が増えても正しい列を見続けます。 ここがVLOOKUPとの一番の違いです。

マスタに無い品番を入れると

存在しない品番 Z-999 を入れた行に「品番が見つかりません」と表示された画面
4つ目に書いた文字が出る。#N/A より原因がすぐ分かる。

STEP3:品番が空の行に 0 を出さない

式を下にコピーすると、品番がまだ入っていない行に 0 が並びます。

品番が空の4行目と5行目に、品名と単価が 0 と表示されてしまった画面
品番が空の行に 0。印刷すると見苦しい。

「A2が空なら空欄、そうでなければ探す」をIFで足します。

=IF($A2="","",XLOOKUP($A2,マスタ!$A:$A,マスタ!$B:$B,"品番が見つかりません")) で、空の行が空欄になった画面
=IF($A2="","",XLOOKUP(…))。空の行には何も出ない。

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

STEP4:金額も自動にする

伝票のE2に =IF(C2="","",C2*D2) を入力し、金額76,000と60,000が自動で出た画面
E2 に =IF(C2="","",C2*D2)。単価の打ち間違いによる請求ミスは、ここで消える。

つまずきやすいところ

品番「S-100 」の後ろにスペースが入っているため「品番が見つかりません」になり、金額が #VALUE! になった画面
4行目は「S-100 」と後ろにスペース。見た目は同じでも別の文字。金額の #VALUE! はその巻き添え。
×

「品番が見つかりません」(#N/A)が出る原因

・品番の後ろに余分なスペース(S-100 )
・全角と半角がまざっている(S-100 と S-100)
・マスタ側が数値、伝票側が文字

スペースは TRIM関数 でまとめて消せます。

×

#NAME? が出る

Excel 2019以前では XLOOKUP は使えません。社内に古いExcelがあるなら VLOOKUP で書きます。

マスタは「テーブル」にしておく

Excelの挿入タブで「テーブル」ボタンを赤い枠で示した画面
マスタの中を1つ選んで「挿入」→「テーブル」(または Ctrl+T)。下に行を足したとき範囲が自動で広がる。

作るときの順番

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

    1行目が見出し、2行目から。空行・結合セルを入れない。

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

    手で打った値と一致すればOK。

  3. C2にコピーして、取ってくる列だけ変える
  4. IFで囲んで、空の行に何も出ないようにする
  5. 必要な行数分コピーする

次に読むとよい記事


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

#Excel#XLOOKUP#表引き

この記事について

SUPRAXは、林業・製造・運送の現場経験をもとに、中小企業の業務改善・AI活用・GISの支援を行っています。 記事の内容について「うちの場合はどうか」を聞きたい方は、無料相談をご利用ください。

無料で相談してみる

あわせて読みたい

記事一覧へ戻る