ExcelのVLOOKUP関数の使い方｜指定の列と同じ行にある値を返す
- Release
- Update
Excel（エクセル）には400種類以上の関数が存在しますが、その中でもよく利用されるのがVLOOKUP（ブイ・ルックアップ）関数です。
引数が多く、設定方法を忘れてしまいがちな関数ですが、この記事では初めてVLOOKUP関数を触る人に向けて引数を1つずつ分解して解説しております。
最後に実際に手を動かしながら覚える練習問題も用意しておりますので、ぜひ覚えて効率化を目指しましょう。
VLOOKUPとは？
VLOOKUP関数のVは「Vertical（垂直、縦）」、LOOKUPは「探す」と言う意味です。垂直に探すと覚えるとイメージしやすいのではないでしょうか。一方、横方向に検索する場合はHLOOKUP（エイチ・ルックアップ）関数を使用し、Hは「Horizon（水平、横）」を意味します。
VLOOKUP関数は、指定した範囲の先頭列を縦方向に検索し、検索条件に一致したデータを検索して取り出してくれる関数です。VLOOKUP関数を使用することで手入力をしていた大量のデータも自動的に入力が可能となります。
VLOOKUP関数の用途
VLOOKUP関数が使われるのは以下のようなケースが一般的です。その他の関数と組み合わせることで活用方法は広がります。
- 在庫管理で商品番号と商品の情報が一覧となっている場合、商品番号を検索してその商品の商品名や価格を取り出す場合
- 住所録の氏名から住所や電話番号を取り出す場合
- 請求書の入力で項目番号を入力すると対応した項目名、価格を取り出す場合
- 2つの異なるデータ群からデータが合致していないキーワードまたは数字を抽出する場合
大量のデータの中から、複数の関連した項目をもったデータを抽出するときにVLOOKUP関数は力を発揮します。
VLOOKUP関数の書式
まずは、VLOOKUP関数の書式を確認していきましょう。
VLOOKUP関数は、指定された範囲の1列目で特定の値を検索し、指定した列と同じ行にある値を返します。
数式としては、何のキーワードで（検索値）、どこを（範囲）、どの列か（列番号）を引数として指定していきます。
=VLOOKUP（検索値, 範囲, 列番号, [検索の型]）
VLOOKUP関数では引数を最大4つ使用します。4つもあると多いと感じるかもしれませんが、下記で1つずつ説明していきます。
※引数（ひきすう）とは、Excelの関数を使用する際に必要な情報です。関数が結果を返すための判断材料とイメージしましょう。関数名の後の括弧「()」内に入力します。
このページを見ている人におすすめの商品
第1引数（検索値）
1番目の引数は「検索値」です。この引数は必須です。検索したい値またはセル参照を指定します。文字列などの値を指定するときは「"文字列"」のようにダブルクォーテーションで囲います。
検索値は第2引数である「範囲」の左端の列（左から1列目）からのみ検索します。2列目以降や範囲の左側は検索対象としません。
例えば「B4:D6」のセル範囲から検索する場合、検索値はB列に存在する必要があります。
検索値は全角文字と半角文字は区別して検索しますが、英字の大文字と小文字は区別しないので注意しましょう。
第2引数（範囲）
2番目の引数は「範囲」です。この引数は必須です。参照する表のセル範囲を指定します。
指定したセル範囲の左端の列（左から1列目）には、検索値が含まれている必要があります。またセル範囲内に検索する結果の値も含める必要があります。
例えば検索値がB列にあった場合、「B4:D6」のように範囲の最初の列であるB列を含める必要があります。
また、範囲を絶対参照にしないと数式をコピーした時に範囲がずれてしまいます。絶対参照「$」でセルを固定すると、VLOOKUP関数の数式を他のセルにコピーして使用しても、元のテーブル範囲を変えずに使用することができます。
第3引数（列番号）
3番目の引数は「列番号」です。この引数は必須です。第2引数の「範囲」の左から数えた列の番号を指定します。
列をアルファベットではなく、数字で指定してください。「範囲」の左端の列が1になり、次の列以降2、3、4・・・となります。
第4引数（検索の型）FALSEとTRUEの違い
4番目の引数は「検索の型」です。この引数は任意です。検索値に一致したデータが見つからなかったとき、どのように対応するかを決める引数です。完全一致のデータのみ検索する方法と、一番近いデータを検索する方法の2種類があります。
- 「FALSE」または「0」と指定すると、検索値と完全一致するデータのみを範囲の中から探します。「FALSE」の場合はデータの並び替えは必要ありません。一致するデータがない場合は「#N/A」が結果として返ってきます。
- 「TRUE」または「1」と指定すると、近似値（検索値未満の最大値）を範囲の中から探します。「TRUE」を指定する場合、範囲の左端の列にある「データを昇順で並び替えておかないと正しい結果が返ってこない」ことがあります。
「検索の型」を省略した場合、「TRUE」として扱われます。
VLOOKUP関数の使い方（実践）
実際にVLOOKUP関数を使用する例をご紹介します。引数をどのように使用するか見てみましょう。
作業時間：10分
表を準備する
まずは上記の表を準備しましょう。数式が入っているのは、「単価」と「個数」を掛け算したG10,G11,G12セルのみです。今回の目的としては商品コードを入力するとその商品コードに紐づいた商品名や単価を表示させるようにしていきます。
VLOOKUP関数を入力する
まずD10セルを選択し、セルに直接『=vlookup』と入力します。
検索値を指定する
検索値としてC10セルを選択します。D10セルに『=vlookup(C10』と入力されました。
範囲を指定する
セルB4からD6までのセルをまとめて範囲選択します。選択されている状態で【F4】キーを押します。そうすると「$B$4:$D$6」のように指定されます。「$」は絶対参照と呼ばれます。数式をコピーしてもズレないようにセルを固定することができます。
列番号のを指定する
列番号として『2』を入力します。列番号には指定した範囲のうち、取り出したい値がある列の番号を指定します。商品名を取り出したいので2列目を指定しています。
検索方法を選択する
検索方法として、ここでは完全一致の「FALSE」を指定します。
VLOOKUP関数の数式を確定する
D10セルに『=vlookup(C10,$B$4:$D$6,2,FALSE)』と入力されましたので【Enter】キーを押します。
VLOOKUP関数の列番号を指定する
E10セルを選択し、D10セルと同じようにVLOOKUP関数を指定します。『=vlookup(C10,$B$4:$D$6,3)』のように列番号として『3』を入力します。指定したセルB4からD6までの範囲で単価を取り出したいので3列目を指定しています。
商品コードを入力する
C10セルにバナナの商品コード『1002』を入力します。
VLOOKUP関数の結果
D10、E10セルに商品コード「1002」に紐づいている商品名と単価が表示されました。
個数を入力した後の金額
F10セルに個数を入力すると単価に個数をかけた計算結果がG10セルに反映されました。
他の行への反映
同じようにバナナやみかんを設定した結果になります。VLOOKUP関数を使うことで商品コードを指定すれば決められた商品名と単価が表示されました。
まとめ
今回、実践で作成した表を1つの図にまとめました。VLOOKUP関数は引数がたくさんあり最初はとまどうかもしれませんが、この記事でイメージをつけていただき会社や学校でご活用ください。
その他、様々なVLOOKUP関数の使い方
検索する
様々な検索方法
VLOOKUP関数には「検索の型」以外にも様々な検索方法があります。
以下の記事ではVLOOKUP関数の検索方法として「検索の型」「文字列の検索」「あいまいな検索」「複数検索」の4つの方法を紹介しています。
ワイルドカードを使ったあいまいな検索方法
VLOOKUP関数はあいまいな検索も可能です。記号を利用することにより、任意の文字列を指定することが可能です。
指定方法が少し手間がかかるワイルドカードを覚えて、一歩進んだVLOOKUP関数マスターになりましょう。
複数条件で検索する方法
VLOOKUP関数は条件を1つしか指定できませんが、元の表データを加工することで複数条件で検索することが可能です。
検索値が重複してしまう場合はぜひ複数条件で検索してみましょう。
参照する
別シートを参照する方法
VLOOKUP関数を利用する場合、参照元と参照先の2つのテーブルが最低でも必要となります。
この2つのテーブルを別のシートで管理する方法をお伝えします。
列番号を可変する（ずらす）方法
VLOOKUP関数の式が入ったセルを他のセルにコピーする際に、列番号だけは固定されたままになってしまいとても不便です。
列番号を他のセルに用意して、そのセルを参照することにより問題は解決されます。
左側の値を取得する方法
VLOOKUP関数では範囲指定した一番左の列を検索値の検索範囲とし、2列目以降の値を取得する関数です。
そうとなると一番左側の数値は取得できないこととなります。ある関数を利用すれば再現できますので、その代用方法を以下の記事でお伝えします。
エラーを対処する
#N/Aエラーが起きたときの対処法
「#N/A」エラーになるのは関数の書式が違ったり、引数の使い方が間違っていたりすることがほとんどです。その原因と対処方法をチェックしましょう。
エラーや0を返さず空白で返す方法
検索した値が見つからないときに「#N/A」エラーが出たり、参照先のセルが空欄だったときに「0」が表示されますが、それを空白に置き換えてすっきりさせましょう。
その他
合計する方法
VLOOKUP関数で取得したリストを、VLOOKUP関数で合計まですることはできません。
以下の記事で、リスト化された数値を、指定した条件付きで合計する方法をSUMIF関数を使ってお伝えします。
IF関数を組み合わせて使う方法
IF関数の条件式の中にVLOOKUP関数を組み合わせると、点数に対する合否判定ができたり、IF関数の判定結果にVLOOKUP関数を組み合わせると、プルダウンの「会員/一般」の選択でそれぞれの価格を取得することが可能です。
IF関数を利用することで関数の式が長くなって難しそうに思えますが、できることが格段に増えますのでぜひチャレンジしてみましょう。
ExcelのVLOOKUP関数をVBAで高速で処理する方法
VBAでExcelのVLOOKUP関数のように検索する際、大量のデータを処理をすると実行時間がかかってしまう場合があります。
実行時間が速くなるVBAの記述をサンプルコードとともに紹介しています。VBAを記述するときにご参考ください。
XLOOKUP関数も合わせてチェック！
VLOOKUP関数の後継として新しくXLOOKUP関数が発表されました。XLOOKUP関数の基本的な使い方を説明しています。
LOOKUP関数も合わせてチェック！
LOOKUP関数の基本的な使い方を説明しています。VLOOKUP関数やHLOOKUP関数との違いも比較しています。
他の関数も合わせてチェック！
Office Hackでは、ここでご紹介できなかった関数の一覧ページもご用意しております。ぜひ、参考にしてください。