スプレッドシートに関する既知の情報がある場合は、
VLOOKUP を使用して、関連する情報を行単位で検索できます。たとえばオレンジを購入したい場合は、VLOOKUP で価格を検索できます。
使用例
VLOOKUP("Apple",table_name!fruit,table_name!price)
構文
VLOOKUP(検索キー, 範囲, 番号, 並べ替え済み)
検索キー: 範囲の最初の列で検索する値。範囲: 検索対象のデータ列。番号: 結果を取り出す対象のデータ列。並べ替え済み: [省略可]検索キーに一致するものを検出する方法。FALSE: 完全一致を検索する場合、この値をおすすめします。TRUE: 近似照合で並べ替え済みが指定されていない場合のデフォルトです。
ヒント: 近似照合を使用する前に、検索キーを昇順で並べ替えてください。この操作を行わないと、間違った戻り値が返される可能性があります。間違った戻り値が返される理由についてご確認ください。
構文
=VLOOKUP(検索キー, 範囲, 番号, [並べ替え済み])
入力
検索キー: 範囲の最初の列で検索する値。範囲: 検索対象の上限値と下限値。番号: 範囲の戻り値を含む列の番号。番号には正の整数を指定してください。並べ替え済み: 省略可能な入力。次のいずれかを指定します。FALSE= 完全一致。この値をおすすめします。TRUE= 近似照合。並べ替え済みが指定されていない場合は、この値がデフォルトです。
重要: 近似照合を使用する前に、検索キーを昇順で並べ替えてください。この操作を行わないと、間違った戻り値が返される可能性があります。間違った戻り値が返される理由についてご確認ください。
戻り値
選択した
詳細な技術情報:
範囲の中で最初に一致した値。例:
=VLOOKUP(G9, B4:D8, 3, FALSE)
=VLOOKUP("Apple", B4:D8, 3, TRUE)
| 入力 | 説明 |
検索キー |
範囲の最初の列で検索する値です。エラー以外の値を返すには、検索キーが範囲の最初の列にある必要があります。セル参照も使用できます。簡単な確認方法:
検索キーが B3 にある場合、範囲は列 B から始まる必要があります。 |
範囲 |
範囲では次のことが行われます。
エラー以外の値を返すには、検索キーが
範囲の最初の列にある必要があります。簡単な確認方法:
検索キーが B3 にある場合、範囲は列 B から始まる必要があります。 |
番号 |
「列番号」とも呼ばれ、戻り値を含む
範囲の列の番号です。
範囲を設定すると、
VLOOKUP によって検索されるのは番号が 1 のときは検索キー列のみとなり、番号の値によってそれより右側の列も検索されます。ヒント:
VLOOKUP を使用するときは、範囲の各列に左から右の順で番号（先頭は 1）が付けられると想定してください。 |
並べ替え済み |
この入力は任意です。指定できる値は
TRUE と FALSE です。
次のことを強くおすすめします。
|
| 出力 | 説明 |
| 戻り値 |
ユーザーの入力に基づいて
VLOOKUP が返す値です。各 VLOOKUP 関数が返す値は 1 つのみです。
予期しない値または #N/A や #VALUE! などのエラーが返された場合は、トラブルシューティングを開始してください。#N/A を別の値に置き換えたい場合は、VLOOKUP() で IFNA() を使用する方法についてご確認ください。
|
VLOOKUP の基本的な例:
VLOOKUP でさまざまな検索キーを使用する
VLOOKUP を使用して、オレンジとリンゴの価格を確認します。
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 を使用するときは、範囲の各列に左から右の順で番号（先頭は 1）が付けられると想定してください。ターゲット情報を検索するには、その列番号を指定する必要があります（例: 個数の場合は列 2）。
番号 = 2範囲の 2 番目の列でオレンジの個数を確認します。 |
=VLOOKUP(G3, B4:D8, 2, FALSE)
戻り値 = 5
|
VLOOKUP での完全一致または近似照合
- 正確な一致を検索するには、
VLOOKUPの完全一致を使用します。 - 近似の一致を検索するには、
VLOOKUPの近似照合を使用します。
完全一致ではなく、最も近い一致を検索するときは、近似照合（
並べ替え済み = 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() の詳細をご覧ください。
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 を複数の条件に直接適用することで、複数の既存列を結合できます。
| 1. 「&」を使用して姓と名を組み合わせる場合は、補助列を作成できます。 | B4 で「=C4&D4」と指定して B8 にドラッグすると、補助列が表示されます。 |
| 2. セル参照 B7（JohnLee）を検索キーとして使用します。 |
=VLOOKUP(B7, B4:E8, 4, FALSE)
戻り値 =「Support」
|
VLOOKUP でワイルドカードまたは部分一致を使用する
VLOOKUP ではワイルドカードや部分一致も使用できます。次のワイルドカード文字を使用できます。- 疑問符「?」は任意の 1 文字に一致します。
- アスタリスク「*」は任意の文字列に一致します。
VLOOKUP でワイルドカードを使用するには、完全一致（並べ替え済み = FALSE）を使用する必要があります。
| "St*" は St で始まる文字列の検索に使用します（「Steve」、「St1」、「Stock」、「Steeeeeeve」など、文字数に関係なく）。 |
=VLOOKUP("St*", B4:D8, 3, FALSE)
戻り値 =「Marketing」
|
エラーのトラブルシューティングとおすすめの方法:
間違った戻り値-
予期しない値が返される:
並べ替え済みをTRUEに設定しても、範囲の最初の列を数値順またはアルファベット順で昇順に並べ替えていない場合は、並べ替え済みをFALSEに変更します。 - VLOOKUP では最初の一致が返される:
VLOOKUPは最初の一致のみを返します。一致する検索キーが複数あると、値は返されますが、予期する値とは異なる場合があります。 - データがクリーンでない: 先頭や末尾にスペースを含む値は、見た目は似ていても、
VLOOKUPでは扱い方が異なります。たとえば、次の記述はVLOOKUPではそれぞれ異なる扱いになります。- " Apple"
- "Apple "
- "Apple"
予期する結果を得るには、
VLOOKUP を使用する前にスペースを削除してください。詳しくは、おすすめの方法をご覧ください。
- 近似照合（
並べ替え済み=TRUE）を使用していて、VLOOKUPの検索キーが最初の列の最小値より小さい場合、VLOOKUPは #N/A を返します。 - 完全一致（
並べ替え済み=FALSE）を使用している場合、VLOOKUPの検索キーの完全一致が最初の列で見つからないことがあります。検索キーが最初の列で見つからないときに #N/A が返されないようにしたい場合は、IFNA() 関数を使用できます。
範囲を、その最大列数より大きい数値を使用して- テーブル全体ではなく、選択した
範囲の列をカウントします。 - 0 ではなく 1 からカウントします。
#VALUE! エラーが表示された場合、次のことを行っている可能性があります。
番号のテキストまたは列名を正しく入力していない。番号に 1 より小さい数値を入力した。番号は 1 以上で、範囲の最大列数よりも小さい数値である必要があります。VLOOKUPが検索できるのは番号= 1 のときは検索キー列のみとなり、番号の値によってそれより右側の列も検索できます。
重要: 番号に指定できるのは数値のみです。
検索キーがテキストデータの場合、キー内で引用符を付け忘れた可能性があります。
| 推奨 | 理由 |
範囲の絶対参照を使用する |
次を使用してください。
次を使用しないでください。
絶対参照により、
範囲をコピーまたはドラッグするときに想定外の変更が防止されます。 |
近似照合（並べ替え済み = TRUE など）を使用するときは、最初の列を昇順で並べ替える |
近似照合（並べ替え済み = TRUE）を使用する場合は、最初の列を昇順で並べ替える必要があります。この操作を行わないと、間違った戻り値が返される可能性が高くなります。並べ替えの方法についてご確認ください。 |
VLOOKUP を使用する前にデータをクリーニングする |
VLOOKUP を使用する前に、データのクリーニングを忘れないようにしてください。データがクリーンでないと、VLOOKUP が予期しない値を返すことがあります。クリーンでないデータのよくある例を次に示します。
先頭と末尾のスペースを削除するには、[データ]
 [データ クリーンアップ] [空白文字を削除] を使用できます。 |
| 数値または日付の値をテキストとして格納しない |
VLOOKUP の範囲の最初の列（検索キー列など）の日付の値または数値がテキスト値として格納されないようにします。テキスト値になっていると、予期しない戻り値が返されることがあります。
|