ExcelのVLOOKUP関数の使い方|別シートの参照方法も解説

ExcelのVLOOKUP関数の使い方

Excel(エクセル)のVLOOKUP関数の使い方をご紹介します。VLOOKUP関数は利用頻度の高い関数で、求人情報でも「VLOOKUP関数が使えればOK」といった記載が見られるなど様々なビジネスシーンで活用されています。

こちらの記事では別シートの参照方法も詳しく解説していきますので、ぜひ参考にしながらVLOOKUP関数の使い方を学んでみてください。

ExcelのVLOOKUP関数とは

Excel(エクセル)のVLOOKUP関数とは、表の縦方向にデータを検索し条件に一致した値を取り出してくれる関数です。 VLOOKUPのVは「垂直」という意味の「Vertical」からきており 、「探す」という意味の「LOOKUP」とあわせて「垂直に探す」ということになります。

大量のデータの中から探したい値を機械的に探すことができるため、時間の短縮になり単純ミスも抑えられるでしょう。基礎的な使い方と応用的な使い方を紹介していきますので、用途に合わせて参考にしてください。

ExcelのVLOOKUP関数の構文

Excel(エクセル)のVLOOKUP関数の構文は「=VLOOKUP(“検索値”, 検索範囲, 列番号, [検索方法])」です。引数は4つと少し多めになっていますが、それぞれ詳しくみていきましょう。

  • 第1引数:検索値
  • 第2引数:範囲
  • 第3引数:列番号
  • 第4引数:検索の型(FALSE/TRUE)

第1引数:検索値

まず第1引数である「検索値」です。こちらは検索したい値を「”」「”」で囲んで入力します。「ノート」といったような文字列でも数値でも構いません。こちらに入力した値を元に検索することになるので間違えないようにしましょう。

ExcelのVLOOKUP関数の構文

またこの引数にはセル参照を使うことも可能です。その場合は「”」「”」なしで入力しましょう。

ExcelのVLOOKUP関数の構文

第2引数:範囲

次に第2引数である「範囲」です。こちらは検索するための元データの範囲を指定します。検索したい範囲のデータだけでなく、検索値がある列や取り出したいデータがある列も全て含めるようにしましょう。

こちらは別のシートにある表も範囲として設定することができます。詳しくはこの記事の後半に書かれている「応用編」を参照ください。

ExcelのVLOOKUP関数の構文

第3引数:列番号

第3引数は「列番号」です。こちらは指定した検索値と同じものが見つかった場合、どの列にあるデータを取得するのかを指定します。元データの表の何列目にあるか、数字で指定します。

Excelのシートの列番号ではなく、あくまで第2引数で指定した範囲の中での列番号となりますのでご注意ください。例では取り出したい値が表の中で2列目にあたるので、「2」と入力しています。

ExcelのVLOOKUP関数の構文

第4引数:検索の型(FALSE/TRUE)

最後は第4引数である「検索の型」になります。検索値と完全に一致しない場合にどのような処理をするかを指定します。何も指定しないと自動的にTRUEが適用されます。

  • FALSE:完全に一致するデータのみを検索範囲から探し、なければ「#N/A」と表示します。
  • TRUE:検索値に一番近いデータを範囲から取り出します。

第1〜4までの引数を全て合わせると、セルには「=VLOOKUP(B3,B6:C10,2,FALSE)」と入力されています。

ExcelのVLOOKUP関数の構文

ExcelのVLOOKUP関数の使い方【基本編】

Excel(エクセル)のVLOOKUP関数の基本的な使い方をご紹介します。
ここでは例として、ID、出身県、年齢が記載された表を使い、検索したIDの人の出身県と年齢を表示させる関数を作成していきます。

STEP.1
まず元データとして画像のような表を作成します。検索値であるIDの項が必ず一番左の列にくるようにしてください。

ExcelのVLOOKUP関数の使い方【基本編】

STEP.2
次に、「検索したいデータ」を入力する欄と「そのIDを元に抽出されたデータ」を表示する欄を作成します。例では「ID」の入力欄と「検索したIDの出身県」「検索したIDの出身県年齢」の表示欄を作成しています。

ExcelのVLOOKUP関数の使い方【基本編】

STEP.3
「検索したIDの出身県」の表示欄のセル内にVLOOKUP関数を入力していきます。第1引数に「検索値」を入力します。例では先程作成した検索したいIDを入力するための欄のセルを参照しましょう。

ExcelのVLOOKUP関数の使い方【基本編】

STEP.4
第2引数には表の範囲を入力します。例では「$B$4:$D$12」と絶対参照で記載しています。絶対参照で記載する場合は、範囲をマウスで指定した後にアルファベットと数字の前にそれぞれ「$」マークを挿入しましょう。

今回は絶対参照でも相対参照でも問題ありませんが、コピー&ペーストで関数を使い回す時に相対参照を使用するとエラーが発生しやすくなります。絶対参照で書く癖をつけるとよいでしょう。詳しくは記事の最後に載せているエラーリストをご確認ください。

ExcelのVLOOKUP関数の使い方【基本編】

STEP.5
第3引数には抽出したいデータがある列を指定します。例では出身県のあるデータは2列目となっていますので「2」と入力しましょう。

ExcelのVLOOKUP関数の使い方【基本編】

STEP.6
第4引数は検索の型です。今回は完全に一致した場合のみデータを抽出したいので「FALSE」と入れて下さい。全て入力し終わったら「Enter」キーを押しましょう。

STEP3~6をまとめると、セルには「=VLOOKUP(F3,$B$4:$D$12,2,FALSE)」と入力されることになります。

ExcelのVLOOKUP関数の使い方【基本編】

STEP.7
実際にID入力欄にいずれかのIDを入力してみましょう。表から正しい値が取得されていればOKです。

ExcelのVLOOKUP関数の使い方【基本編】

STEP.8
年齢の方も同様に関数を入力しましょう。先程作成した関数の第3引数を3と変更すればOKです。以上で操作は完了です。

ExcelのVLOOKUP関数の使い方【基本編】

ExcelのVLOOKUP関数の使い方【応用編】

次はExcel(エクセル)の応用的なVLOOKUP関数の使い方を説明していきます。少し複雑なデータ構造の場合、知っていると役に立つ知識ですのでぜひ参考にしてみてください。

  • ExcelのVLOOKUP関数で別シートを参照する方法
  • ExcelのVLOOKUP関数で住所から郵便番号を取得する方法

ExcelのVLOOKUP関数で別シートを参照する方法

Excel(エクセル)のVLOOKUP関数で別シートを参照する方法です。元データとなる表が別シートにある場合にこの方法を使います。

STEP.1
まず、基本となるシートに「検索したいデータ」を入力する欄と「そのIDを元に抽出されたデータ」を表示する欄を作成します。例では「ID」の入力欄と「検索したIDの出身県」の表示欄を作成しています。

ExcelのVLOOKUP関数で別シートを参照する方法

STEP.2
別のシートに検索対象となる元データの表を用意しましょう。

ExcelのVLOOKUP関数で別シートを参照する方法

STEP.3
引数の設定の仕方は基本編と同じですが、第2引数の範囲を入力するとき参照したいシートの名前も挿入するのがポイントです。

基本編ではVLOOKUP関数を入力するセルと参照する表が同じシート上にあったためセルの範囲「$B$4:$D$12」のみでOKですが、今回は異なるシートに表が存在するため「Sheet2!$B$4:$D$12」というようにセルの前にシート名を追加すればOKです。シート名の後に「!」を付けるのを忘れないようにしましょう。

ExcelのVLOOKUP関数で別シートを参照する方法

STEP.4
これで他のシートのデータを検索して値を抽出する関数が出来上がりました。以上で操作は完了です。

ExcelのVLOOKUP関数で別シートを参照する方法

ExcelのVLOOKUP関数で住所から郵便番号を取得する方法

次は日本郵便で配布されている郵便番号データを用いて住所から郵便番号を取得する方法を説明していきます。

STEP.1
まず日本郵便のサイトにアクセスし、郵便番号データのファイルをダウンロードしましょう。画像の矢印の所を左クリックしてください。

ExcelのVLOOKUP関数で住所から郵便番号を取得する方法

STEP.2
ダウンロードが終わったらファイルの上で右クリックし、「すべて展開(T)」を選んでください。csvファイルが出てくるのでファイルを開きましょう。

ExcelのVLOOKUP関数で住所から郵便番号を取得する方法

STEP.3
こちらのcsvファイルにある表を、VLOOKUP関数が使えるように修正します。一列目に新しい行を加え、「=C1&D1&E1」と入力しましょう。これは分離されている住所を繋げて表示させるための作業です。

入力後「Enter」キーを押し、セルの右下にマウスを移動させるとカーソルが黒い十字マークに変化します。この時ダブルクリックすると、一番下の行まで数式をコピーしてくれます。

ExcelのVLOOKUP関数で住所から郵便番号を取得する方法

STEP.4
次に郵便番号を検索したい住所が記載されているファイルを開きましょう。先程ダウンロードした郵便番号のcsvファイルを、新しいシートとしてドラッグ&ドロップで追加してください。

ExcelのVLOOKUP関数で住所から郵便番号を取得する方法

STEP.5
元のシートに戻り、郵便番号を表示させたいセルに「=VLOOKUP(C3,KEN_ALL_ROME!$A$1:$H$124523,2,FALSE)」と入力しましょう。範囲は絶対参照で書くようにするのがポイントです。

ExcelのVLOOKUP関数で住所から郵便番号を取得する方法

STEP.6
問題なく郵便番号が取得できていたら、残りのセルもオートフィル機能を使って関数をコピーしましょう。以上で操作は完了です。

ExcelのVLOOKUP関数で住所から郵便番号を取得する方法

ExcelのVLOOKUP関数でよくあるエラーと確認ポイント

Excel(エクセル)のVLOOKUP関数でよくあるエラーと確認ポイントについてご紹介します。

【よくあるエラー一覧】

  1. 検索値が先頭列になく検索対象が見つからない
  2. 範囲を相対参照で記述してしまい、参照していた表の範囲がズレてしまう
  3. 検索値が文字列の場合、表記ゆれで上手く検索できない
  4. 第4引数を指定していないため、検索結果がおかしい

【各エラーの対応方法と確認ポイント】

  1. 検索値は必ず表の一番左側、先頭列におくようにしましょう。慣れてくると忘れがちなポイントですので注意してください。
  2. この記事の本文中でも何回か出てきましたが、第2引数を相対参照で書いてしまうとコピー&ペーストで他のセルで使い回した時に表の範囲がズレてしまいます。参照する表のアドレスは固定のため、必ず絶対参照で記載するようにしましょう。
  3. VLOOKUP関数は検索値と完全に一致する時に値を取得することができます。例えば全角・半角が異なっていたり、文字列の前後に空白があったりと微妙な違いで上手く検索できていない場合があります。
  4. 第4引数を指定しないと、自動的に「TRUE」で設定されてしまいます。完全に一致する値がない場合は近似値を取得していまいますので、思い通りの結果が得られないことがあります。

ExcelのVLOOKUP関数について改めておさらい

Excel(エクセル)のVLOOKUP関数は引数が4つと多めで、元データの形式にもルールがあるため難しいと感じている方も多いかもしれません。

そのような場合には今回ご紹介した簡単な例題などを参考に取り組んでみましょう。どうしてもエラーがでたり正しい値が取得できなかったりする場合はエラーリストを確認し、当てはまる原因がないか検討してみてください。