使用関数：VLOOKUP
ExcelのVLOOKUP（ブイルックアップ）関数には、データ表を縦の方向へ検索して一致する項目のデータを自動で転記する機能があります。商品コードから単価と商品名を、顧客の名前から電話番号を、住所から配達料金を自動で取り出す便利な関数です。
IF関数と並んで「使いこなしたいExcel関数」No.1のVLOOKUP関数ですが、初めて使う人や慣れていない人にとっては問題の多い関数でもあります。計算が重い、設定が複雑、数式が長くなりがち等の問題に加え、引数の指定方法に固定的なルールがあり、これが「VLOOKUP関数がうまく使えない！」ことの原因になっています。逆に言うと、引数の指定ルールを覚えれば、VLOOKUP関数の複雑な数式の構文もすぐに分かるようになります。
ここではVLOOKUP関数の使い方を初めて使う人にも分かりやすく解説していきます。
VLOOKUP関数の引数の指定方法
VLOOKUP関数の構文を、引数それぞれの指定方法を確認しながら説明していきます。
売上のデータを入力する表を例にします。このデータベースに自分で入力する列は「日付」「商品コード」「個数」です。「商品名」と「単価」の列にはVLOOKUP関数を入力して別表から自動転記させ、「金額」の列には「単価」と「個数」を掛け算する計算式を入力しておきます。
下図はVLOOKUP関数で検索の範囲にする表で、自社商品の情報をまとめた簡単な商品マスタです。「商品コード」「商品名」「単価」を対応させています。
（ここでは分かりやすくするために検索の範囲にする表を売上表と同じシートに並べていますが、実務では別のファイルや別のワークシートに置くのが普通です）
VLOOKUP関数の構文と引数の構成は以下のようになります。
引数それぞれの説明と指定方法です。
- 第１引数【検索値】
- VLOOKUP関数の１つ目の引数「検索値」には検索のキーとなるデータ「どの値を検索するか」を指定します。全角半角は区別して検索されますが、英字の大文字小文字は区別されません。
この例では「商品コード」の最初の行のセルを参照します。
- 第２引数【範囲】
- VLOOKUP関数の２つ目の引数「範囲」には「どこからどこまでを検索するか」を指定します。
この例では「商品コード」「商品名」「単価」を対応させた商品マスタのセル範囲をドラッグで指定し、転記するデータがずれないように絶対参照にしておきます。
検索の範囲にする表のルール「範囲」にする表では検索値を探す列を一番左にするというルールがあります。または検索値を探す列が左端になるように範囲を指定します。
- 第３引数【列番号】
- VLOOKUP関数の３つ目の引数「列番号」には、「取り出すデータのある列の場所が「範囲」の先頭列から何列目にあるか」を数字で指定します。
この例では「商品名」の列番号「2」を入力します。
- 第４引数【検索方法（旧バージョンでは検索の型）】
- VLOOKUP関数の４つ目の引数「検索方法（検索の型）」には「検索値の照合を近似値検索で行うか完全一致検索で行うか」をTRUEまたはFALSEで指定します。
ここは特定の例を除き、FALSE（完全一致）を指定します。省略するとTRUE（近似値）を指定したことになるので、この例ではFALSEまたは0と入力します。
「商品名」を取り出すVLOOKUP関数の数式です➔ =VLOOKUP(C2,$I$2:$K$10,2,0)
この式を「単価」のセルにコピー＆ペーストして、「列番号」を「2」から「3」に変更します。
検索値に指定した「商品コード」のセルに入力して、「商品名」と「単価」がVLOOKUP関数により自動で転記されたことを確認します。個数も入力すれば、単価と掛け算されて金額が自動計算されます。
エラー値#N/Aが空白になるようにする
検索値に指定したセルが未入力だと、VLOOKUP関数はエラー#N/Aを返します。
検索値を入力すれば正しい値が返りますが、このままだと感じがよくないので、エラーの場合は空白になるようにVLOOKUP関数をIFEROOR関数にネストしておきましょう。
「商品名」のセルの数式はこうなります➔ =IFERROR(VLOOKUP(C2,$I$2:$K$10,2,0),””)
第１引数にVLOOKUP関数の式を入力し、第２引数に空白文字「””」を指定します。「単価」の数式も同様にして、それぞれの列のセル範囲にオートフィルコピーします。また、「金額」に表示される0を空白にしたい場合も掛け算の式をネストします。
VLOOKUP関数で検索する「範囲」を固定するには…
VLOOKUP関数の第２引数「範囲」の指定ではセル番地を絶対参照にします。これは転記するデータがずれないようにするためですが、もう少し効率的に「範囲」を固定させる方法があります。
以下の方法でVLOOKUP関数の「範囲」を固定させると、表に加えた変更が数式にそのまま反映されるので、範囲を指定し直す必要がありません。
- 検索範囲の固定方法① 「範囲」を列で指定する
- VLOOKUP関数の「範囲」に指定する表の列番号をドラッグして列全体を範囲に含めます。
- 検索範囲の固定方法② 表をテーブル化する
- VLOOKUP関数の「範囲」に指定する表を「ホーム」➔「テーブルとして書式設定」でテーブル化します。範囲を固定するだけでなく、表をテーブル化しておくとVLOOKUP関数の引数に構造化参照が使えるため、複雑に見える数式が直感的に理解できるようになります。
テーブル化についての詳細解説はこちらを参照して下さい。
構造化参照についての詳細解説はこちらを参照して下さい。
VLOOKUP関数の第４引数にTRUE（近似値検索）を指定する場合とは？
VLOOKUP関数の第４引数「検索方法（検索の型）」にTRUE（または1、または省略）を指定すると、検索値を近似値で検索することになります。この場合の検索値は数値や日付です。
近似値検索では「範囲」の左端列を検索して検索値と一致する値が見つからなければ、「検索値」未満で最大の値を探し、その値に対応するデータを取り出します。
下図右はVLOOKUP関数の「範囲」にする対応表です。下図左の成績を検索値にして、対応するクラスを表示させます。
近似値検索では「範囲」の先頭の列を昇順に並べ替えておく必要があります。数値なら小さい方から大きい方へ、日付なら古い方から新しい方へ並べ替えます。並べ替えしないでVLOOKUP関数を実行すると正しい結果が返りません。
数式の作成は前章とほとんど変わりません。第４引数を省略するだけです。
数式➔ =VLOOKUP(B2,$E$2:$F$5,2)
VLOOKUP関数の近似値検索により成績でクラス分けができました。
検索値「45」の成績に一致する値は範囲の左端列に見つからないので、近似値検索により「45」未満で最大値の「30」を見つけ、それに対応する値の「C」を取り出しています。
VLOOKUP関数の「検索方法」で「FALSE」と「TRUE」の使い分けについての詳細解説は以下を参照して下さい。。
LOOKUP➔VLOOKUP➔XLOOKUPへの進化
Excelの代表的な検索関数といえばVLOOKUP関数ですが、その影に隠れた存在のLOOKUP関数もまだ使われています。とは言え、MicrosoftはLOOKUPより縦に検索するVLOOKUP関数と横に検索するHLOOKUP関数の使用を推奨しています。
VLOOKUP関数の優位は動かないかと思われましたが、2021年、MicrosoftはXLOOKUPという新関数を公開しました。
XLOOKUP関数はVLOOKUP関数の問題を取り除き、HLOOKUP関数の機能を取り入れ、複数の検索値を一括検索して、同時期に登場したスピル機能でまとめて転記できるように進化した画期的な関数です。
ExcelのXLOOKUP関数の使い方
新しい関数なので旧バージョンExcelとの互換性の問題があり、現在のところXLOOKUPがVLOOKUP関数を代用するまでには至っていません。しかし、今後徐々にXLOOKUPの使用が拡大するにつれ、VLOOKUP関数の存在意義が薄れていく可能性は大きいでしょう。
できればVLOOKUP関数と平行してXLOOKUP関数の使い方にも慣れておくことをお勧めします。