VLOOKUP は、Excel の表から指定した値を検索し、対応する行の値を取得できる関数です。
例えば、
・ 社員番号から社員名を取得したい
・ 商品コードから商品名を表示したい
・ 顧客コードから住所を取得したい
といった場面でよく利用されます。
Excel の業務では今でも広く使われている関数の一つであり、初心者が早い段階で覚えておきたい関数です。
この記事では、VLOOKUP の構文や各引数、実際の使用例を初心者向けに分かりやすく解説します。
エラー表示されたときの対処方法も紹介しているので、エラー表示が出た場合はメニューから飛んでください。
現在は VLOOKUP の後継である XLOOKUP が出ていますので興味があれば以下の記事も合わせてどうぞ。
VLOOKUPとは
VLOOKUP は、指定した範囲の左端の列から検索値を探し、探し出した値の行から欲しい列の値を取得する関数です。
文字で説明すると難しいですが、下の「社員表」から「社員番号」を指定して「社員名」を取得するものとなります。
なお、VLOOKUP の V は Vertical (縦方向) を意味しており、縦方向に並んだ表を検索する際に使用します。
VLOOKUP の構文
VLOOKUP の構文と各引数の説明です。
範囲と列番号の意味が少しわかりづらいので、基本的な使い方で詳しく説明します。
検索方法については任意指定となりますが、指定しない場合に意図しない検索結果になる場合があります。
構文
VLOOKUP ( 検索値, 範囲, 列番号, [検索方法] )
※ 検索方法は省略可能
例)
= VLOOKUP ( "1", A1:B10, 2 )
= VLOOKUP ( A1, B1:D20, 3, FALSE )
= VLOOKUP ( A1, 社員表!C1:D9, 2, TRUE )
| 引数 | 内容 | 必須 |
|---|---|---|
| 検索値 | 検索したい値 | ○ |
| 範囲 | 検索する表 (範囲) 全体 | ○ |
| 列番号 | 指定した表から値を取得する列番号 | ○ |
| 検索方法 | FALSE (完全一致)、または TRUE (近似一致) | 任意 |
通常は完全一致検索を使用するため 4つの引数をすべて指定します。
基本的な使い方
社員番号から社員名を取得するサンプルを作成します。
A2 セルに入力された社員番号を社員表から検索して社員名を取得します。
社員表を別シートに作成して列指定するとさらにわかりやすい使い方ができます。
第三引数までが必須項目となりますが、完全一致検索 ( 第四引数で説明 ) することが多いため間違えずに使用してください。
= VLOOKUP ( A2, A6:B15, 2, FALSE )
別シートの社員表を参照する場合
= VLOOKUP ( B3, 社員表!A2:B11, 2, FALSE )
第一引数「検索値」
第一引数は、検索する値を指定します。
表で管理している内容 (先ほどの例では社員表) からキーとなる値 (社員番号) を指定することで対象行を検索することができます。
検索値にはセル参照のほか、あまり使うことはありませんが直接値を指定することも可能です。
※ 直接値を指定する場合は “” (ダブルクォーテーション) で囲みます
= VLOOKUP ( A2, A6:A15, 2, FALSE )
= VLOOKUP ( "10002", A6:A15, 2, FALSE )第二引数「範囲」
第二引数は、検索対象となる範囲を指定します。
一番左端の列に検索列、右端に検索結果が来るように範囲指定します。(下図の赤枠参照)
検索結果は範囲内であれば右端でも構いませんが、範囲を広くしても特に何も変わりません。
通常は数式をコピーしても参照先が変わらないように絶対参照にすることが多いです。
また、列を丸ごと指定するような列参照にすることもできます。
= VLOOKUP ( B3, B6:D15, 3, FALSE )
・ 絶対参照
= VLOOKUP ( B3, $B$6:$D$15, 3, FALSE )
・ 列参照
= VLOOKUP ( B3, A:B, 2, FALSE )
第三引数「列番号」
第三引数は、検索結果を取得する列番号を指定します。
シート全体から見た列番号ではなく、第二引数で指定した範囲の列番号になります。
下図の場合、4行目に記載した番号が列番号に指定する番号です。
社員名を取得する場合、指定範囲は狭くても良い ( B6:C15 ) ですがそのままでも使用できます。
・ 売上を取得する場合 (下図参照)
= VLOOKUP ( B3, B6:D15, 3, FALSE )
・ 社員名を取得する場合
= VLOOKUP ( B3, B6:D15, 2, FALSE )
第四引数「検索方法」
第四引数は、指定範囲から取得する際の検索方法を指定します。
検索方法は 2種類あり、完全一致検索 ( FALSE )、または近似一致検索 ( TRUE ) を指定します。
なお、第四引数は省略することが可能ですが、省略した場合は近似一致検索となります。
・ 完全一致 ( FALSE )
検索値と完全に一致する値を検索します。
社員番号から社員名を取得する場合や、商品コードから金額を取得する場合に使用します。
一意の値を検索する場合は完全一致を指定しますので、実務では FALSE を使用することがほとんどです。
なお、完全一致する値が存在しない場合はエラーとなり、#N/A ( Not Available ： 利用できない・該当なし ) が表示されます。
・ 近似一致検索 ( TRUE ) ※ 省略可能
検索値と完全に一致しない場合は、検索値以下で最も近い値を返します。
数値や日付などで検索値に最も近い値を検索するときに使用します。
注意点としては、近似一致検索は検索列の上から値を比較するため、検索列の順番が昇順に並んでいる必要があります。
下のサンプルでは基準点数表から取得点数の合否を判定しています。
取得点数 ( 780 ) より小さく、かつ一番近い値 ( 700 ) が検索されます。
※ 基準点数が昇順で並んでいることが条件です。
= VLOOKUP ( B3, B6:C12, 2, TRUE )
・ TRUE は省略可能
= VLOOKUP ( B3, B6:C12, 2 )
よくあるエラー表示
VLOOKUP を使用していると発生するエラー表示を紹介します。
よく発生する表示内容なので対処方法も大体同じ方法が使用されています。
#N/A
第四引数の時に記載した Not Available の省略表示で、検索値が見つからない場合に表示されます。
主に完全一致検索の場合に発生しますが、近似一致検索でも検索列に満たない値で検索すると発生します。
対処方法は、IFERROR 関数と組み合わせることがほとんどです。
IFERROR 関数は後日作成する予定です。
= IFERROR ( VLOOKUP ( A2, A6:B15, 2, FALSE ), "該当なし" )
#REF!
#REF! は Reference の省略表示で、第三引数 ( 列番号 ) の参照先が無効な場合に表示されます。
検索範囲に指定した列数よりも大きい列番号を指定したときに発生します。
対処方法は、列番号を小さくするか、検索範囲を広げることになります。
以下は発生例
範囲に指定した列数 ( 2 列 ) よりも大きい列番号 ( 3 ) を指定すると発生
= VLOOKUP ( A2, A6:B15, 3, FALSE )
VLOOKUP を使う際の注意点
左方向の検索はできない
VLOOKUP は検索列が左端である必要があります。
先ほどの社員表では、社員名から社員番号を検索するような検索はできません。
社員名から社員番号を検索したい場合は、「社員番号と社員名の列を入れ替える」か「社員名の右列に社員番号をコピーする」など小技が必要になります。
現在は XLOOKUP を利用すると左方向の検索も可能となりましたが、XLOOKUP がなかった時代は左端に検索列を作成するためにいくつもの隠し列が作成されていたこともあります。
( 下のサンプル画像など )
列を追加すると数式が壊れることがある
VLOOKUP では、第三引数で列番号を指定する必要がありますが、検索範囲の途中に列を追加すると第二引数の範囲は Excel が自動調整してくれますが、第三引数の列番号は Excel が自動調整してくれないので参照する値がずれてしまうことがあります。
下のサンプルでは、範囲は自動調整 ( A6:B15 → A6:C15 ) されていますが、列番号 ( 2 ) は自動調整されていません。
現在推奨されている XLOOKUP では、検索範囲も戻り範囲 (表示する列) も Excel が自動調整してくれるため、新しく作成する場合は、XLOOKUP を利用する方がミスが発生しにくくなります。
よくある質問
VLOOKUP と XLOOKUP はどちらを使えばいいですか？
VLOOKUP を使う際の注意点にも記載しましたが、新しく作成する Excel ファイルであれば XLOOKUP がおすすめです。
ただし、既存の Excel ファイルでは VLOOKUP が使用されていることも多いため、どちらも覚えておくと役立ちます。
まとめ
VLOOKUP は、Excel で最も利用されている検索関数の一つです。
社員番号や商品コードなどを検索する際に便利で、今でも多くの業務で利用されています。
実務では、完全一致検索で使用されることがほとんどなので、第四引数は省略可能ですが常に設定する ( 完全一致の場合は FALSE ) ことをおすすめします。
また、新しく Excel ファイルを作成する場合は、VLOOKUP の後継である XLOOKUP もあわせて覚えておくと、より効率的にデータ検索を行えるようになります。