VLOOKUPの使い方を基本から解説|検索業務がラクになる関数

アイキャッチ:VLOOKUPは最後のFALSEを省略しない。NGは省略して表にないC003でC002の単価120が返る、OKはFALSEで#N/A 関数の使い方

「VLOOKUPで検索したら、ちゃんと数字が返ってきた。だからこの表は合っている」

経理担当から回ってきた請求一覧をチェックしていて、一瞬そう安心しかけた場面を思い浮かべてください。エラー表示はどこにもなく、すべてのセルにきれいに数字が入っている。ですが念のため元データと1件ずつ突き合わせてみると、3件だけ金額がズレている。原因は、参照先のテーブルに「取引先名」という列がふたつ紛れ込んでいたこと。片方は先週更新された最新版、もう片方は半年前の情報のまま残された古い列で、VLOOKUPは律儀に「一番左にある方」を拾い続けていたのです。

エラーが出ない、イコール正しい、ではありません。これはVLOOKUPを何年使っていても、忙しいときにふと忘れてしまう感覚だと思います。

目的別のExcel関数の選び方でも触れましたが、関数は「動いたかどうか」と「合っているかどうか」を別々に確認する習慣が必要です。VLOOKUPは、使い方さえ押さえてしまえば検索業務を大きくラクにしてくれる関数です。ただ、その恩恵を安心して受け取るには、基本の書き方と一緒に、つまずきやすいポイントも知っておく必要があります。今回はVLOOKUP関数の基本の書き方から、実務でよく起こる「動いているのに違う」を防ぐコツまで、順を追って整理していきます。

1. まず確認したいセルフチェック

本題に入る前に、次の5つを見てみてください。心当たりが2つ以上あれば、この記事はきっと役に立つはずです。

  • 検索方法の引数(TRUEかFALSEか)を、毎回意識して書いている自信がない
  • 参照先の表に、似たような名前の列が複数ないか確認したことがない
  • 検索値より左側の列を取ろうとして、うまくいかず諦めたことがある
  • 「列番号」を、テーブル範囲の中ではなくシート全体の列だと思い込んでいた時期がある
  • コピーしてきたデータをそのままVLOOKUPにかけて、原因不明の「#N/A」に悩まされたことがある

一つも当てはまらなければ、すでにVLOOKUPをかなり正確に扱えている証拠です。逆に複数当てはまった方は、次の章から自分に近いケースを探してみてください。

2. VLOOKUP関数の基本をおさらい

VLOOKUP(Vertical Lookup)は、表を縦方向に検索して、条件に一致した行から必要な値を取り出す関数です。書式は次の通りです。

=VLOOKUP(検索値, テーブル範囲, 列番号, 検索方法)
  • 検索値:探したいキーとなる値
  • テーブル範囲:検索対象となる表全体
  • 列番号:テーブル範囲の左端を1として数えたときの、取得したい列の位置
  • 検索方法:FALSE(完全一致)またはTRUE(近似一致)

たとえば、A列に商品コード、B列に商品名、C列に単価が入った表があるとします。商品コード「C003」の単価を調べたいなら、次のように書きます。

=VLOOKUP("C003", A2:C6, 3, FALSE)

この式は、A列で「C003」と完全に一致する行を探し、その行のテーブル内3列目(つまりC列)の値を返します。書式そのものはシンプルですが、この4つの引数それぞれに、実務でつまずきやすいポイントが隠れています。ここからは、実際によくある「動いているのに違う」を、原因別に見ていきます。

商品コード・商品名・単価の表で=VLOOKUP("C003",A2:C6,3,FALSE)がC003クリアファイルの単価80を返している画面
緑枠の行から、範囲の3列目にある単価80が返ります

3. ケースA:検索方法を省略して、知らないうちに近似一致になっていた

VLOOKUPの最後の引数「検索方法」は、省略すると自動的にTRUE(近似一致)として扱われます。これが実務でもっとも事故につながりやすいポイントです。

先ほどの商品コード表で、うっかり最後の引数を書き忘れたとします。

=VLOOKUP("C003", A2:C6, 3)

もし表がコード順に並んでいて、「C003」が範囲内に存在しなければ、VLOOKUPはエラーを返さずに「C003に近い、それより小さい最後のコード」の単価を返してしまいます。エラーが出ないぶん、見た目には何も問題がないように映るのが厄介なところです。近似一致そのものが悪いわけではなく、テストの点数から評価ランクを判定するような「範囲で区切りたい」場面では正しい使い方になります。ただし、商品コードや社員番号、取引先名のように「1対1で一致してほしい」検索では、迷わずFALSEを明記する。これをルール化しておくと、この種の事故はほぼ防げます。

C003が存在しない商品表で、検索方法にFALSEを指定した式は#N/A、省略した式はC002ボールペンの単価120を返している画面
省略すると、赤枠のC002の単価120がエラーなしで返ります

4. ケースB:参照先に「似た名前の列」が複数ある重複列事故

冒頭のエピソードがまさにこのケースです。参照先のテーブルに「取引先名」と「取引先名(新)」のような、意味の近い列が複数存在すると、VLOOKUPは常に一番左側で最初に見つかった列を検索対象にします。更新済みの列が右側に追加されていても、それより左に古い列が残っていれば、古い方のデータがずっと参照され続けてしまいます。

このパターンは同じ項目の列が複数あると集計がズレる原因と直し方でも詳しく取り上げていますが、VLOOKUP特有の怖さは「検索結果が返ってくること」そのものにあります。数字が入っている以上、パッと見ただけでは異常に気づけません。他の担当者が作った表を引き継いで使うときは、列の並び順を過信せず、まずヘッダー行をすべて確認してから数式を組み立てる。この一手間が、後から気づく大きなミスを防いでくれます。

5. ケースC:検索値より左の列を取ろうとして詰まっている

VLOOKUP関数には構造上の制約があり、検索値の列よりも左側にある列を検索結果として取得することはできません。たとえばB列を検索値にして、A列の値を取り出したい、というのはVLOOKUP単体では実現できない相談です。

この壁にぶつかったときによく使われるのが、INDEX関数とMATCH関数の組み合わせです。INDEX関数は「範囲の中の何行目・何列目」を指定して値を取り出す関数、MATCH関数は「指定した値が範囲の中で何番目にあるか」を調べる関数で、この2つを組み合わせると、検索値より左の列であっても自由に値を取り出せるようになります。書き方はVLOOKUPよりやや複雑になりますが、「VLOOKUPではできないことがある」という事実を知っておくだけでも、無理に列の並び順を変えようとして表を壊してしまう事態を避けられます。

6. ケースD:列番号を「シート全体」の列だと勘違いしている

VLOOKUP関数の「列番号」は、シート全体のA列・B列・C列を指すのではなく、指定したテーブル範囲の中で先頭から何列目か、を表しています。たとえばテーブル範囲をC列からスタートさせた場合、そのC列が「1列目」としてカウントされます。この感覚を持たずにシートの列番号をそのまま入力してしまうと、意図した列とはズレた値を拾ってしまいます。参照範囲を後から広げたり、列を挿入・削除したりしたときにも、この列番号は自動では追従してくれません。数式を修正した記憶がないのに結果が変わっていたら、範囲の左端がずれていないか、列番号がそのままになっていないか、両方を疑ってみてください。

7. ケースE:検索値の表記ゆれと、エラーとの付き合い方

検索値とテーブル側のデータで、全角と半角、大文字と小文字、見えない余分な空白といった表記の違いがあると、完全一致検索では「見つからない」と判定され、「#N/A」が返ってきます。他部署から共有されたファイルや、システムから書き出したデータを扱うときは、コピー段階でこうした表記ゆれが紛れ込んでいないか、あらかじめ確認しておくと余計なエラーに悩まされずに済みます。

「#N/A」が出続けて困っている場合は、VLOOKUPで#N/Aが消えないときの原因の探し方で原因別の見分け方をまとめているので、あわせて確認してみてください。

なお、資料としての見た目を整えるためにIFERROR関数でエラー表示を隠す方法もありますが、原因を確認しないまま隠してしまうと、本来気づくべき入力ミスまで見えなくなってしまいます。IFERROR関数の使い方で、隠す前に確認しておきたいポイントを整理していますので、こちらも参考になると思います。

8. 5つのケースをBefore/Afterで振り返る

ここまでのケースを、起きていることと直し方の対応表として整理しておきます。

ケース起きていること直し方
A:近似一致事故検索方法を省略し、近い値を拾ってしまう完全一致なら必ずFALSEを明記する
B:重複列事故似た名前の列のうち、左側の古い列を参照し続ける列名を一意にする、古い列を削除・統合する
C:左列参照の壁検索値より左の列を取得できず数式が組めないINDEX関数とMATCH関数の組み合わせを検討する
D:列番号の勘違いシート全体の列番号で数えて位置がズレるテーブル範囲の左端を1として数え直す
E:表記ゆれ・#N/A全角半角や空白の違いで「見つからない」扱いになるデータの入力元を確認し、表記を揃える

自分がどのケースに近いかを特定できると、直すべき場所が一気に絞り込めます。

9. 実務でよくある質問

Q. VLOOKUPとXLOOKUP、結局どちらを使えばいいですか?

A. 会社のExcelがバージョン2019以降、またはMicrosoft 365であればXLOOKUPを検討する価値があります。ただし、共有ファイルを古いバージョンの相手が開く可能性がある場合は、まだVLOOKUPの方が安全です。関数を選ぶ前に、ファイルを開く相手全員の利用環境を確認しておくと判断しやすくなります。

Q. 検索方法をTRUEにするのが正しい場面はありますか?

A. あります。たとえば点数から評価ランクを判定するときのように、検索値がテーブル側に存在しない可能性があり、なおかつ「その値以下でもっとも近いもの」を拾いたい場面です。この使い方をする場合は、テーブル側を検索値の昇順に並べ替えておくことが前提条件になります。並び替えを忘れると、近似一致は正しく機能しません。

Q. 列を1つ挿入しただけでVLOOKUPの結果がおかしくなりました。なぜですか?

A. 列番号を固定の数値で指定していると、テーブル範囲の途中に新しい列を挿入したとき、それより右側にあった列の位置が一つずつずれてしまいます。列番号を数値で直接書いている数式は、列の挿入・削除が入るたびに見直しが必要になる、という前提で扱っておくと事故を防げます。

Q. テーブル機能に変換すると、今まで組んでいた数式は壊れませんか?

A. 通常のセル範囲をテーブルに変換しても、VLOOKUPの数式自体がいきなり壊れることはありません。参照範囲を後からテーブル名を使った構造化参照に書き換えれば、行の追加にも自動で対応できるようになります。変換した直後は、代表的なセルで結果が変わっていないか一度確認しておくと安心です。

Q. 検索値は見つかっているのに、結果が「0」になります。エラーではないので原因がわかりません。

A. 取り出そうとしている列のセルが空欄になっていると、VLOOKUPは空欄をそのまま返さず「0」と表示します。#N/Aのようなエラーにはならないので、計算ミスなのか、もともと値が入っていないのかが見分けにくいのが厄介なところです。日付の列なら「1900/1/0」と表示され、さらに戸惑うことになります。

C004付箋の単価が空欄の表で、VLOOKUPのままの列は0、IFで空欄を返す列は空白になっている画面
赤枠の空欄が、VLOOKUPでは0として返ってきます

まず、参照先の表で該当する行を開いて、取り出す列が本当に空欄かどうかを確かめてください。空欄だった場合、「0」のまま集計に使ってしまうと、平均が下がったり、「単価0円」として扱われたりします。空欄は空欄のまま返したいときは、結果が空欄かどうかを先に判定します。

=IF(VLOOKUP(E2,$A$2:$C$100,3,FALSE)="","",VLOOKUP(E2,$A$2:$C$100,3,FALSE))

VLOOKUPを2回書くので式は長くなりますが、空欄なら空欄、値があればその値を返します。末尾に「&””」を付けて空欄を隠す書き方もよく見かけますが、この方法だと数字まで文字として返ってくるため、あとでSUMが合わなくなる原因になります。数字を取り出す列では使わないほうが安全です。XLOOKUPでも空欄は「0」で返ってくるので、同じ考え方で対処します。

Q. 「0」が返ってきたセルが、本当に0なのか空欄なのかを一覧で確かめる方法はありますか?

A. 隣の列に =COUNTBLANK(INDEX($C$2:$C$100,MATCH(E2,$A$2:$A$100,0))) と入れると、取り出し元のセルが空欄なら1、値が入っていれば0になります。結果が「0」で、この列が1になっている行は、参照先に値が入っていない行です。件数が多い表で、入力漏れをまとめて見つけたいときに使えます。

10. テーブル機能を使って事故を予防する

参照範囲をExcelの「テーブル」として設定しておくと、データが追加されたときに範囲が自動的に拡張されるため、参照範囲がズレる心配そのものが減ります。テーブル化した範囲はセル番地ではなく構造化参照という形式で扱われるので、数式を見返したときにも「何を参照しているか」が読み取りやすくなるという副次的なメリットもあります。行数がよく変動する表でVLOOKUPを使う場合は、この機能とあわせて使うことをおすすめします。

11. VLOOKUPで手が届かない場面には、XLOOKUPという選択肢もある

ここまで見てきたケースCのように、VLOOKUPには構造上の制約がいくつかあります。左方向への検索ができない、列を挿入すると列番号がズレる、といった弱点は、比較的新しい関数であるXLOOKUPを使うことで解消できる場合があります。

使えるExcelのバージョンに制限があるため必ずしも乗り換えが正解とは限りませんが、XLOOKUPに乗り換えるかを考えた記事で判断基準を整理していますので、環境が対応している方は目を通しておくと選択肢が広がります。

VLOOKUPで押さえておくこと

VLOOKUP関数は、正しく使えば検索作業を大幅に効率化してくれる、実務で欠かせない存在です。ただ、エラーが出ないからといって、結果が正しいとは限りません。今回整理した5つのケースを、最後にもう一度チェックリストとして振り返っておきます。

  • 完全一致で検索したいときは、検索方法にFALSEを明記しているか
  • 参照先の表に、似た名前の列が複数紛れ込んでいないか
  • 検索値より左の列を取ろうとして無理な数式を組んでいないか
  • 列番号は、テーブル範囲内の位置で数え直せているか
  • 検索値とテーブル側のデータに、表記ゆれが紛れ込んでいないか

「値が返ってきたから大丈夫」ではなく、「正しい値が返ってきているか」まで確認する。この一歩を意識するだけで、VLOOKUPまわりの事故はかなり防げるようになります。

実務では、ここで挙げた原因が1つではなく、いくつか重なって「なぜか合わない」状態になっていることも珍しくありません。原因が重なったときの切り分け方は、もう一歩踏み込みたい方向けにVLOOKUPの複合トラブル診断ガイド(note)にまとめています。

コメント

タイトルとURLをコピーしました