このトピックでは、関数の誤った結果の最も一般的な VLOOKUP の理由について説明し、代わりに INDEX と MATCH を使用するための推奨事項を示します。
ヒント: また、#NA の問題でよくある原因について便利な PDF ファイルで説明する「クイック リファレンス カード: VLOOKUP トラブルシューティング ヒント」も参照してください。 この PDF を他のユーザーと共有したり、印刷して手元に置いたりすることもできます。
問題:参照値が table_array 引数の最初の列にない
VLOOKUP の制約の 1 つは、テーブルの配列の左端の列にある値のみを参照する点です。 参照値が配列の最初の列にない場合、#N/A エラーが表示されます。
次のテーブルでは、Kale に販売された単位数を取得しようとしています。
検索値 “Kale” が table_array 引数 A2:C10 の 2 列目 (Produce) に表示されるため、#N/A エラーが発生します。 この場合、Excel は B 列ではなく、A 列で検索しています。
解決方法:正しい列を参照するように、VLOOKUP を調整することができます。 調整できない場合は、列を移動します。 セル値が他の計算結果である大規模、または複雑なスプレッドシートの場合や、列を移動できない他の論理的な理由がある場合、列の移動は実用的ではないことがあります。 この問題を解決するには、INDEX 関数と MATCH 関数を組み合わせて使用し、ルックアップ テーブル内の列の位置にかかわらず、列の値を参照する方法があります。 次のセクションを参照してください。
代わりに、INDEX/MATCH の使用を検討してください
INDEX と MATCH は、VLOOKUP がニーズを満たさない多くのケースに適したオプションです。 INDEX/MATCH の主な利点は、ルックアップ テーブル内の任意の位置にある列の値を参照できることです。 INDEX は、指定されたテーブルや範囲から、その位置に応じて値を返します。 MATCH は、テーブルや範囲内の値の相対的な位置を返します。 数式に INDEX と MATCH を両方使用すると、テーブル/配列内の値の相対的な位置を指定することで、テーブル/配列内の値を参照できます。
VLOOKUP よりも INDEX/MATCH を使用する方が多くの利点があります。
-
INDEX と MATCH では、戻り値が検索列と同じ列内にある必要はありません。 これは、戻り値が指定された範囲内になければならない VLOOKUP とは異なります。 これはどのような意味があるでしょうか。 VLOOKUP では、戻り値を含む列番号を知る必要があります。 あまり大きな問題ではないかもしれませんが、大きなテーブルで、列数を数える必要があると面倒なことがあります。 また、テーブルに列を追加または削除すると、数え直し、col_index_num 引数を更新する必要があります。 INDEX と MATCH の場合、参照列は戻り値を含む列とは異なるので、数える必要はありません。
-
INDEX と MATCH では、配列で行または列、または両方を指定することができます。 そのため、垂直方向と水平方向の両方で値を検索できます。
-
INDEX と MATCH を使用して、任意の列の値を検索できます。 テーブルの最初の列内にある値のみを検索できる VLOOKUP とは異なり、INDEX と MATCH は、最初の列、最後の列、またはその間の任意の列にある参照値でも検索できます。
-
INDEX と MATCH には、戻り値を含む列を動的に参照できる柔軟性があります。つまり、INDEX と MATCH を分割しなくても、テーブルに列を追加できます。 一方、テーブルに列を追加する必要がある場合、VLOOKUP はテーブルの静的参照なので機能しなくなります。
-
INDEX と MATCH はより柔軟に対応付けることができます。INDEX と MATCH では、参照値と完全に一致する値、より大きい値、またはより小さい値を検索できます。 VLOOKUP は、最も近い値 (既定) または完全に一致する値のみを検索します。 また、VLOOKUP は、既定ではテーブル配列の最初の列がアルファベット順に並べ替えられていると想定しています。テーブルがそのようにセットアップされていない場合、検索している値でなくても、テーブル内の最初の最も近い値を返します。
構文
INDEX/MATCH の構文を構築するには、INDEX 関数の array/reference 引数を使用し、その中に MATCH 構文を入れ子にする必要があります。 これは、以下のフォームになります。
=INDEX(array または reference, MATCH(lookup_value,lookup_array,[match_type])
前述の例の VLOOKUP を INDEX/MATCH で置き換えてみましょう。 構文は次のようになります。
=INDEX(C2:C10,MATCH(B13,B2:B10,0))
簡単な文章で説明すると、次のようになります。
=INDEX(C2:C10 から値を返す, それは MATCH(Kale, B2:B10 配列のどこかにある, 戻り値は Kale に対応する最初の値である位置))
この数式は、Kale (B7 内) に対応する C2:C10 の最初の値を検索し、C7 (100) の値を返します。これは、Kale に一致する最初の値です。
問題:完全一致が見つからない
range_lookup 引数が FALSE で、VLOOKUP でデータ内に完全一致を見つけられない場合、#N/A エラーが返されます。
解決方法: 関連するデータがスプレッドシート内に確実にあり、VLOOKUP で見つけられない場合は、参照対象のセルに非表示のスペースや印刷されない文字が含まれていないことを検証する時間を確保してください。 また、セルが正しいデータ型に従っていることを確認してください。 たとえば、数値を含むセルは、文字列ではなく、数値として書式が設定されている必要があります。
また、CLEAN または TRIM 関数のいずれかを使用して、セル内のデータをクリーンアップすることを検討してください。
問題:参照値が、配列内の最小値よりも小さい
range_lookup 引数が TRUE に設定され、参照値が配列の最小値よりも小さい場合、#N/A エラーが表示されます。 TRUE の場合、配列内の近似の一致が検索され、参照値よりも小さい最も近い値が返されます。
次の例では、参照値は 100 ですが、B2:C10 の範囲には 100 未満の値がないため、エラーになります。
解決方法:
-
必要に応じて参照値を修正します。
-
参照値を変更できず、値のマッチングを柔軟にする必要がある場合は、VLOOKUP ではなく この記事の上記のセクションにある INDEX/MATCH を使用することをお勧めします。 INDEX/MATCH の場合、参照値よりも大きい値、小さい値、または一致する値を検索できます。 VLOOKUP ではなく INDEX/MATCH を使用する方法については、このトピックの前のセクションを参照してください。
問題:参照列が昇順で並べ替えられていない
range_lookup 引数が TRUE に設定され、参照列のいずれかが昇順 (A-Z) で並べ替えられていない場合、#N/A エラーが表示されます。
解決方法:
-
完全な一致を検索するように VLOOKUP 関数を変更します。 この場合、range_lookup 引数を FALSE に設定します。 FALSE の場合、並べ替えの必要はありません。
-
並べ替えられていないテーブルで値を検索するには、INDEX/MATCH 関数を使用します。
問題:値が大きな浮動小数点数
セルに時間値または大きな 10 進数がある場合、浮動小数点精度により Excel は #N/A エラーを返します。 浮動小数点数は、小数点以下の数値です (Excel は時間値を浮動小数点数として保存することにご注意ください)。 (Excel は大きい浮動小数点数を格納できません。.) そのため、関数が正常に動作するには、非常に大きな浮動小数点数を持つ数値を小数点以下 5 桁に丸める必要があります。
解決方法:数字を短くするには、ROUND 関数を使用して小数点以下の桁数を 5 桁以下に丸めます。
補足説明
Excel Tech Communityで、いつでも専門家に質問できます。また、コミュニティでは、サポートを受けられます。
