【Excel】大きな表からデータを転記・検索する！VLOOKUP関数の使い方

こんにちは、新人エンジニアの田口です。

Excelをデータベースとして活用する際、データの転記や検索を驚くほど楽にしてくれるVLOOKUP関数について解説していきます。

関数についての基本的な知識があることを前提としているので、引数や戻り値といった用語がわからない方は先に以下の記事をご覧ください。

【Excel】関数入門｜数値と文字列の違いや、代表的な関数について解説

VLOOKUP関数とは　

VLOOKUP関数とは、データを検索・転記する関数です。

たとえば、電話帳を使って電話番号を調べる場面をイメージしてください。

まずは上から順に目的の名前を探し、見つけたらその横に書かれている電話番号を調べるかと思います。

VLOOKUP関数は、このようなL字型に情報を検索する作業をExcelに代行してもらうための関数といえます。

VLOOKUP関数の動きを見る

書式の解説に入る前に、実際にこの関数がどんな場面でどのように働くのかを見てみます。

下の図のように、「商品別売上表」と商品コードと商品名を対応させた「商品一覧」があるとします。

この時、商品コードに対応する商品名を売上表に入力したいのですが、いちいち一覧表から探して入力するのは面倒です。

そこで、VLOOKUP関数を使えば、一瞬でほしいデータを探し出して入力することがで来ます。

例：コードに対応する商品を転記する

この時入力したVLOOKUP関数も、次のようにL字型に検索をして転記しています。

引数が多くて大変に見えますが、注意することはそれほど多くはありません。

次はこの関数の書き方について解説します。

VLOOKUP関数の書式

VLOOKUP関数：データを縦方向に検索する

=VLOOKUP(検索値,範囲,列番号,検索の型)

検索値：検索したい「値」を指定

範囲　：値を検索して取り出す「セル範囲」を指定

列番号：取り出したい値が「範囲」の何列目にあるかを指定

検索の型：２通りの検索方法　TRUE(部分一致)かFALSE(完全一致)かを指定

これまで述べてきたような「名前を電話帳から探して電話番号を調べる」「指定したコードを一覧から探して、対応する商品名を調べる」という２つの例はいずれも、「〇〇から△△を探して、対応する□□を調べる」という形になっています。

そしてこの穴あき部分が最初の3つの引数、「検索値」、「範囲」、「列番号」に対応しています。

第4引数の「検索の型」については、特別な理由がない限りFALSEを指定してください。

別の記事にて、TRUEを使うケースについても解説します。

FALSE:「検索値」に一致する値のみを取得

TRUE:「検索値」に一致する値を検索する。ただし、一致する値がない場合は「検索値未満」で一番近い値を検索する。

VLOOKUP関数の注意点、ルール

VLOOKUP関数の記述において、特に注意が必要な点について述べておきます。

引数の注意点

引数の指定を誤ると、データの転記や検索がうまくいかないことがあります。以下ではよくある間違いや、注意することについて解説します。

▼　列はシート基準ではなく、指定した範囲を基準とする

以下はよくある勘違いの例です。

先述したコードに対応する商品を転記する例を使って確認します。

取り出したい値である商品名列はC列にあります。C列はシート全体から見て、3列目です。

そこで、VLOOKUP関数の「列番号」は3と指定しました。すると下記のようにエラーとなります。

第3引数の「列番号」は、第2引数で指定した範囲の何列目のデータを転記するかを指定することを覚えておきましょう。

▼　セル範囲は、取り出す値を探す範囲も指定する

同じくコードに対応する商品を転記する例を使って、よくある勘違いの例を確認します。

検索値B4の値であるA001を検索します。

商品コードを探すために、第2引数にB列のみを範囲指定しました。

すると以下のようにエラーが発生します。

第2引数の「範囲」には、第3引数で指定して取り出す値が含まれている列も指定しましょう。

▼　「検索値」は「範囲」以外のセルを指定する

同じくコードに対応する商品を転記する例を使って、よくある間違い例を確認します。

今回は失敗例を表示するためあえて商品別売り上げ表の順番を入れ替えています。

以下の表には、検索値で指定したい商品コードが2か所にあります。

どちらを指定したらよいかわからなかったので、商品一覧にある商品コードを指定することにしました。

すると以下のようにエラーが発生します。

売上表では、本来「りんご」であるはずのA001が「みかん」になっていたり、C6セルとC7セルではエラー値が返されています。

このように正しい検索結果が得られない可能性があるので、第1引数の「検索値」と第2引数の「範囲」で、同一のセルを指定することがないようにしてください。

参照表のルール

参照表とは一般的な用語ではありませんが、ここでは「範囲」で指定する表という意で使用します。

先ほどの例でいうと、「商品一覧」の表を指します。

参照する表は別シートで管理する

たとえば先ほどの表の商品別売り上げ表に行や列を追加・削除を行うとします。

この時同じシートに参照表があると、参照表にも行や列を追加・削除が反映されて、表の内容が壊れてしまう恐れがあります。

このような場合でも、参照表を別シートで管理しておけば、どんなに入力用の表に修正が加わっても影響が出なくなります。

VLOOKUP関数の活用例

VLOOKUP関数の様々な使い方について解説しています。

【Excel】VLOOKUPで検索|検索値に対応する二つの値を表示させる

【Excel】IF関数の代わりにVLOOKUP関数を使った条件分岐のやり方。

【Excel】VLOOKUP関数で「TRUE」を使って近似値検索を行うパターンの動きを確認する