「VLOOKUPを入れたら、ちゃんと値が返ってきた。だから合っている」
そう思って安心していたら、実は違うデータを拾っていた——そんな経験、私にもあります。VLOOKUPは、エラーさえ出なければ「正しく検索できている」ように見えてしまう関数です。ですが、その裏側には初心者が気づきにくい落とし穴がいくつも隠れています。値が返ってきたことと、正しい値が返ってきたことは、実はまったく別の話なのです。【関数の使い方シリーズ①】名前は知ってるのに使えない…関数、なんとなくで選んでいませんか？でも少し触れましたが、参照先に似た意味の列が複数あると、VLOOKUPは意図しない列を拾ってしまうことがあります。今回は「関数の使い方シリーズ」第4回として、VLOOKUP関数の基本から、実務でつまずきやすいポイントまで一通り整理していきます。
1. VLOOKUP関数とは
VLOOKUP（Vertical Lookup）関数は、指定したテーブルの中から特定の値を検索し、対応するデータを取得するための関数です。たとえば、顧客名から売上金額を検索する、商品コードから商品名を検索する、といった場面でよく使われます。名前の通り、表を縦方向に検索していくイメージを持っておくと、動作原理をつかみやすくなります。
2. VLOOKUP関数の書式
VLOOKUP関数の書式は次の通りです。
=VLOOKUP(検索値, テーブル範囲, 列番号, 検索方法)検索値：検索したい値を指定します。テーブル範囲：検索対象のテーブル範囲を指定します。列番号：検索結果として返したい列が、テーブル範囲の左から何列目にあるかを指定します。検索方法：TRUE（近似一致）またはFALSE（完全一致）を指定します。
書式そのものはシンプルですが、この4つの引数それぞれに、初心者がつまずきやすいポイントがあります。
3. 完全一致検索の基本
以下のようなテーブルがあるとします。
顧客名「B社」の売上金額を検索する場合、数式は次のようになります。
=VLOOKUP("B社", A1:B3, 2, FALSE)この式は「2000」を返します。ここで意識しておきたいのが、最後の引数「FALSE」です。完全に一致する値だけを探したい場合は、必ずFALSEを指定します。
4. 近似一致の危険性
近似一致（TRUE）を指定すると、検索値に完全に一致するデータがなくても、それに近い値を検索してくれます。価格帯の判定など、あえて近似一致を使いたい場面もあるのですが、実務で本当に注意したいのは「検索方法の引数を省略したとき」です。
VLOOKUP関数は、検索方法の引数を省略すると、自動的にTRUE（近似一致）として扱われます。完全一致のつもりでFALSEを書き忘れると、検索値と完全には一致しない、近い値のデータを拾ってしまうことがあります。しかも、テーブルが検索値の昇順に並んでいない場合は、正しく機能せず、明らかに違う値が返ってくることもあります。エラーが出ないぶん、この間違いには本当に気づきにくいので、迷ったら基本的にFALSEを指定する、というルールにしておくと安心です。
たとえば、商品コードをもとに単価を検索する数式で、うっかり最後の引数を省略してしまったとします。商品コードが完全一致しなくても、近い順番のコードの単価が返ってきてしまい、金額の間違った請求書ができあがってしまう——といったことも、現場では実際に起こり得ます。近似一致を使う正当な場面は「点数から評価ランクを判定する」といった、値の範囲で区切りたいケースに限られる、と覚えておくと判断しやすくなります。
5. VLOOKUP関数の限界：検索値より左の列は参照できない
VLOOKUP関数には、構造上の制約があります。それは、検索値の列よりも左側にある列を、検索結果として取得できないという点です。たとえば、B列を検索値にして、A列のデータを取得したい、ということはVLOOKUP関数単体ではできません。
このようなケースでは、列の順番を入れ替えるか、INDEX関数とMATCH関数を組み合わせる方法が使われます。INDEX関数は「範囲の中の何行目・何列目」を指定して値を取り出す関数、MATCH関数は「指定した値が範囲の中で何番目にあるか」を調べる関数です。この2つを組み合わせることで、検索値より左の列であっても、自由に値を取り出せるようになります。INDEXとMATCHの組み合わせは、VLOOKUPよりも柔軟に検索できる反面、書き方がやや複雑になるため、まずは今回VLOOKUPの限界を知っておくだけでも十分です。関数の使い方シリーズでは、INDEX・MATCHについても改めて取り上げる予定です。
6. 参照範囲に重複した列があると起こる事故
【Excel初心者がよくハマる】集計がうまくいかない理由㉖｜「同じ項目が複数列ある」は、VLOOKUPを使う上でとくに注意したいポイントです。参照先のテーブルに「名前」と「名前2」のような、似た意味を持つ列が複数存在すると、VLOOKUPは常に一番左側で最初に見つかった列を優先して検索します。更新されているはずの列があっても、それより左に古い列が残っていると、古いデータの方が参照されてしまいます。検索結果が合っているかどうかを確認するときは、単に「値が返ってきたか」だけでなく、「参照している列が正しいか」まで目視で確認しておくと安心です。とくに、他の担当者が作った表を引き継いで使う場合は、列の並び順を過信せず、一度ヘッダー行をすべて確認してから数式を組み立てることをおすすめします。
7. テーブル機能を使った効率的な参照方法
参照範囲を「テーブル」として設定しておくと、データが追加されたときに範囲が自動的に拡張されるため、VLOOKUPの参照範囲がズレる心配が少なくなります。テーブル化した範囲は、セル番地の代わりに構造化参照という形式で扱われるため、数式そのものも読みやすくなるというメリットがあります。範囲がよく変動する表でVLOOKUPを使う場合は、テーブル機能とあわせて使うことをおすすめします。
8. VLOOKUP関数でよくある間違い
8-1. 列番号を、テーブル全体の列番号だと勘違いする
VLOOKUP関数の「列番号」は、シート全体の列番号ではなく、指定したテーブル範囲の中で何列目か、を表しています。テーブル範囲の先頭列を1として数えるため、参照範囲の取り方によって、同じ列を指しているつもりでも列番号がズレてしまうことがあります。
8-2. 検索値が見つからないとエラーになる
検索値がテーブル範囲の中に存在しない場合、VLOOKUP関数は「#N/A」というエラーを返します。このエラー自体は「見つからなかった」ことを正しく教えてくれているのですが、表の見た目としてはエラー表示が目立ってしまいます。IFERROR関数と組み合わせて、見つからなかった場合は空欄や「該当なし」を表示させておくと、資料としても見やすくなります。
8-3. 参照範囲を固定していないと、行が増えたときにズレる
参照範囲をセル番地でそのまま指定していると、表にデータを追加したときに、参照範囲がそこまで自動で広がってくれません。範囲を絶対参照（$を付けた指定）にしておく、あるいは前述のテーブル機能を使っておくことで、行が増えても参照範囲が追従してくれるようになります。コピー&ペーストで数式を他のセルに広げる際にも、絶対参照にしておかないと、参照先がずれて意図しない結果になることがあるため、あわせて注意しておきたいポイントです。
8-4. 検索値の表記ゆれに気づかない
検索値とテーブル側のデータで、全角と半角、大文字と小文字、余分な空白など、見た目には気づきにくい表記の違いがあると、完全一致検索では「見つからない」と判定されてしまいます。コピーしてきたデータや、他部署から共有されたファイルを扱う際は、表記ゆれが紛れ込んでいないか、あらかじめ確認しておくと余計なエラーに悩まされずに済みます。
まとめ
VLOOKUP関数は、正しく使えば検索作業を大幅に効率化してくれる、実務で欠かせない関数です。ただ、エラーが出ないからといって、結果が正しいとは限りません。検索方法の指定、参照範囲の重複列、列番号の数え方、表記ゆれ。こうしたポイントを一つずつ確認する習慣をつけておくと、VLOOKUPの検索結果に対する信頼度がぐっと上がります。
「値が返ってきたから大丈夫」ではなく、「正しい値が返ってきているか」まで確認する。この一歩を意識するだけで、VLOOKUPまわりの事故はかなり防げるようになります。関数の使い方シリーズでは、他の関数についても実務目線で整理していますので、あわせてご覧ください。
コメント