1. ホーム
  2. ブログ
  3. VLOOKUP関数の使い方|列を挿入すると静かに間違う

VLOOKUP関数の使い方|列を挿入すると静かに間違う

#

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

品番を入れたら品名と単価が出る「表引き」を長く支えてきたのが VLOOKUP(ブイルックアップ) です。

後継の XLOOKUP がありますが、Excel 2019以前では使えません。 古いExcelがまざっている会社向けに、使い方と落とし穴を書きます。

まず、自分のExcelがどちらか確認する

空いているセルに =XL と打ってみてください。候補に XLOOKUP が出れば XLOOKUP関数の使い方 を読んでください。出なければ、この記事です。

!

社内でバージョンがまざっているなら、VLOOKUPで書く

XLOOKUPで作ったファイルを古いExcelで開くと #NAME? になり、計算されません。複数人で使うファイルは、一番古い環境に合わせます。

使う表

商品マスタのシート。品番・品名・単価の3列。A列の品番を赤い枠で示している
VLOOKUPは、指定した範囲の一番左の列(赤い枠)しか探しません。 探す値は必ず左端に置く。

VLOOKUPの形

=VLOOKUP(探す値, 探す範囲, 何列目を取るか, 検索方法)
  1. 探す値手元にある値(伝票のA2の品番)
  2. 探す範囲マスタの範囲。一番左の列が「探しに行く列」になる
  3. 何列目を取るか範囲の左端を1として数えた番号。品名なら2
  4. 検索方法完全一致なら FALSE。省略しない

STEP1:品名を出す

伝票のB2に =VLOOKUP($A2,マスタ!$A:$C,2,FALSE) を入力し、「みかん 10kg」と表示された画面
B2 に式。マスタのA〜C列の中で、2列目(品名)を取ってくる。

STEP2:単価も出す

伝票のC2に =VLOOKUP($A2,マスタ!$A:$C,3,FALSE) を入力し、3,800と表示された画面
変えたのは 2 → 3 だけ。金額は =C2*D2。

列番号の数え方

シートの列記号ではなく、指定した範囲の左端から数えます。

マスタの品番・品名・単価の下に 1列目・2列目・3列目 と書いた画面
範囲が A:C なら 品番=1、品名=2、単価=3。範囲を B:C にすると品名=1、単価=2 に変わる。

最大の落とし穴:列を挿入すると静かに間違う

マスタに「規格」の列を1つ挿入したとします。

マスタのB列に「規格」の列が挿入され、品名が3列目に移動した画面
品名は2列目から3列目に移った。

式に書いた 2 は、自動では変わりません。

伝票の品名の欄に「10kg」、単価の欄に「みかん」が入ってしまった画面。式は =VLOOKUP($A2,マスタ!$A:$D,2,FALSE) のまま
品名の欄に「10kg」。エラーは出ない。 単価の式があった列もずれて「みかん」になっている。
×

エラーが出ないのが、一番の問題

#N/A も #REF! も出ず、それらしい値が普通に表示されます。間違った品名の納品書が出続け、気づくのは取引先に指摘されたときです。

XLOOKUPは列を番号ではなく列そのもので指定するので、これが起きません。

VLOOKUPのまま安全に使う3つの対策

  1. マスタの列を挿入しないルールにする

    項目を足すときは一番右に足す。これだけで列番号はずれない。

  2. マスタのシートを保護する

    誤って列を挿入されるのを防ぐ。パスワードは無くてよい。

  3. 式のそばに「品名=2 / 単価=3」とメモを書く

    次に触る人が気づける。

Excelの校閲タブで「シートの保護」ボタンを赤い枠で示した画面
マスタのシートで「校閲」→「シートの保護」。

列番号を見出し名から自動で求める方法は INDEXとMATCHの使い方 で扱います。

FALSE を省略してはいけない

4つ目の FALSE は「完全一致」の指定です。省略すると近似一致になります。

FALSEを省略した =VLOOKUP($A3,マスタ!$A:$D,3) で、マスタに無い品番 H-150 なのに「ぶどう」と900が返ってしまった画面
マスタに無い H-150 なのに「ぶどう 900円」が返っている。エラーにならないのが怖いところ。

0 と書いても FALSE と同じ意味です。

よくあるつまずき

×

#N/A と出る

マスタに無い値を探しています。品番の後ろのスペース、全角と半角のまざり、数値と文字のまざりを確認してください。スペースは TRIM関数、未入力の行の #N/A を消すには IFERROR関数。

×

下にコピーしたら途中からおかしくなった

範囲を $ で固定していません。マスタ!A:C ではなく マスタ!$A:$C。セルを選んで F4 キーで切り替わります。

今日やってみること

  1. 自分のExcelで XLOOKUP が使えるか確認する

    =XL と打って候補に出るか。

  2. いま使っているVLOOKUPの式を1つ開く

    3つ目の数字と、4つ目の FALSE を確認する。

  3. FALSE が抜けていたら足す

    結果が変わったら、過去の資料も確認する。

  4. マスタの列順をメモに残す

次に読むとよい記事


「古いExcelのままでいいのか、入れ替えるべきなのか」という判断も含めてご相談いただけます。現状のヒアリングと課題整理は無料です。

#Excel#VLOOKUP#表引き

この記事について

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

無料で相談してみる

あわせて読みたい

記事一覧へ戻る