1. Home » 
  2. エクセル関数一覧表 » 
  3. Excel関数の目次 » 
  4. VLOOKUP関数の使い方(データを検索する)

VLOOKUP関数の使い方(データを検索する):Excel関数


スポンサードリンク


更新:2024/6/25;作成:2005/7/17

完全一致を検索する(検索の型:FALSE) エラー処理(#N/Aを表示しない)
近似一致を検索する(検索の型：TRUE) 検索範囲に名前を定義する場合
検索範囲にテーブルを利用する方法
検索表を使用しない方法 #N/Aエラー が返されるいろいろなケース
セルのデータ型を調べる VLOOKUP関数で複数条件の検索をする
VLOOKUP関数とMATCH関数の複合

はじめに：VLOOKUP関数のポイント

　ブイルックアップ
=VLOOKUP(検索値,範囲,列位置,検索の型)
　　　検索の型：「TRUE」もしくは[省略]（「1」と入力してもOK)
　　　　　　　　検索値が見つからない場合に、検索値未満で最も大きい値が使用されます。
　　　　　　　　(注)検索範囲の値は昇順に並んでおく必要があります。
　　　検索の型：「FALSE」(「0」と入力してもOK)
　　　　　　　　検索値と完全に一致する値だけが検索され、見つからない場合はエラー値 #N/A が返されます。
　　　　　　　　文字列の検索に適します。

[A]　完全一致で検索する　(検索の型：FALSEまたは0)

問題1の解答例

VLOOKUP関数のエラー処理(#N/Aを表示しない)　　　　topへ

IFERROR関数でエラー処理をする(Excel2007以降で使用できます)

IFNA関数でエラー処理をする(Excel2013以降で使用できます)

ISNA関数やISERROR関数でエラー処理をする

VLOOKUP関数の検索範囲にテーブルを利用する　　　　topへ

  1. H3:J6セルを選択します。
  2. [ホーム]タブの[テーブルとして書式設定]→[オレンジ,テーブル スタイル(淡色)10]を選択しました。
  3. H3:J6セルがテーブルに変換できました。
    テーブルデザイン タブのプロパティ グループのテーブル名に「商品コード」と入力します。
  4. C4:D6セルを選択して、
    C4セルに　=VLOOKUP($B4,商品コード,COLUMN(B1),FALSE)　と入力して、[Ctrl]+[Enter]で確定します。
  5. メリットはテーブルのデータが増えても数式の変更が必要ないということです。

VLOOKUP関数の検索範囲に定義した名前を利用する方法もあります　　　　topへ

  1. 検索範囲に名前を定義して、数式にその名前を使用すると数式が読みやすくなります。
    (数式を見て、すぐにその名前の範囲が参照されているのがわかります。)
    セル範囲 H4:J7 に『商品一覧表』と名前を定義します。
    （ポイント：検索列が一番左になるようにセル範囲を選択して、名前を定義します。）
    1. 名前ボックスを使って名前を定義する方法
      1. データの範囲 H4:J7 をドラッグして選択します。
      2. 「名前ボックス」に「商品一覧表」と入力し、　[Enter] キーを押します。
        これで、セル範囲に名前が定義されます。
    2. リボンから名前を定義する方法
      1. セル範囲 H4:J7 をドラッグして選択します。
      2. Excel2007以降は、[数式]タブの定義された名前グループの[名前の定義]を実行します。
        Excel2003以前は、メニューバーの［挿入]→[名前]→[定義]を選択します。
        新しい名前 ダイアログの名前に「商品一覧表」と入力し、[OK]ボタンをクリックします。
        これで、セル範囲に名前が定義されます。
  2. C4セルの数式は　=VLOOKUP($B4,商品一覧表,2,FALSE)
    D4セルの数式は　=VLOOKUP($B4,商品一覧表,3,FALSE)　とします。
  3. C4:D6セルを選択します。
    C4セルに　=VLOOKUP($B4,商品一覧表,COLUMN(B1),FALSE)と入力して、[Ctrl]+[Enter]で確定します。

[B]　近似一致を検索する場合　(検索の型：TRUE)　　　　　topへ
　　　　(注)検索値が昇順に並んでいる必要があります。近似値(検索値未満の最大値)を範囲の中から探します。
　　　　　　検索値が数値の範囲ではなく、その値に対応する時は[FALSE]とします。

問題2の解答例-1　

問題2の解答例-2　検索範囲に名前を定義して使う方法

[C]　検索表を使用しない方法　　(数式中に検索範囲のデータを書く方法)　　　　topへ

[D]　検索値が見つからず #N/Aエラー が返されるいろいろなケース　　　topへ

  1. 検索値にスペースが紛れ込んでいないか確認してみます。特に最後にスペースが含まれる場合は気づきにくいです。
    1. [ホーム]タブの[検索と選択]→[置換]を実行します。
      ショートカットキーは[Ctrl]+[H]です。
    2. 検索する文字列：半角または全角スペースを入力します。(画面では見た目の変化はないです)
      置換後の文字列：何も入力しません。
      『半角と全角を区別する』にチェックは入れません。(【オプション】をクリックすると表示されます)
      【すべて検索】ボタンをクリックし、【すべて置換】をクリックすると、一度に置換されます。
      確認しながら置換するときは、【次を検索】と【置換】を使います。
  2. 他のソフトなどで作成したデータで空白文字CHAR(160)が紛れ込んでくることがあります。
  3. 表示形式は文字列であるが、データは数値と認識されていることがあります。
    この様な場合は、=TYPE(A1)のようにTYPE関数を使ってセルのデータ形式を確認します。

セルのデータ型を調べる　　　topへ
　タイプ
=TYPE(データタイプ)
　　　数値は「1」 、テキストは「2」 、論理値は「4」
　　　エラー値は「16」 、配列は「64」を返します。

  1. C列にD列のようなそれぞれの値や数式を入力します。
  2. C5,C6はそれぞれの結果「FALSE」「#VALUE!」が表示されます。
  3. C7はTYPE関数の戻り値「64」が表示され、E7は戻り値「64」は数値なので、「1」が返されます。
    B C D E F
    2 データタイプ 入力データ C列の内容 TYPE関数の戻り値 E列の数式
    3 数値 10 ←10 1 ←=TYPE(C3)
    4 テキスト 文字です ←文字です 2 ←=TYPE(C4)
    5 論理値 FALSE ←=A1="moji" 4 ←=TYPE(C5)
    6 エラー #VALUE! ←=B1/B2 16 ←=TYPE(C6)
    7 配列 64 ←=TYPE({1,2;3,4}) 1 ←=TYPE(C7)

[E] VLOOKUP関数で複数条件の検索をする　　　topへ

左端列に検索条件を1つにまとめます(作業列を利用します)

SUMPRODUCT関数を使う事もできます。この場合B列は不要です。

Microsoft365をお使いの場合は XLOOKUP関数で対処できます。

VLOOKUP とMATCH の複合　　　topへ
　　　縦横の検索表から、一致する値を取り出すのに、VLOOKUP関数とMATCH関数を組み合わせて使う方法です。

解答例

Microsoft365をお使いの場合は XLOOKUP関数で対処できます。

スポンサードリンク

Home »  エクセル関数一覧表 »  VLOOKUP関数の使い方(データを検索する)

PageViewCounter
Counter
Since2006/2/27