SUMやAVERAGE、COUNTなどの初級関数を1つ1つクリアしていき、いよいよレベルアップだ！という時に立ちふさがる中ボス的存在、VLOOKUP（ブイルックアップ）。
今回は「これからちゃんと使えるようになりたい」という方向けに、VLOOKUPの基本的な使い方をまとめてみました。何をするもので、どんなシーンで使うのかを理解するためのちょっとした手助けになれば幸いです。
※解説画像はMicrosoft365のExcelを使用しています
VLOOKUP関数でできること
まず、VLOOKUPとはどんな事ができる関数なのかを見ていきましょう。
一例として、Excelで見積書や請求書を作成している場面を想像してください。
上半分には日付、宛名、自社の情報、合計金額などを記載し、下半分には金額の根拠となる明細のリストを表形式でつけるフォーマットが一般的ですが、そのすべてを手打ちにしていたのでは時間がかかる上、半角全角スペース入力のルールも人によってまちまち、金額も単価表を見間違えて誤入力･･･なんて事態が起こらないとも限りません。
一方、作成しているものとは別に、手元にはExcelに入力された取引先の情報をまとめた表や、商品コード・商品名・単価などが一覧できる商品リストがあったとします。もしこれらを使って、「お客様番号を入力しただけで名前などの顧客情報が表示できたら」「明細書で商品コードを入力したら商品名と単価が自動的に表示できたら」とても便利ですよね。
それを実現してくれるのが「VLOOKUP関数」です。特定の値を入力したら、指定した表の範囲内でその値を検索し、一致した行と同じ行にある値を取得して表示してくれる働きがあります。
VLOOKUPの引数
それではVLOOKUPの引数を見てみましょう。
=VLOOKUP(検索値,範囲,列番号,検索方法)
| 検索値 | 検索する値を指定します。一般的には、「範囲」から欲しい情報 を引っ張り出すためのキーとなる値が入力されたセルを指定する 事が多いです。 |
| 範囲 | 検索値及び表示したい値が含まれる範囲を指定します。 ※検索値の含まれる列が必ず先頭列になるようにします |
| 列番号 | 「範囲」で指定した先頭列を1列目とし、検索値と一致した行の 何列目の値を表示するかを指定します。 |
| 検索方法 | 検索値と完全に一致する値を検索するか、近似値を含めて検索す るかを指定します。完全一致の場合は「FALSE」、近似一致にする 場合は「TRUE」と入力します。 ※「FALSE」は「0」、「TRUE」は「1」というように数値で 指定する事もできます。 |
ここで、検索方法に「完全一致」と「近似一致」の2種類ある点を少し補足しておきます。
VLOOKUPは、「検索値」で指定した値を「範囲」の一番左にある列の先頭から縦方向に検索する仕組みになっていますが、検索方法を完全一致（FALSE）に指定した時は「範囲」の中で一致するものを参照し、一致するものがなければ「値なし」でエラー値を返します。
一方、検索方法を近似一致（TRUE）で指定した時は、値が一致しない場合に検索値未満の最大値を参照します。いやー、自分で書いてて何ですがわかりづらいですよね（笑）
近似一致は「〇〇以上〇〇未満」の値を検索したい時など使いどころが限られますので、まず、商品コードや顧客番号などの一意の値を検索値にする場合は完全一致（FALSE）を指定する、と覚えておきましょう。この回でも、完全一致で検索するパターンを想定して進めます。
余談ですが、VLOOKUPの「V」は「範囲を縦方向に検索する」の「縦」の英訳Vertical（バーティカル）を意味します。この関数の仲間に「HLOOKUP」というものもありますが、そちらは「範囲を横方向に検索する」ものなので、「横（水平）」の英訳Horizontal（ホリゾンタル）の頭文字が使われています。
近似一致を使うパターンは別回にてアップしましたのでご参考にどうぞ。
VLOOKUPを入力する
では、先ほどの表を使ってVLOOKUPを入力してみます。
現在、「明細」のセルB3に「1001」というコードを入力している状態です。これを検索値として、C3に「商品名」、E3に「単価」を「商品マスタ」から取得して表示してみましょう。
まずはセルC3に商品名を表示する式を入れますので、C3にカーソルを合わせ、[数式]タブをクリックして「検索/行列」をクリックします。「関数の挿入」ダイアログボックスから入れる場合も「分類」で「検索/行列」を選択すると探しやすいです。
関数の一覧を下にスクロールして「VLOOKUP」をクリックします。
「関数の引数」ダイアログボックスが開いたら、「検索値」に商品CDが入力されるセルを指定しますので、B3をクリックします。Tabキーを押して「範囲」のボックスにカーソルを移動します。
検索する範囲は「商品マスタ」を表の終端までドラッグして入力します。（表の見出しは含める必要はありません）※厳密に言うとここは商品名を表示するための式なので、「商品CD」と「商品名」の2列だけ範囲に入っていれば大丈夫なのですが、一応単価の列まで範囲指定しておきます。
数式を1行だけしか入れない場合はこのまま進めて問題ありませんが、数式を他の行にもコピーする場合は「範囲」を絶対参照で固定しておかないと、式をコピーした時に範囲も一緒に動いてしまい正しい結果が求められなくなるので注意しましょう。
セル範囲を絶対参照にする場合は、「範囲」ボックスにセル範囲を入力したらＦ４キーを押せば、絶対参照の記号「$」が簡単に挿入できます。
Tabキーを押して列番号に移動します。選択した範囲の左から1列目、2列目と数え、表示したい値の入っている列を数値で入力します。ここでは「商品名」を表示したいので「2」と入力します。
最後に「検索方法」です。ここでは検索値と完全一致する値を検索したいので、「FALSE」と入力します。
ダイアログボックスの説明にはありませんが、「検索方法」に「0」を入力すると「FALSE」と同義になります。私のような昔の人間だとこっちの方が馴染みがあります。どちらでもお好きな方を使ってください。
これですべての引数を入れ終わりましたので、[OK]をクリックしてダイアログボックスを閉じましょう。入力した商品CDと同じ行の2列目にあった商品名が正しく表示されました。
続けてE3に単価を表示する式を入れましょう。
「検索値」「範囲」「検索方法」は先ほどの式と同じですが、「列番号」は左から数えて3列目の値を取得するので「3」と入力します。
引数を入れ終わったら[OK]をクリックしてダイアログボックスを閉じます。「単価」も正しく表示されました。
F列の「金額」に[数量(D3)]*[単価(E3)]の数式を入れて書式を整えておけば（画像では通貨にしています）、明細に直接入力するのはB列の商品CDとD列の数量だけで済みますのでスピーディに表が作成できます。
検索対象範囲が別シートにある場合
「範囲」に指定する表は数式を入れる表とシートが分かれていても問題ありません。数式にシート名から指定する時は、シート名のあとに「!」（半角エクスクラメーション）を入力し、続けてセル範囲を指定します。
| 設定例 | 数式 |
|---|---|
| Sheet2のA1からC12までの連続したセル範囲 | Sheet2!A1:C12 |
| シート名「コード表」のA5からE10までの連続したセル範囲 | コード表!A5:E10 |
シート名は直接入力しても良いですが、数式作成中にクリック操作で簡単に入力する事ができます。
下図は例として「範囲」に指定する表が「商品マスタ」シートに入れてあるケースを想定しています。「範囲」のボックスにカーソルが移動した段階で、まず商品マスタが入力されているシートタブをクリックします。
「商品マスタ」シートに表示が切り替わると同時に、「範囲」ボックスに「商品マスタ!」というシート名が表示されます。そのまま表の見出し以外の範囲をドラッグすれば、入力ミスもなく正確に入れる事ができます。
IFとの組み合わせでエラー値を表示しないようにする
これで1行目のVLOOKUPの式は完成したので、あとはC3とE3の式をそのまま下の行にコピーすれば完成ですが、コピーすると2行目以下に下図のようなエラーが出てしまいます。
「#N/A」（ノーアサインと読みます）は、参照先に値がない事を示すエラーです。
この式はB列に入力された「商品CD」を基準に商品マスタから値を取得するようにしていますが、B4とB5にはまだ何も入力されておらず空白値となっています。範囲で指定した商品マスタには空白値はありませんので、このエラーが出たという事です。
明細は毎回すべての行が埋まるとは限らないですが、空欄の行に#N/Aが表示されたままというのも何だか見た目がよろしくありません。そこで、よく使われる手がIF（イフ）関数との組み合わせです。
IFの条件式を「商品コードに何も入力されていなければ」とし、値が真の場合は空白、偽の場合はVLOOKUPで値を取得するという式にすれば、無駄なエラー表示を回避することができます。
C3の数式を例にIFと組み合わせるとこうなります。
=IF(B3=””,””,VLOOKUP(B3,$B$9:$D$16,2,FALSE))
関数を入れ子にする手順はこちらの回で書いていますので参考にしていただければと思いますが、すでに作ってあるVLOOKUP式があるならせっかくなのでそれを使いましょう。
まず、C3にカーソルを合わせ、数式バーに表示されたVLOOKUPの式を＝部分を抜いてドラッグで選択し、コピーします。Ctrl+Cでコピーすると簡単です。
C3の数式は一旦Deleteキーで削除し、IFを入力します。「論理式」に「B3が空白だったら」を示す「B3=””」、「値が真の場合」は「空白」を示す「””」を入れます。
「値が偽の場合」のボックスにカーソルを移動したら、そこで先ほどコピーしたVLOOKUP式を貼り付けてしまいましょう。ここもショートカットーキーCtrl+Vを使うと簡単です。
Enterを押して完成です。数式をコピーすると、B列に何も入っていない行のエラー値が表示されなくなりました。
同じ要領でE3の式と、ついでにF3の乗算式も「=IF(B3=””,””,D3*E3)」としておいてコピーしておけば、コードが入力された行だけVLOOKUPと金額の乗算式が働くようになります。
もし、コードを入力しても「#N/A」表示になる場合は、範囲に含まれないコードを入力している可能性が高いので、入力した値に間違いがないか、または数式の範囲指定に誤りがないか確認しましょう。
※参考記事
