Excel VLOOKUP関数

Excel VLOOKUP関数は、指定された値をテーブルまたは範囲の最初の列で垂直に一致させて検索し、同じ行の別の列から対応する値を返す強力なツールです。VLOOKUPは非常に便利ですが、初心者には理解が難しいことがあります。このチュートリアルでは、VLOOKUPの引数のステップバイステップの説明、有用な例、およびVLOOKUP関数を使用する際に遭遇する可能性のある一般的なエラーの解決策を提供することで、VLOOKUPをマスターする手助けをします。

• 引数のステップバイステップの説明
• 構文と引数
• VLOOKUPの例
  完全一致 vs.近似一致 | 複数条件検索を使用したVLOOKUP
• VLOOKUPの一般的なエラーと解決策
  #N/Aエラー | #VALUEエラー | #REFエラー | 不正な値

VLOOKUPの例.xlsx VLOOKUPのエラー.xlsx

関連動画

引数のステップバイステップの説明

上のスクリーンショットに示されているように、VLOOKUP関数は指定されたID番号に基づいてメールを検索するために使用されます。この例でVLOOKUPを使用する方法を、各引数をステップバイステップで分解して詳しく説明します。

ステップ1: VLOOKUP関数を開始する

結果を出力するセル（この場合はH6）を選択し、数式バーに次のテキストを入力してVLOOKUP関数を開始します。

=VLOOKUP(

ステップ2: 検索値を指定する

まず、VLOOKUP関数で検索する値（探しているもの）を指定します。ここでは、特定のID番号1005を含むセルG6を参照します。

=VLOOKUP(G6

ステップ3: テーブル範囲を指定する

次に、探している値と返したい値の両方を含むセル範囲を指定します。この場合、範囲B6:E12を選択します。数式は次のように表示されます。

=VLOOKUP(G6,B6:E12

=VLOOKUP(G6,$B$6:$E$12

ステップ4: 値を返したい列を指定する

次に、値を返したい列を指定します。

この例では、ID番号に基づいてメールを返す必要があるため、データ範囲の4番目の列から値を返すようにVLOOKUPに指示するために、ここで番号4を入力します。

=VLOOKUP(G6,B6:E12,4

ステップ5:近似一致または完全一致を見つける

最後に、近似一致を探しているのか、完全一致を探しているのかを決定します。

• 完全一致を見つけるには、最後の引数としてFALSEを使用する必要があります。
• 近似一致を見つけるには、最後の引数としてTRUEを使用するか、空白のままにします。

この例では、完全一致のためにFALSEを使用します。数式は次のようになります。

=VLOOKUP(G6,B6:E12,4,FALSE

Enterキーを押して結果を取得する

上記の例で各引数を一つずつ説明することで、VLOOKUP関数の構文と引数がより理解しやすくなります。

構文と引数

=VLOOKUP (lookup_value, table_array, col_index, [range_lookup])

• Lookup_value（必須）: 探している値（実際の値またはセル参照）。この値はtable_arrayの最初の列にある必要があります。
• Table_array（必須）: 検索値の列と返却値の列の両方を含むセル範囲。
• Col_index（必須）:返却値を含む列番号を表す整数。table_arrayの最も左の列から番号1で始まります。
• Range_lookup （オプション）: VLOOKUPが近似一致または完全一致を見つけるかどうかを決定する論理値。
  • 近似一致 - この引数を設定します TRUE, 1 または空白のままにします 空白.
    重要:近似一致を見つけるためには、table_arrayの最初の列の値が昇順に並んでいる必要があります。そうでない場合、VLOOKUPは誤った結果を返す可能性があります。
  • 完全一致 - この引数をFALSEまたは0に設定します。

例

このセクションでは、VLOOKUP関数をより包括的に理解するためのいくつかの例を示します。

例1: VLOOKUPにおける完全一致と近似一致

VLOOKUPを使用する際に完全一致と近似一致について混乱している場合、このセクションはその混乱を解消するのに役立ちます。

VLOOKUPにおける完全一致

この例では、範囲E6:E8に記載されたスコアに基づいて対応する名前を見つける予定です。セルF6に次の数式を入力し、AutoFillハンドルをF8までドラッグします。この数式では、最後の引数がFALSEとして指定されており、完全一致検索を実行します。

=VLOOKUP(E6,$B$6:$C$12,2,FALSE)

しかし、スコア98がデータ範囲の最初の列に存在しないため、VLOOKUPは#N/Aエラー結果を返します。

VLOOKUPにおける近似一致

上記の例を使用して、最後の引数をTRUEに変更すると、VLOOKUPは近似一致検索を実行します。一致が見つからない場合、検索値より小さい次の最大値を見つけて対応する結果を返します。

=VLOOKUP(E6,$B$6:$C$12,2,TRUE)

スコア98が存在しないため、VLOOKUPは98より小さい次の最大値である95を見つけ、スコア95の名前を最も近い結果として返します。

例2: 複数の条件を使用してVLOOKUPを使用する

このセクションでは、Excelで複数の条件を使用してVLOOKUPを使用する方法を示します。以下のスクリーンショットに示されているように、指定された名前（セルH5）と部門（セルH6）に基づいて給与を見つけようとしている場合、以下の手順に従ってください。

ステップ1: 検索列の値を連結するための補助列を追加する

この場合、名前列と部門列の値を連結するための補助列を作成する必要があります。

1. データ範囲の左側に補助列を追加し、この列にヘッダーを付けます。スクリーンショットを参照してください。
   
   
2. この補助列で、ヘッダーの下の最初のセルを選択し、次の数式を 数式バーに入力し、 Enter.
   

   =C6&" "&D6

   
   を押します。

   • 注: この数式では、アンパサンド（&）を使用して2つの列のテキストを結合し、単一のテキストを生成します。
   • これらの2つのセルの値は、間にスペースを入れて連結されます。
3. この結果セルを選択し、 AutoFillハンドル を下にドラッグして、この数式を同じ列の他のセルに適用します。
   
   

ステップ2: 指定された条件でVLOOKUP関数を適用する

結果を出力したいセルを選択し（ここではI7を選択）、数式バーに次の数式を入力してEnterを押します。

=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)

結果

一般的なVLOOKUPエラーと解決策

このセクションでは、VLOOKUPを使用する際に遭遇する可能性のある一般的なエラーを一覧表示し、それらを修正するための解決策を提供します。

#N/Aエラーが返される

VLOOKUPの最も一般的なエラーは#N/Aエラーであり、Excelが探していた値を見つけられなかったことを意味します。VLOOKUPが#N/Aエラーを返す理由をいくつか紹介します。

理由1: 検索値がtable_arrayの最初の列にない

Excel VLOOKUPの制限の一つは、左から右にしか検索できないことです。そのため、検索値はtable_arrayの最初の列にある必要があります。

以下のスクリーンショットに示されているように、指定された役職に基づいて名前を返したいと思っています。ここで、検索値（営業マネージャー）はtable_arrayの2番目の列にあり、返却値は検索列の左側にあるため、VLOOKUPは#N/Aエラーを返します。

解決策

このエラーを修正するために、以下の解決策を適用できます。

• 列を再配置する
  
  検索列をtable_arrayの最初の列に配置するために列を再配置できます。
• INDEXとMATCH関数を組み合わせて使用する
  
  ここでは、VLOOKUPの代替としてINDEXとMATCH関数を組み合わせてこの問題を解決します。

  =INDEX(B6:B12,MATCH(F6,C6:C12,0))

  
• XLOOKUP関数を使用する（Excel365、Excel2021以降のバージョンで利用可能）
  

  =XLOOKUP(F6,C6:C12,B6:B12)

理由2: 検索値が検索列に見つからない（完全一致）

VLOOKUPが#N/Aエラーを返す最も一般的な理由の一つは、探している値が見つからないことです。

以下の例に示されているように、E6の指定されたスコア98に基づいて名前を見つける予定です。しかし、このスコアはデータ範囲の最初の列に存在しないため、VLOOKUPは#N/Aエラー結果を返します。

解決策

このエラーを修正するために、以下の解決策を試すことができます。

• VLOOKUPが検索値より小さい次の最大値を検索するようにしたい場合、最後の引数FALSE（完全一致）をTRUE（近似一致）に変更します。詳細については、例1: VLOOKUPを使用した完全一致と近似一致を参照してください。
• 最後の引数を変更せずに検索値が見つからない場合にリマインダーを得るためには、VLOOKUP関数をIFERROR関数内に含めることができます。
  

  =IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")

理由3: 検索値が検索列の最小値より小さい（近似一致）

以下のスクリーンショットに示されているように、近似一致検索を実行しています。探している値（この場合はID番号1001）は検索列の最小値1002より小さいため、VLOOKUPは#N/Aエラーを返します。

解決策

ここに2つの解決策があります。

• 検索値が検索列の最小値以上であることを確認してください。
• Excelが検索値が見つからなかったことをリマインドするようにしたい場合は、VLOOKUP関数をIFERROR関数内にネストしてください。
  

  =IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")

理由4: 数値がテキストとしてフォーマットされている

以下のスクリーンショットに示されているように、この例の#N/Aエラー結果は、元のテーブルの検索セル（G6）と検索列（B6:B12）との間のデータ型の不一致によるものです。ここで、G6の値は数値であり、範囲B6:B12の値はテキストとしてフォーマットされた数値です。

解決策

この問題を解決するには、検索値を数値に戻す必要があります。ここに2つの方法があります。

• 数値への変換機能を適用する
  
  テキストを数値に変換したいセルをクリックし、このボタンを選択します  セルの横にある 数値への変換.
  
• テキストと数値の間でバッチ変換する便利なツールを適用する
  
  Kutools for Excelのテキストと数値の変換機能を使用すると、テキストから数値へ、またはその逆にセル範囲を簡単に変換できます。今すぐ30日間のフル機能の無料試用版を入手してください！

理由5: VLOOKUP数式を他のセルにドラッグするときにtable_arrayが一定でない

以下のスクリーンショットに示されているように、E6とE7に2つの検索値があります。F6で最初の結果を取得した後、VLOOKUP数式をセルF6からF7にドラッグすると、#N/Aエラー結果が返されます。これは、セル参照（B6:C12）がデフォルトで相対的であり、行を下に移動するにつれて調整されるためです。テーブル範囲がB7:C13に移動され、検索スコア73を含まなくなりました。

解決策

セル参照の行と列の前に$記号を追加してテーブル範囲をロックし、一定に保つ必要があります。Excelの絶対参照について詳しく知りたい場合は、このチュートリアルをご覧ください: Excel絶対参照（作成と使用方法）。

#VALUEエラーが返される

以下の条件がVLOOKUPに#VALUEエラー結果を返させる可能性があります。

理由1: 検索値が255文字を超えている

以下のスクリーンショットに示されているように、セルH4の検索値が255文字を超えているため、VLOOKUPは#VALUEエラー結果を返します。

解決策

この制限を回避するために、より長い文字列を処理できる異なる検索関数を適用できます。以下の数式のいずれかを試してください。

• INDEXとMATCH:
  

  =INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))

  
• XLOOKUP関数 （Excel365、Excel2021以降のバージョンで利用可能）:
  

  =XLOOKUP(H4,B5:B11,E5:E11)

理由2: col_index引数が1未満である

列インデックスは、返したい値を含むtable_arrayの列番号を指定します。この引数は、table_arrayの有効な列に対応する正の数である必要があります。

列インデックスが1未満（つまり、ゼロまたは負）を入力すると、VLOOKUPはtable_array内の列を見つけることができません。

解決策

この問題を修正するには、VLOOKUP数式の列インデックス引数がtable_arrayの有効な列に対応する正の数であることを確認してください。

#REFエラーが返される

このセクションでは、VLOOKUPが#REFエラーを返す理由を1つ挙げ、この問題の解決策を提供します。

理由: col_index引数が列数を超えている

以下のスクリーンショットに示されているように、table_arrayには4列しかありません。しかし、VLOOKUP数式で指定した列インデックスは5であり、table_arrayの列数を超えています。その結果、VLOOKUPは列を見つけることができず、最終的に#REFエラーを返します。

解決策

• 正しい列番号を指定する
  VLOOKUP数式の列インデックス引数がtable_arrayの有効な列に対応する数であることを確認してください。
• 指定された列ヘッダーに基づいて列番号を自動的に取得する
  テーブルに多くの列が含まれている場合、正しい列インデックス番号を決定するのに苦労するかもしれません。ここでは、指定された列ヘッダーに基づいて列の位置を見つけるためにMATCH関数をVLOOKUP関数にネストすることができます。

  =VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)

  注: 上記の数式では、MATCH("Email",B5:E5,0)関数を使用して、データ範囲B6:E12の"Email"列の列番号を取得します。ここでの結果は4であり、VLOOKUP関数のcol_indexとして使用されます。

不正な値が返される

VLOOKUPが正しい結果を返さない場合、以下の理由による可能性があります

理由1: 検索列が昇順に並んでいない

近似一致のために最後の引数をTRUEに設定した場合（または空白のままにした場合）、検索列が昇順に並んでいないと、結果の値が不正になる可能性があります。

解決策

検索列を昇順に並べることでこの問題を解決できます。これを行うには、以下の手順に従ってください。

1. 検索列のデータセルを選択し、データタブに移動して、ソートとフィルターグループで 最小から最大へソートをクリックします。
2. ソート警告ダイアログボックスで、選択範囲を拡張するオプションを選択し、OKをクリックします。

理由2: 列が挿入または削除された

以下のスクリーンショットに示されているように、元々返したい値はtable_arrayの4番目の列にあるため、col_index番号を4として指定しました。新しい列が挿入されると、結果列はtable_arrayの5番目の列になり、VLOOKUPが誤った列から結果を返す原因となります。

解決策

ここに2つの解決策があります。

• 返却列の位置に合わせて列インデックス番号を手動で変更できます。ここでの数式は次のように変更する必要があります。

  =VLOOKUP(H6,B6:F12,5,FALSE)

• 特定の列、例えばこの例ではEmail列から常に結果を返したい場合、以下の数式は、table_arrayから列が挿入または削除されても、指定された列ヘッダーに基づいて列インデックスを自動的に一致させるのに役立ちます。

  =VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)

その他の関数の注意事項

• VLOOKUPは左から右にしか値を検索しません。
  検索値は最も左の列にあり、結果値は検索列の右側の任意の列にある必要があります。
• 最後の引数を空白のままにすると、VLOOKUPはデフォルトで近似一致を使用します。
• VLOOKUPは大文字と小文字を区別しない検索を行います。
• 複数の一致がある場合、VLOOKUPはtable_array内の行の順序に基づいて最初に見つけた一致のみを返します。

関連記事