VLOOKUP関数の書き方
=vlookup (検索値, 範囲, 列番号, 0)
検索値：検索したい値を入れます
範囲：検索する範囲を選択します(一番左列は「検索値」を含む列に指定)
列番号：範囲の左列から順に1,2,3,…と数えます
0：何も考えずに「0」と入力します(「False」でも同じ意味)
VLOOKUP関数は数あるExcel関数の中でも、出現頻度の高い関数の1つです。
言葉で説明してもなかなか理解が難しい関数でもあるので、この記事ではなるべく具体例を示しながら、はじめてVLOOKUP関数の基本的な機能や書き方、よくある間違いをわかりやすく解説します。
記事の後半では、そこそこExcelを使いこなせている方でも中々知らない応用的な書き方も紹介しています。
この記事ではExcelCamp講師が研修で実際に説明している手順に沿って、解説いたします。
ExcelCamp講師 野沢先生ちなみに実務ではXLOOKUP関数を使う機会の方が多いです。
VLOOKUP関数の上位互換のような関数なので、興味ある方は以下の関連記事をご確認ください。
VLOOKUP関数は何が出来る関数？
VLOOKUP関数とは、あるデータ(検索値)を元に、欲しいデータを自動で探してきてくれる関数です。
ExcelCamp講師 野沢先生Lookupは英語で「探す」、Vは「Vertical(垂直方向)」という意味です。
言葉で定義を説明してもイメージがつきにくいので、以下具体的にVLOOKUP関数を書いて説明していきます。
ある架空の果物屋さんのデータがあるとします。
上記のリストから果物名を「検索値」として、単価と原価を自動的に表示させたい場面を考えます。
先に結論からお伝えすると、以下のようにVLOOKUP関数を書くとうまくいきます。
ExcelCamp講師 野沢先生以下、「単価」のVLOOKUP関数を例にとって、書き方を詳しく解説します。
VLOOKUP関数の書き方を細かく解説
VLOOKUP関数の書き方
=vlookup (検索値, 範囲, 列番号, 検索方法)
検索値：検索したい値を入れます
範囲：検索する範囲を選択します(一番左列は「検索値」を含む列に指定)
列番号：範囲の左列から順に1,2,3,…と数えます
検索方法：何も考えずに「0」と入力します(「False」でも同じ意味)
関数名の書き方
半角モードで=VLOOKUP(とすべて手入力してももちろん良いですが、高効率を目指すのであれば以下のように楽をしましょう。
まず半角モードにしたら、=vlとだけ書きます。
すると上のように関数名の候補が表示されます。
この候補はTabキーを押すことで選択でき、入力をかなり省略することができます。
ExcelCamp講師 野沢先生関数名の候補が複数あるときには↑キー、↓キーを使って候補を選べます。
VLOOKUP関数の場合は=vlまで書けば候補が1つに絞られるので、=、v、l、Tabと順番に押すのが最速の入力法です。
検索値の書き方
検索値には「どの果物の単価が知りたいか」を指定します。矢印キーを使いながらF4セルまで移動してください。
F4セルには「りんご」が入っており、「りんごの情報を取ってきてね」という意味になります。
=VLOOKUP(F4まで入力できたら,(半角カンマ)を入れて次に進みます。
範囲の書き方
検索値だけでは「どこから情報を取ってくればいいのか」がExcelにわかりません。
そこで次に、情報を取ってくる「範囲」を指定してあげる必要があります。
再度矢印キーを押して、関数を書いているセルの外に飛び出して、B4からD8セルを選択します。
※B4セルに移動し、ShiftキーとCtrlキーを押しながら↓キーと→キーを順番に押します。
ExcelCamp講師 野沢先生B4:D8の範囲の選択にはルールがあり、検索値の情報がある列を一番左に指定する必要があります。
今回でいえば検索値である「りんご」がある列はB列なので、B列が一番左に来るように範囲を指定する必要があります。
正しく範囲を選択できていることを確認したら、,(半角カンマ)を入力して次に進みます。
列番号の書き方
次に指定するのは、指定した範囲の中から「何を探すのか」です。
列番号は選んだ範囲の左列から順に1,2,3,…と数えます。今回は「単価」のデータを探したいので、列番号には「2」と入力します。
ExcelCamp講師 野沢先生「単価」の情報は左から2番目の列にあるので「2」と記入します。
列番号はあくまで選んだ範囲の左端の列から「1」と数えるので、A列目から「1,2,3,…」と数えるわけではありません。
正しく列番号を指定できていることを確認したら、,(半角カンマ)を入力して次に進みます。
検索方法の書き方
最後に「検索方法」を指定します。
VLOOKUP関数の検索方法には「完全一致」と「近似一致」の2種類がありますが、ほぼ100%「完全一致」しか使いません。
| 検索方法 | 書き方 |
|---|---|
| 完全一致 | 「0」「false」 |
| 近似一致 | 「1」「true」 |
一応それぞれの書き方を表でまとめましたが、VLOOKUP関数を10,000回書いて9,999回は完全一致を使うので、ここは何も考えずに「0」と書いてしまってもOKです。
ExcelCamp講師 野沢先生「false」と書いても完全一致を指定できますが、「0」のほうが打数が少ないのでおすすめです。
以上で検索値、範囲、列番号、検索方法の指定が全て終わりました。
)は書かなくても大丈夫なので、そのままEnterキーを押して式を確定させると、りんごの原価が自動で表示されます。
ExcelCamp講師 野沢先生F4セルの値を検索値にしているので、例えば「りんご」を「みかん」に書き換えると自動でみかんの単価が表示されます。
検索値を「みかん」に書き換えてEnterキーを押すと、
みかんの単価に自動で書き換わります。
ExcelCamp講師 野沢先生以上が基本的なVLOOKUP関数の書き方です。
ちなみに原価を出すには、列番号を「3」に変えるだけです。
VLOOKUP関数を書くときによく起きるミス
VLOOKUP関数を書くにあたって、「範囲」の指定がもっとも間違えやすいポイントです。
繰り返しとなりますが、範囲は一番左の列に検索値があるように選択しなければなりません。
例えば以下のような範囲の指定の仕方はいずれも間違いです。よくあるエラー例なので、すべて確認してください。
よくあるミス1：範囲の一番左の列に「りんご」が無い
図のように範囲を選択してしまうと一番左の列が空白列となり、Excelがいくら探しても「りんご」という検索値を見つけられずエラーになります。
よくあるミス2：ほしい「単価」の範囲だけを選択してしまう
「単価」の列だけ範囲を選択してしまってもいけません。範囲が1列しかないためExcelは検索値である「りんご」を単価の列から探すことになり、これまたエラーの原因となります。
よくあるミス3：「検索値」だけを範囲にしてしまう
逆に検索値だけを選択してしまってもエラーとなります。
この場合は検索値である「りんご」を探すことはできますが、「単価」の情報が範囲内にないのでエラーとなります。
ExcelCamp講師 野沢先生VLOOKUP関数は決して万能ではなく、あくまで範囲で選んだセルの中から検索をすることしかできません。
さらに検索値が一番左に来るように範囲を選ばなければいけないので、慣れるまでは記入ミスが起こりやすいです。
ちなみに：範囲は「絶対参照」がおすすめ
リストにVLOOKUP関数を書いて下や右に関数をコピーする場合には、必ず範囲に絶対参照を指定してください。
絶対参照($マーク)をつける：(Fn&)F4
※PCによってはFnキーを押しながらF4キーを押します。
| 参照の種類 | 型 | 付け方 |
|---|---|---|
| 「行」と「列」の絶対参照 | $A$1 | F4キーを1回押す |
| 「行」の絶対参照 | A$1 | F4キーを2回押す |
| 「列」の絶対参照 | $A1 | F4キーを3回押す |
| 相対参照 | A1 | F4キーを4回押す （もしくは押さない） |
ExcelCamp講師 野沢先生絶対参照で固定しないと、関数をコピーしたときに選択範囲がずれてしまいます。
セルが移動するごとに範囲も移動してほしいことなどありえないので、範囲を指定したらすかさずF4キーを押すことをクセづけてください。
VLOOKUP関数の結果でエラーが出た場合の対処法
もしも関数を書いたのにエラーが出てしまったときには、すかさずF2キーで書かれた関数の中身を見てエラーの理由を確認してください。
※PCによっては、Fnキーを押しながらF2キーを押す必要があります
セル参照されている場所は色分けされてわかりやすく表示されるので、「検索値」「範囲」「列番号」「検索方法」がそれぞれ正しく入力されているか確認します。
ExcelCamp講師 野沢先生上の例では列番号が「2」ではなく「4」と書かれていました。
範囲は合計3列しか無いので、「4」ではエラーが出ていたわけです。
このように、エラーがあったときには必ずF2で中身を確認しに行くことを癖づけてください。
エラー表示を変更するIFERROR関数の活用法
VLOOKUP関数が正しく書けていても、エラーになるケースがあります。
代表例は、マスタデータに検索値が無いケースです。
このような場合マスタデータにデータを追加する必要があるのですが、実践の場ではなかなかすぐに追加ができないこともあります。
そこでエラー表示を「-」など、別の表記に変更できるIFERROR関数をよく使います。
IFERROR関数の書き方
=IFERROR(値,エラーの場合の値)
ExcelCamp講師 野沢先生IF(もし)+ERROR(エラー)でIFERRORです。
「もしエラーが出たら」という条件を指定できる関数です。
ためしにエラーが出る場合は「-」と表示させる指定をIFERROR関数で書いてみます。
まずは普通にVLOOKUP関数を書きます。
マスタデータに「いちご」は存在しないためエラーが出ました。
この状態でF2キーでセルの中身を開き、「=」と「VLOOKUP」の間に「IFERROR(」と書きます。
次にカーソルを一番右に移動し、「,(カンマ)」を打ちます。
ここに「エラーの場合の表記」を記入するので、“-“と書きます。
※数値データ以外を入力する際は、”(ダブルクオーテーション)で文字を囲う必要があります。
最後にEnterキーを押すと、無事エラーの際には「-」が表示されるようになりました。
ExcelCamp講師 野沢先生IFERROR関数はVLOOKUP関数とセットで使う機会が多い関数です。
エラーの場合「空白」に指定する方がよくいますが、空白は「意図的なのか」「記入漏れなのか」が分かりづらいため「-」などが推奨です。
VLOOKUP関数の書き方応用1：「範囲」が別シートにある場合
VLOOKUP関数を書く場合、ほとんどがマスターデータは別シートにあると思います。
ExcelCamp講師 野沢先生関数の書き方自体は全く同じなので特別な操作は必要ありませんが、関数がやや複雑な表示になるため、混乱しないように一応解説します。
マスタデータが別シートにある場合、範囲選択の際にシートを移動するショートカットを使います。
シートを移動するショートカット
Ctrl&Fn&PgUp/PgDn
範囲選択の際に、ショートカットでシートを移動します。
その際、シートを移動したセルの表記を見ると「マスタデータ!A2」のように書かれているはずです。
これはシート名が違うときのExcel独特の表記で、意味は以下のとおりです。
なので、マスタデータが別シートにある場合のVLOOKUP関数の表記は以下のようになります。
ExcelCamp講師 野沢先生知ってしまえばなんてことのない違いですが、はじめて見た方は不安になりますよね。
決してエラーではありませんのでご安心ください。
VLOOKUP関数の書き方応用2：検索値、列番号もセル参照する
Excelを長時間使う方にとって、いかに効率よく素早く関数を書けるかは重要な観点です。
一番の理想は、関数を左上のセルだけに書き、後はコピペで一気に正しく表示させたいです。
ExcelCamp講師 野沢先生この技術を覚えるとExcel操作効率が数段階レベルアップします。
本来であればExcelCamp本編でお伝えしている技術ですが、以下すべて無料で公開します。
なお、この技術を覚えるためには「絶対参照($マーク)」の正しい理解が必須です。
人に説明できるレベルで絶対参照を理解していない方は、この先の内容を読む前に以下の記事を読んでください。
ExcelCamp講師 野沢先生それでは、以下ステップを5つに分けて解説していきます。
ステップ1：範囲を「絶対参照」にする
繰り返しとなりますが、必ず「範囲」はF4キーで絶対参照にします。
ExcelCamp講師 野沢先生これまで1万回以上VLOOKUP関数を書いてきましたが、
関数を下、右にコピーするときに選択範囲が相対的についてきてほしいと思ったことは一度もありません。
とにかく範囲を指定したら「すかさずF4キー」を押して絶対参照に設定してください。
※PCによってはFnキーを同時に押す必要があります。
試しにこの状態で下に関数をコピーしてみます。
下のセルは全て正しく入力することができました。
しかし右にコピーするとエラーが出てしまいます。
F2キーでエラーの原因を見てみると、「検索値」が相対参照されてズレてしまっていることがわかります。
ExcelCamp講師 野沢先生エラーが出たら、すかさずF2で中身確認です！
なので次にステップ2では、関数を右にコピーしても検索値がズレないように指定します。
ステップ2：検索値を「列の絶対参照」にする
検索値は常に「B列目」にいてほしいので、検索値には「列の絶対参照」を指定する必要があります。
この状態でEnterキーを押して、関数を右にコピーします。
ひとまずエラーは出なくなりました。
しかし、列番号が「2」のままなので、どれだけコピーしてもすべて「単価」の情報が表示されてしまいます。
ExcelCamp講師 野沢先生右にコピーしたら列番号が自動で切り替わり、「原価」「原産地」が表示されるのが理想ですよね。
そのために必要な設定をステップ3以降で行います。
ステップ3：マスタデータに連番を振る
関数の入力方法には「直接入力」と「セル参照」の2種類があります。
このうち直接入力はどこにセルをコピーしても値が変化しないという特徴があります。
ExcelCamp講師 野沢先生今回は右にコピーするごとに列番号の値も変化させたいので、直接入力ではなく「セル参照」に書き替える必要があります。
セル参照で列番号を指定するために、以下のようにマスターデータに連番を振ります。
これでステップ3は完了です。
番外編：一瞬で連番を振るショートカット
連番はいちいち手入力をしていては非効率です。以下の方法でたとえ何万行あろうが一瞬で入力できるのでぜひ覚えてください。
まず、連番が始まるセルに「1」と記入します。
次に、連番を振りたい範囲を選択します。
ExcelCamp講師 野沢先生このとき、「1」と書いたセルが一番左(もしくは一番上)に来るように選択します。
連番を振るショートカットAlt+E+I+Sを入力します(同時ではなく順番に押すショートカットです)。
Altキー、Eキー、Iキー、Sキーを順番に押すと、上記のように「連続データ」ボックスが表示されます。
最後にEnterキーを押すことで、一瞬で連番を振れます。
ExcelCamp講師 野沢先生非常に使う機会の多い、便利なショートカットなのでゴロ合わせを作ってみました。
ええ(E)感じに、一個(I)ずつ、数字(S)を増やす
覚えやすいでしょうか？
ステップ4：列番号を「セル参照」にする
マスターデータに連番を振ったら、列番号を「セル参照」に変更します。
この状態でEnterキーを押して式を確定させ、右にコピーしてみます。
原価、原産地が正しく表示されるようになりました。
F2キーで関数の中身を見てみると、ちゃんと列番号の参照位置が右にズレてくれています。
ExcelCamp講師 野沢先生これで完成！と思いきや最後に落とし穴があります。
このまま関数を下にコピーすると、またもやエラーになってしまいます。
原因を把握するためにF2キーで関数の中身を確認します。
すると、列番号(紫色のセル)が下にもズレてしまっていることがわかります。
列番号に「単価」や「200」と書かれたセルが参照されてしまっていたため、エラーとなっていたわけです。
ExcelCamp講師 野沢先生列番号の参照は、どれだけ下にコピーしても「2行目」にいてほしいですよね。
なので最後のステップで、関数を下にコピーしても列番号がズレないように指定して終わりです。
ステップ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」も絶賛発売中
