VLOOKUP関数は数あるExcel関数の中でも、出現頻度の高い関数の1つです。
言葉で説明してもなかなか理解が難しい関数でもあるので、この記事ではなるべく具体例を示しながら、はじめてVLOOKUP関数のことを勉強する方でもわかりやすく機能を解説しています。
記事の後半では、そこそこExcelを使いこなせている方でも中々知らない「上位1%のVLOOKUP関数の応用技術」も解説します。
ExcelCamp講師 野沢先生関数の入力順序は覚える必要ありません。
ガイドが出てきてくれるので、ガイドを見て思い出せるようになればOKです。
この記事に重要ポイントをすべてまとめたので、もし不安な方は記事をブックマーク頂くと便利です。
VLOOKUP関数とはなにか
VLOOKUP関数とは、あるデータ(検索値)を元に、欲しいデータを自動で探してきてくれる関数です。
ExcelCamp講師 野沢先生Lookupは英語で「探す」、Vは「Vertical(垂直方向)」という意味です。
言葉で定義を説明してもイメージがつきにくいので、以下具体的にVLOOKUP関数を書いて説明していきます。
ある架空の果物屋さんのデータがあるとします。
上記のリストから果物名を「検索値」として、単価と原価をVLOOKUP関数で自動的に表示させる方法を解説します。
先に結論からお伝えすると、以下のようにVLOOKUP関数を書くとうまくいきます。
「単価」のVLOOKUP関数を例にとって、それぞれ解説します。
検索値：F4
まず、検索値にはF4セルを指定しています。
F4セルには果物名が入っており、「この果物名の情報を取ってきてね」という意味になります。
範囲：$B$4:$D$8
検索値だけでは「どこから情報を取ってくればいいのか」がExcelにわかりません。
そこで次に、情報を取ってくる「範囲」を指定してあげる必要があります。
※範囲は必ずF4キーで「絶対参照」の指定をしてください。
ExcelCamp講師 野沢先生$B$4:$D$8の範囲には、果物、単価、原価がまとまっている表全体を指定しています。
この時範囲の指定の仕方に注意点があり、検索値の情報がある列を一番左に指定する必要があります。
今回でいえば検索値である「りんご」がある列はB列なので、B列が一番左に来るように範囲を指定する必要があります。
例えば以下のような範囲の指定の仕方はいずれも間違いです。よくあるエラー例なので、すべて確認してください。
ExcelCamp講師 野沢先生また、範囲はF4キーで絶対参照に指定してください。これは例外なくVLOOKUP関数の範囲を書く際に「必ず」行う操作です。
列番号：2
検索値と範囲を指定したことで、「何をもとに(検索値=りんご)、どこから探すのか($B$4:$D$8から探す)」ことをExcelに伝えています。
次に指定するのは「何を探すのか」です。
今回は「単価」のデータを探したいので、列番号は「2」と入力します。
ExcelCamp講師 野沢先生列番号は、選んだ範囲の左列から順に1,2,3,…と数えます。
「単価」の情報は左から2番目の列にあるので「2」と記入します。
列番号はあくまで選んだ範囲の左端の列から「1」と数えるので、A列目から「1,2,3,…」と数えるわけではありません。
列番号の数え方もよく間違えている方が多いので、注意が必要です。
検索方法：0
最後に「検索方法」を指定します。
VLOOKUP関数の検索方法には「完全一致」と「近似一致」の2種類があり、それぞれ以下のように記載します。
| 検索方法 | 書き方 |
|---|---|
| 完全一致 | 「0」「false」 |
| 近似一致 | 「1」「true」 |
ExcelCamp講師 野沢先生記事冒頭にも書きましたが、検索方法はほぼ「完全一致」しか使わないので、何も考えず「0」と書いてしまって大丈夫です。
「false」と書いても同じ結果になりますが、「0」のほうが圧倒的に早く入力できるので「0」と書くことを推奨しております。
以上で検索値、範囲、列番号、検索方法の指定が全て終わりました。Enterキーを押して式を確定させると、りんごの原価が自動で表示されます。
ためしに検索値を他の果物に変えてみます。VLOOKUP関数が正しく書けていれば「単価」が自動で書き換わります。
ExcelCamp講師 野沢先生以上が基本的なVLOOKUP関数の書き方です。原価を出すには、列番号を「3」に変えるだけです。
「近似一致」を使う場面もあることにはありますが、1万回VLOOKUPを書いて数回あるかどうかの頻度です。
是非まずは王道の「完全一致」の使い方をマスターしてください。
VLOOKUP関数を書くときのポイント
ポイント1：範囲を間違えない
VLOOKUP関数を書くにあたって、「範囲」の指定がもっとも間違えやすいポイントです。
繰り返しとなりますが、「範囲の一番左の列に検索値があるように選択する」というルールを忘れずに確認して下さい。
ExcelCamp講師 野沢先生また、必ずF4キーで絶対参照に指定することも忘れないでください。
※PCによってはFnキーを同時に押す必要があります。
ポイント2：=vl+Tabキーで最速で記述する
=vlookup(といちいち手入力するのは非効率です。
=vlとだけ書けば関数候補がVLOOKUP関数のみに絞られるので、すかさずTabキーを押して記入してください。
ポイント3：エラーが出たらF2キーで関数の中身を確認する
関数を書いたのにエラーが出てしまったときには、すかさずF2キーで書かれた関数の中身を見てエラーの理由を確認してください。
※PCによっては、Fnキーを同時に押す必要があります。
セル参照されている場所は色分けされてわかりやすく表示されるので、「検索値」「範囲」「列番号」「検索方法」がそれぞれ正しく入力されているか確認します。
ExcelCamp講師 野沢先生上の例では列番号が「2」ではなく「4」と書かれていました。
範囲は合計3列しか無いので、「4」ではエラーが出ていたわけです。
このように、エラーがあったときには必ずF2で中身を確認しに行くことを癖づけてください。
マスタデータが別シートにある際の書き方
VLOOKUP関数を書く場合、ほとんどがマスターデータは別シートにあると思います。
ExcelCamp講師 野沢先生関数の書き方自体は全く同じなので特別な操作は必要ありませんが、関数がやや複雑な表示になるため、混乱しないように一応解説します。
マスタデータが別シートにある場合、範囲選択の際にシートを移動するショートカットを使います。
シートを移動するショートカット
Ctrl&Fn&PgUp/PgDn
範囲選択の際に、ショートカットでシートを移動します。
その際、シートを移動したセルの表記を見ると「マスタデータ!A2」のように書かれているはずです。
これはシート名が違うときのExcel独特の表記で、意味は以下のとおりです。
なので、マスタデータが別シートにある場合のVLOOKUP関数の表記は以下のようになります。
ExcelCamp講師 野沢先生知ってしまえばなんてことのない違いですが、はじめて見た方は不安になりますよね。
決してエラーではありませんのでご安心ください。
エラー表示を変更するIFERROR関数の活用法
VLOOKUP関数が正しく書けていても、エラーになるケースがあります。
代表例は、マスタデータに検索値が無いケースです。
このような場合マスタデータにデータを追加する必要があるのですが、実践の場ではなかなかすぐに追加ができないこともあります。
そこでエラー表示を「-」など、別の表記に変更できるIFERROR関数をよく使います。
IFERROR関数の書き方
=IFERROR(値,エラーの場合の値)
ExcelCamp講師 野沢先生IF(もし)+ERROR(エラー)でIFERRORです。
「もしエラーが出たら」という条件を指定できる関数です。
ためしにエラーが出る場合は「-」と表示させる指定をIFERROR関数で書いてみます。
まずは普通にVLOOKUP関数を書きます。
マスタデータに「いちご」は存在しないためエラーが出ました。
この状態でF2キーでセルの中身を開き、「=」と「VLOOKUP」の間に「IFERROR(」と書きます。
次にカーソルを一番右に移動し、「,(カンマ)」を打ちます。
ここに「エラーの場合の表記」を記入するので、“-“と書きます。
※数値データ以外を入力する際は、”(ダブルクオーテーション)で文字を囲う必要があります。
最後にEnterキーを押すと、無事エラーの際には「-」が表示されるようになりました。
ExcelCamp講師 野沢先生IFERROR関数はVLOOKUP関数とセットで使う機会が多い関数です。
エラーの場合「空白」に指定する方がよくいますが、空白は「意図的なのか」「記入漏れなのか」が分かりづらいため「-」などが推奨です。
「上位1%」のVLOOKUP関数活用術
Excelを長時間使う方にとって、いかに効率よく素早く関数を書けるかは重要な観点です。
一番の理想は、関数を左上のセルだけに書き、後はコピペで一気に正しく表示させたいです。
ExcelCamp講師 野沢先生この技術を覚えるとExcel操作効率が数段階レベルアップします。
本来であればExcelCamp本編でお伝えしている技術ですが、以下すべて無料で公開します。
なお、この技術を覚えるためには「絶対参照($マーク)」の正しい理解が必須です。
人に説明できるレベルで絶対参照を理解していない方は、この先の内容を読む前に以下の記事を読んでください。
ExcelCamp講師 野沢先生それでは、以下ステップを5つに分けて解説していきます。
VLOOKUP応用技ステップ1：範囲を「絶対参照」にする
繰り返しとなりますが、必ず「範囲」はF4キーで絶対参照にします。
ExcelCamp講師 野沢先生これまで1万回以上VLOOKUP関数を書いてきましたが、
関数を下、右にコピーするときに選択範囲が相対的についてきてほしいと思ったことは一度もありません。
とにかく範囲を指定したら「すかさずF4キー」を押して絶対参照に設定してください。
※PCによってはFnキーを同時に押す必要があります。
試しにこの状態で下に関数をコピーしてみます。
下のセルは全て正しく入力することができました。
しかし右にコピーするとエラーが出てしまいます。
F2キーでエラーの原因を見てみると、「検索値」が相対参照されてズレてしまっていることがわかります。
ExcelCamp講師 野沢先生エラーが出たら、すかさずF2で中身確認です！
なので次にステップ2では、関数を右にコピーしても検索値がズレないように指定します。
VLOOKUP応用技ステップ2：検索値を「列の絶対参照」にする
検索値は常に「B列目」にいてほしいので、検索値には「列の絶対参照」を指定する必要があります。
この状態でEnterキーを押して、関数を右にコピーします。
ひとまずエラーは出なくなりました。
しかし、列番号が「2」のままなので、どれだけコピーしてもすべて「単価」の情報が表示されてしまいます。
ExcelCamp講師 野沢先生右にコピーしたら列番号が自動で切り替わり、「原価」「原産地」が表示されるのが理想ですよね。
そのために必要な設定をステップ3以降で行います。
VLOOKUP応用技ステップ3：マスタデータに連番を振る
関数の入力方法には「直接入力」と「セル参照」の2種類があります。
このうち直接入力はどこにセルをコピーしても値が変化しないという特徴があります。
ExcelCamp講師 野沢先生今回は右にコピーするごとに列番号の値も変化させたいので、直接入力ではなく「セル参照」に書き替える必要があります。
セル参照で列番号を指定するために、以下のようにマスターデータに連番を振ります。
これでステップ3は完了です。
一瞬で連番を振るショートカット
連番はいちいち手入力をしていては非効率です。以下の方法でたとえ何万行あろうが一瞬で入力できるのでぜひ覚えてください。
まず、連番が始まるセルに「1」と記入します。
次に、連番を振りたい範囲を選択します。
ExcelCamp講師 野沢先生このとき、「1」と書いたセルが一番左(もしくは一番上)に来るように選択します。
連番を振るショートカットAlt+E+I+Sを入力します(同時ではなく順番に押すショートカットです)。
Altキー、Eキー、Iキー、Sキーを順番に押すと、上記のように「連続データ」ボックスが表示されます。
最後にEnterキーを押すことで、一瞬で連番を振れます。
ExcelCamp講師 野沢先生非常に使う機会の多い、便利なショートカットなのでゴロ合わせを作ってみました。
ええ(E)感じに、一個(I)ずつ、数字(S)を増やす
覚えやすいでしょうか？
VLOOKUP応用技ステップ4：列番号を「セル参照」にする
マスターデータに連番を振ったら、列番号を「セル参照」に変更します。
この状態でEnterキーを押して式を確定させ、右にコピーしてみます。
原価、原産地が正しく表示されるようになりました。
F2キーで関数の中身を見てみると、ちゃんと列番号の参照位置が右にズレてくれています。
ExcelCamp講師 野沢先生これで完成！と思いきや最後に落とし穴があります。
このまま関数を下にコピーすると、またもやエラーになってしまいます。
原因を把握するためにF2キーで関数の中身を確認します。
すると、列番号(紫色のセル)が下にもズレてしまっていることがわかります。
列番号に「単価」や「200」と書かれたセルが参照されてしまっていたため、エラーとなっていたわけです。
ExcelCamp講師 野沢先生列番号の参照は、どれだけ下にコピーしても「2行目」にいてほしいですよね。
なので最後のステップで、関数を下にコピーしても列番号がズレないように指定して終わりです。
VLOOKUP応用技ステップ5：列番号を「行の絶対参照」にする
関数を下にコピーしても列番号が「2行目」からズレないように指定するには、行の絶対参照を使います。
具体的には以下のように指定してください。
これでEnterキーで確定し、下にコピーします。
エラー無く、正しく入力することができました。
ExcelCamp講師 野沢先生以上がVLOOKUP関数応用編「上位1%のテクニック」です。
絶対参照の指定の仕方はいつも同じなので、慣れてきたら以下のようにF4キーを押す回数を手に覚えさせてしまうと効率的です。
検索値：列の絶対参照(F4キーを3回)
範囲：絶対参照(F4キーを1回)
列番号：行の絶対参照(F4キーを2回)
休日「1日」を投資して、平日の生産性を最大「20倍」にするExcel研修
私たちが提供するExcel研修「上位1%を目指す！ExcelCamp」では、これまで延10,000名以上の方に研修を受講いただき、受講者満足度は9.71(※)という大変高い評価を頂いております。
※10点満点中の評価の平均値。
休日1日を研修に使うのは「やや重たい」というご意見もいただきます。
しかし、本当にExcelスキルを実践で使えるレベルで高めるとなると、通常数年はかかるものです。
実際にExcelCampで教える内容は、代表の中田が業務コンサルタントとして数年間、毎日10時間以上Excelと向き合った結果ようやく生まれたスキルです。
そのことを考えると、休日の「たった1日」を投資して、その後のExcel業務の生産性を圧倒的に高めることは決して悪くない選択だと思います。
参加者の中にはお伝えしたスキルを仕事の現場で活かし、生産性が「20倍」になったという事例もあります。
動画サービス「bizplay(ビズプレイ)」で研修の一部を無料公開中！
※全4回の動画のダイジェスト版です。
※第1~4回の内容をフルで視聴するには、bizplayへの無料会員登録が必要です。
現在セミナー動画見放題サービス「bizplay」で、ExcelCamp Day1の講義内容の一部を無料配信しています。
全4回に分けてExcelが得意になるための考え方や覚えておくべき基本操作を解説していますので、Day1への参加を迷われている方はぜひ一度ご覧ください。
著書「神速Excel」も絶賛発売中
