「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つの引数それぞれに、実務でつまずきやすいポイントが隠れています。ここからは、実際によくある「動いているのに違う」を、原因別に見ていきます。
3. ケースA：検索方法を省略して、知らないうちに近似一致になっていた
VLOOKUPの最後の引数「検索方法」は、省略すると自動的にTRUE（近似一致）として扱われます。これが実務でもっとも事故につながりやすいポイントです。
先ほどの商品コード表で、うっかり最後の引数を書き忘れたとします。
=VLOOKUP("C003", A2:C6, 3)もし表がコード順に並んでいて、「C003」が範囲内に存在しなければ、VLOOKUPはエラーを返さずに「C003に近い、それより小さい最後のコード」の単価を返してしまいます。エラーが出ないぶん、見た目には何も問題がないように映るのが厄介なところです。近似一致そのものが悪いわけではなく、テストの点数から評価ランクを判定するような「範囲で区切りたい」場面では正しい使い方になります。ただし、商品コードや社員番号、取引先名のように「1対1で一致してほしい」検索では、迷わずFALSEを明記する。これをルール化しておくと、この種の事故はほぼ防げます。
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が消えない原因と対処法｜実務で多い3つのミスで原因別の見分け方をまとめているので、あわせて確認してみてください。
なお、資料としての見た目を整えるために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の数式自体がいきなり壊れることはありません。参照範囲を後からテーブル名を使った構造化参照に書き換えれば、行の追加にも自動で対応できるようになります。変換した直後は、代表的なセルで結果が変わっていないか一度確認しておくと安心です。
10. テーブル機能を使って事故を予防する
参照範囲をExcelの「テーブル」として設定しておくと、データが追加されたときに範囲が自動的に拡張されるため、参照範囲がズレる心配そのものが減ります。テーブル化した範囲はセル番地ではなく構造化参照という形式で扱われるので、数式を見返したときにも「何を参照しているか」が読み取りやすくなるという副次的なメリットもあります。行数がよく変動する表でVLOOKUPを使う場合は、この機能とあわせて使うことをおすすめします。
11. VLOOKUPで手が届かない場面には、XLOOKUPという選択肢もある
ここまで見てきたケースCのように、VLOOKUPには構造上の制約がいくつかあります。左方向への検索ができない、列を挿入すると列番号がズレる、といった弱点は、比較的新しい関数であるXLOOKUPを使うことで解消できる場合があります。
使えるExcelのバージョンに制限があるため必ずしも乗り換えが正解とは限りませんが、XLOOKUP関数の使い方をわかりやすく解説｜VLOOKUPとの違いと乗り換えの判断基準で判断基準を整理していますので、環境が対応している方は目を通しておくと選択肢が広がります。
まとめ
VLOOKUP関数は、正しく使えば検索作業を大幅に効率化してくれる、実務で欠かせない存在です。ただ、エラーが出ないからといって、結果が正しいとは限りません。今回整理した5つのケースを、最後にもう一度チェックリストとして振り返っておきます。
- 完全一致で検索したいときは、検索方法にFALSEを明記しているか
- 参照先の表に、似た名前の列が複数紛れ込んでいないか
- 検索値より左の列を取ろうとして無理な数式を組んでいないか
- 列番号は、テーブル範囲内の位置で数え直せているか
- 検索値とテーブル側のデータに、表記ゆれが紛れ込んでいないか
「値が返ってきたから大丈夫」ではなく、「正しい値が返ってきているか」まで確認する。この一歩を意識するだけで、VLOOKUPまわりの事故はかなり防げるようになります。
今回は基本の使い方と、代表的な5つの事故パターンを中心に整理しました。ただ実務では、これらの原因が単独ではなく複数重なって「なぜか合わない」状態になっているケースも珍しくありません。そうした一段階複雑な複合トラブルの切り分け方は、noteで診断ガイドとしてまとめています。もう一歩踏み込んで確認しておきたい方は、あわせてご覧ください。
→ VLOOKUPが「動いているのに違う」を見抜く複合トラブル診断ガイド（note）
コメント