この記事は 事務でよく使うExcel関数まとめ|目的から探せる一覧 の1本です。他の関数もまとめて見られます。
伝票を作るとき、品番を見ながら品名と単価をマスタから探して手で打っていませんか。
XLOOKUP(エックスルックアップ)を使うと、品番を入れた瞬間に品名も単価も出ます。 打ち間違いが消えます。
使う表


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

まず1行で、手で打った値と一致するか確認します。
$ の意味
$A2 の $ は「A列から動くな」。式を右にコピーしても、探す値が B2、C2 とずれません。マスタ!$A:$A も同じです。セルを選んで F4 を押すと $ が付きます。くわしくは 絶対参照($)の使い方。
STEP2:単価も出す
B2の式をC2にコピーして、「取ってくる列」だけを単価の列に変えます。

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

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

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

=IF($A2="","",XLOOKUP(…))。空の行には何も出ない。長く見えますが、足したのは「空なら何もしない」だけです。IFは IF関数の使い方 で。
STEP4:金額も自動にする

=IF(C2="","",C2*D2)。単価の打ち間違いによる請求ミスは、ここで消える。つまずきやすいところ

「品番が見つかりません」(#N/A)が出る原因
・品番の後ろに余分なスペース(S-100 )
・全角と半角がまざっている(S-100 と S-100)
・マスタ側が数値、伝票側が文字
スペースは TRIM関数 でまとめて消せます。
#NAME? が出る
Excel 2019以前では XLOOKUP は使えません。社内に古いExcelがあるなら VLOOKUP で書きます。
マスタは「テーブル」にしておく

作るときの順番
- マスタを別シートに分ける
1行目が見出し、2行目から。空行・結合セルを入れない。
- B2に式を入れて、1行で確認する
手で打った値と一致すればOK。
- C2にコピーして、取ってくる列だけ変える
- IFで囲んで、空の行に何も出ないようにする
- 必要な行数分コピーする
次に読むとよい記事
- 古いExcel(2019以前)なら → VLOOKUP関数の使い方
- 行と列の両方を指定して取り出す → INDEXとMATCHの使い方
- 条件に合う行をまとめて抜き出す → FILTER関数の使い方
- 取ってきた数字を集計する → SUMIFS関数の使い方
「うちのマスタは項目がバラバラで、そもそも整理されていない」という段階のご相談もお受けしています。現状のヒアリングと課題整理は無料です。
この記事について
SUPRAXは、林業・製造・運送の現場経験をもとに、中小企業の業務改善・AI活用・GISの支援を行っています。 記事の内容について「うちの場合はどうか」を聞きたい方は、無料相談をご利用ください。
あわせて読みたい
SUMIFS関数の使い方|複数条件の合計、月別×取引先別の集計表、0になるときの直し方
「A社の9月分だけ合計したい」。フィルタで絞って右下の合計を電卓に打ち直していませんか。 SUMIFS(サムイフズ)を1つ書けば、条件に合う行だけを足し…
VLOOKUP関数の使い方|列を挿入すると静かに間違う
品番を入れたら品名と単価が出る「表引き」を長く支えてきたのが VLOOKUP(ブイルックアップ) です。 後継の XLOOKUP がありますが、Exce…
IFERROR関数の使い方|エラーを消していい場面とは
式を入れた表に #N/A や #DIV/0! がずらっと並ぶ。これを消すのが IFERROR(イフエラー) です。 ただし、使ってはいけない場面がありま…