VLOOKUP

 
スプレッドシートに関する既知の情報がある場合は、VLOOKUP を使用して、関連する情報を行単位で検索できます。たとえばオレンジを購入したい場合は、VLOOKUP で価格を検索できます。
VLOOKUP formula example
BigQuery の VLOOKUP

垂直方向の検索です。検索列内で一致が見つかった位置のデータ列の値を返します。

使用例

VLOOKUP("Apple",table_name!fruit,table_name!price)

構文

VLOOKUP(検索キー, 範囲, 番号, 並べ替え済み)

  • 検索キー: 検索列で検索対象になる値。
  • 検索列: 検索対象のデータ列。
  • 結果列: 結果を取り出す対象のデータ列。
  • 並べ替え済み: [省略可] 検索キーと一致するものを検出する方法。
    • FALSE: 完全一致を検索する場合、この値をおすすめします。
    • TRUE: 近似照合で並べ替え済みが指定されていない場合のデフォルトです。
      ヒント: 近似照合を使用する前に、検索キーを昇順で並べ替えてください。この操作を行わないと、間違った戻り値が返される可能性があります。間違った戻り値が返される理由についてご確認ください。

ヒント: BigQuery でより柔軟なデータベース クエリを行うには、XLOOKUP を使用します

構文

=VLOOKUP(検索キー, 範囲, 番号, [並べ替え済み])

入力

  1. 検索キー: 範囲の最初の列で検索する値。
  2. 範囲: 検索対象の上限値と下限値。
  3. 番号: 範囲の戻り値を含む列の番号。番号には正の整数を指定してください。
  4. 並べ替え済み: 省略可能な入力。次のいずれかを指定します。
    • FALSE = 完全一致。この値をおすすめします。
    • TRUE = 近似照合。並べ替え済みが指定されていない場合は、この値がデフォルトです。
      重要: 近似照合を使用する前に、検索キーを昇順で並べ替えてください。この操作を行わないと、間違った戻り値が返される可能性があります。間違った戻り値が返される理由についてご確認ください。

戻り値

選択した範囲の中で最初に一致した値。
詳細な技術情報:
例:
=VLOOKUP(G9, B4:D8, 3, FALSE)
=VLOOKUP("Apple", B4:D8, 3, TRUE)
入力 説明
検索キー
範囲の最初の列で検索する値です。エラー以外の値を返すには、検索キーが範囲の最初の列にある必要があります。セル参照も使用できます。
簡単な確認方法: 検索キーが B3 にある場合、範囲は列 B から始まる必要があります。
範囲
範囲では次のことが行われます。
  • 指定された検索キーを範囲の最初の列で検索します。
  • VLOOKUP番号で指定された列の値を返します。名前付き範囲を使用することもできます。
エラー以外の値を返すには、検索キーが範囲の最初の列にある必要があります。
簡単な確認方法: 検索キーが B3 にある場合、範囲は列 B から始まる必要があります。
番号
「列番号」とも呼ばれ、戻り値を含む範囲の列の番号です。
  • 最も小さい番号は 1 です。
  • 最も大きい番号は、その範囲における列の最大数です。
範囲を設定すると、VLOOKUP によって検索されるのは番号が 1 のときは検索キー列のみとなり、番号の値によってそれより右側の列も検索されます。
ヒント: VLOOKUP を使用するときは、範囲の各列に左から右の順で番号(先頭は 1)が付けられると想定してください。
並べ替え済み
この入力は任意です。指定できる値は TRUEFALSE です。
  • 並べ替え済みTRUE の場合、VLOOKUP では近似照合が使用されます。
    重要: 近似照合を使用する前に、検索キーを昇順で並べ替えてください。この操作を行わないと、予期しない値が返されることがあります。間違った戻り値が返される理由についてご確認ください。
  • 並べ替え済みFALSE の場合、VLOOKUP では完全一致が使用されます。

  • 並べ替え済みが指定されていない場合、デフォルトは TRUE になります。
次のことを強くおすすめします。
  • 並べ替え済みには FALSE を使用します。検索キー列が並べ替えられているかどうかに関係なく動作が一貫しているためです。
  • 常に並べ替え済みを指定します。入力は任意ですが、検索結果が読みやすくなります。

 

出力 説明
戻り値
ユーザーの入力に基づいて VLOOKUP が返す値です。各 VLOOKUP 関数が返す値は 1 つのみです。
  • 一致する検索キーの値が複数ある場合は、検索キー列で最初に一致した検索キーに関連付けのある値が戻り値列に返されます。
  • #N/A が返された場合は、値が見つからないことを意味します。
予期しない値または #N/A や #VALUE! などのエラーが返された場合は、トラブルシューティングを開始してください。#N/A を別の値に置き換えたい場合は、VLOOKUP() で IFNA() を使用する方法についてご確認ください。

VLOOKUP の基本的な例:

VLOOKUP でさまざまな検索キーを使用する

VLOOKUP を使用して、オレンジとリンゴの価格を確認します。

VLOOKUP on different search keys example
説明:

VLOOKUP では、"Apple" や "Orange" など、さまざまな検索キーを使用できます。

エラー以外の値を返すには、検索キーが範囲の最初の列にある必要があります。検索キーの値を入力しない場合は、「G9」などのセル参照を使用することもできます。
検索キー: "Orange"
=VLOOKUP("Orange", B4:D8, 3, FALSE)
戻り値 = $1.01
検索キー: "Apple"
=VLOOKUP("Apple", B4:D8, 3, FALSE)
戻り値 = $1.50
G9 の「Apple」のセル参照を使用した検索キー
=VLOOKUP(G9, B4:D8, 3, FALSE)
戻り値 = $1.50

VLOOKUP でさまざまな列番号を使用する

VLOOKUP を使用して、番号 2 の列でオレンジの個数を確認します。
VLOOKUP on different column indexes example
説明:
VLOOKUP を使用するときは、範囲の各列に左から右の順で番号(先頭は 1)が付けられると想定してください。ターゲット情報を検索するには、その列番号を指定する必要があります(例: 個数の場合は列 2)。
番号 = 2
範囲の 2 番目の列でオレンジの個数を確認します。
=VLOOKUP(G3, B4:D8, 2, FALSE)
戻り値 = 5

VLOOKUP での完全一致または近似照合

  • 正確な一致を検索するには、VLOOKUP の完全一致を使用します。
  • 近似の一致を検索するには、VLOOKUP の近似照合を使用します。
VLOOKUP exact match or approximate match example
説明:
完全一致ではなく、最も近い一致を検索するときは、近似照合(並べ替え済み = TRUE)を使用します。
表に存在しない ID 102 を検索する場合、近似照合によって、1 つ前の ID 101 が返されます。これは、検索キー列で 101 が最も近く、102 より小さい値でもあるためです。
近似照合では、検索キーよりも大きい値が見つかるまで検索キー列を下方向に検索します。検索キーより大きい値が見つかると、その前の行で停止し、その行の値を戻り値列に返します。つまり、検索キー列が昇順で並べ替えられていないと、間違った戻り値が返される可能性が高くなります。
重要: 近似照合を使用する前に、正しい値が返されるように検索キーを昇順で並べ替えてください。この操作を行わないと、予期しない値が返されることがあります。
完全一致(並べ替え済み = FALSE など)を使用すると、完全に一致する値が返されます。たとえば、ID 103 のフルーツ名は「Banana」です。完全一致がない場合は #N/A エラーが返されます。動作が予測しやすいので、完全一致を使用することをおすすめします。
完全一致
=VLOOKUP(G6, A4:D8, 2, FALSE)
戻り値 =「Apple」
近似照合
=VLOOKUP(G3, A4:D8, 2, TRUE)
または
=VLOOKUP(G3, A4:D8, 2)
戻り値 =「Banana」

VLOOKUP の一般的な応用例

VLOOKUP からのエラー値を置き換える

検索キーが存在しないときに VLOOKUP から返されるエラー値を別の値に置き換えたい場合があります。その場合、#N/A が返されないようにするには、IFNA() 関数を使用して #N/A を置き換えることができます。IFNA() の詳細をご覧ください。
Replace error value from VLOOKUP example
VLOOKUP で「Pencil」を検索した場合、この検索キーは「Fruit」列にないため、#N/A が返されます。
IFNA() は、#N/A エラーを、関数内で指定された 2 つ目の入力に置き換えます。この場合は "NOT FOUND" です。
=IFNA(VLOOKUP(G3, B4:D8, 3, FALSE),"NOT FOUND")
戻り値 =「NOT FOUND」

ヒント: 他のエラー(#REF! など)を置き換えたい場合は、IFERROR() の詳細をご覧ください。

VLOOKUP で複数の条件を使用する

VLOOKUP を複数の条件に直接適用することはできません。代わりに、新しい補助列を作成して VLOOKUP を複数の条件に直接適用することで、複数の既存列を結合できます。
VLOOKUP with multiple criteria example
1. 「&」を使用して姓と名を組み合わせる場合は、補助列を作成できます。 B4 で「=C4&D4」と指定して B8 にドラッグすると、補助列が表示されます。
2. セル参照 B7(JohnLee)を検索キーとして使用します。
=VLOOKUP(B7, B4:E8, 4, FALSE)
戻り値 =「Support」

VLOOKUP でワイルドカードまたは部分一致を使用する

VLOOKUP ではワイルドカードや部分一致も使用できます。次のワイルドカード文字を使用できます。
  • 疑問符「?」は任意の 1 文字に一致します。
  • アスタリスク「*」は任意の文字列に一致します。
VLOOKUP でワイルドカードを使用するには、完全一致(並べ替え済み = FALSE)を使用する必要があります。
VLOOKUP with wildcard example
"St*" は St で始まる文字列の検索に使用します(「Steve」、「St1」、「Stock」、「Steeeeeeve」など、文字数に関係なく)。
=VLOOKUP("St*", B4:D8, 3, FALSE)
戻り値 =「Marketing」

エラーのトラブルシューティングとおすすめの方法:

間違った戻り値
  • 予期しない値が返される: 並べ替え済みTRUE に設定しても、範囲の最初の列を数値順またはアルファベット順で昇順に並べ替えていない場合は、並べ替え済みを FALSE に変更します。

  • VLOOKUP では最初の一致が返される: VLOOKUP は最初の一致のみを返します。一致する検索キーが複数あると、値は返されますが、予期する値とは異なる場合があります。
  • データがクリーンでない: 先頭や末尾にスペースを含む値は、見た目は似ていても、VLOOKUP では扱い方が異なります。たとえば、次の記述は VLOOKUP ではそれぞれ異なる扱いになります。
    • " Apple"
    • "Apple "
    • "Apple"
予期する結果を得るには、VLOOKUP を使用する前にスペースを削除してください。
詳しくは、おすすめの方法をご覧ください。
#N/A
  • 近似照合(並べ替え済み = TRUE)を使用していて、VLOOKUP の検索キーが最初の列の最小値より小さい場合、VLOOKUP は #N/A を返します。
  • 完全一致(並べ替え済み = FALSE)を使用している場合、VLOOKUP の検索キーの完全一致が最初の列で見つからないことがあります。検索キーが最初の列で見つからないときに #N/A が返されないようにしたい場合は、IFNA() 関数を使用できます。
#REF!
範囲を、その最大列数より大きい数値を使用して指定してしまうことがあります。そうした誤りを回避するには、次のことを行います。
  • テーブル全体ではなく、選択した範囲の列をカウントします。
  • 0 ではなく 1 からカウントします。
#VALUE!
#VALUE! エラーが表示された場合、次のことを行っている可能性があります。
  • 番号のテキストまたは列名を正しく入力していない。
  • 番号に 1 より小さい数値を入力した。番号は 1 以上で、範囲の最大列数よりも小さい数値である必要があります。VLOOKUP が検索できるのは番号 = 1 のときは検索キー列のみとなり、番号の値によってそれより右側の列も検索できます。

重要: 番号に指定できるのは数値のみです。

#NAME?
  • 検索キーがテキストデータの場合、キー内で引用符を付け忘れた可能性があります。
おすすめの方法

 

推奨 理由
範囲の絶対参照を使用する
次を使用してください。
  • VLOOKUP範囲の絶対参照
  • VLOOKUP(G3, $B$3:$D$7, 3, FALSE)
次を使用しないでください。
  • VLOOKUP(G3, B3:D7, 3, FALSE)
絶対参照により、範囲をコピーまたはドラッグするときに想定外の変更が防止されます。
近似照合(並べ替え済み = TRUE など)を使用するときは、最初の列を昇順で並べ替える 近似照合(並べ替え済み = TRUE)を使用する場合は、最初の列を昇順で並べ替える必要があります。この操作を行わないと、間違った戻り値が返される可能性が高くなります。並べ替えの方法についてご確認ください。
VLOOKUP を使用する前にデータをクリーニングする
VLOOKUP を使用する前に、データのクリーニングを忘れないようにしてください。データがクリーンでないと、VLOOKUP が予期しない値を返すことがあります。クリーンでないデータのよくある例を次に示します。
  • 先頭のスペース: " apple"
  • 末尾のスペース: "apple "
  • 空白またはスペース: "" と " " は同じではありません。
先頭と末尾のスペースを削除するには、[データ] 次に [データ クリーンアップ] 次に [空白文字を削除] を使用できます。
数値または日付の値をテキストとして格納しない
VLOOKUP の範囲の最初の列(検索キー列など)の日付の値または数値がテキスト値として格納されないようにします。テキスト値になっていると、予期しない戻り値が返されることがあります。
  1. スプレッドシートの上部にある検索キー列を選択します。
  2. [表示形式] メニュー 次に [数字] をタップします。
  3. 必要なデータの種類に応じてオプションを選択します。
    • 日付
    • 数値

さらにサポートが必要な場合

次の手順をお試しください。

true
ラーニング センターにアクセス

職場や学校で Google ドキュメントなどの Google のサービスを利用している場合は、役に立つヒント、チュートリアル、テンプレートをお試しください。Office をインストールせずに Office ファイルを操作する方法、プロジェクト計画やチーム カレンダーを動的に作成する方法、受信トレイを自動的に整理する方法などをご確認いただけます。

検索
検索をクリア
検索を終了
Google アプリ
メインメニュー