VLOOKUPが失敗することがある理由

VLOOKUPは信頼性の高い関数ですが、いくつか決まったパターンで失敗します。ルールを知っていれば、ほとんどのエラーは謎ではありません。

このレッスンでは、検索が機能しない一般的な原因と、それぞれの正確な修正方法を学びます。これらを知っていれば、分かりにくい#N/Aや#REF!エラーも、すばやく簡単に修正できます。

VLOOKUPは、table_arrayの左端の列だけを検索し、値は右側から返せます。値を検索して、その左側にある値を返すことはできません。

たとえば、IDが列Cにあり、必要な名前が列Aにある場合、VLOOKUPでは左側に戻って検索することができません。列を並べ替えて検索列を最初にするか、どの方向にも検索できるINDEX-MATCHまたはXLOOKUPを使用します。

col_index_numはシート上の位置ではなく、table_arrayの左端から数えます。よくある間違いは、シートの列記号をそのまま番号として使うことです。

範囲がC1:F10で、列Fを指定したい場合、これは範囲内では4列目なので、インデックスは6ではなく4です。反対側から数えると間違った項目が返され、番号が範囲の幅を超えると#REF!エラーになります。

col_index_numがtable_arrayの列数より大きい場合、VLOOKUPは#REF!を返します。

たとえば、3列の範囲A1:C10から5列目を求めることはできません：

必要な列が含まれるようにtable_arrayの範囲を広げるか、範囲内に実在する列番号にインデックスを修正してください。

4番目の引数を省略すると、デフォルトでTRUE（近似一致）になります。並べ替えられていないリストでは、エラーにならずに近くの間違った値が返されるため、気付きにくい問題になります。

修正方法は簡単で、習慣にするべきものです。完全一致の検索では必ずFALSEを追加してください。

検索値が"A100"の場合、末尾にスペースがある"A100 "とは一致しません。インポートしたデータには、このような見えない違いが数多く含まれています。

値が明らかに存在するのに#N/Aになる場合は、両方の値をTRIMで整えて、余分なスペースを削除してください：

検索値が数値の100で、表ではコードがテキストの"100"として保存されている場合（またはその逆）、両者は一致せず、#N/Aになります。

テキストであることを示す小さな緑色の三角形や、左揃えで表示された数値を確認してください。テキストをVALUE()で囲んで数値に変換するか、数値に&""を連結してテキストに変換し、両方の値の型をそろえます。

table_arrayを固定し忘れると、数式を下方向にコピーしたときに範囲がデータからずれていきます。2行目のA1:C100が、3行目ではA2:C101、その次はA3:C102となり、途中の行を見落としてしまいます。

絶対参照を使って修正すると、検索値だけが移動し、表の範囲は固定されたままになります：

それぞれのエラーは原因を示しています：

• #N/A - 値が見つかりませんでした（一致しない、スペースがある、型が違う、または本当に存在しない）
• #REF! - col_index_numが範囲より大きい、または参照していたセルが削除されています
• #VALUE! - 引数の型が間違っています。たとえば、列番号が負の数または0になっています
• #NAME? - VLOOKPのように、関数名のスペルが間違っています

エラーを意味と照らし合わせるだけで、問題の半分はすでに解決できています。

デバッグ中は、検索をラップして、エラーをそのまま表示する代わりに明確なメッセージをユーザーに表示することもできます。IFERRORはあらゆるエラーを受け取り、代わりに指定したテキストを返します。

これは根本的な原因を修正するものではないため、検索が失敗した理由を理解してから使用してください。早い段階でエラーを隠すと、実際のデータの問題を見落とすおそれがあります。

検索が期待どおりに動かないときは、次の簡単なチェックリストを確認してください：

• 検索値は範囲の最初の列にありますか？
• col_index_numは範囲の左端から数えられており、範囲の列数以内になっていますか？
• 完全一致のためにFALSEを追加しましたか？
• 両方の値の型（テキストと数値）は同じで、余分なスペースはありませんか？
• table_arrayはドル記号で固定されていますか？

このリストを上から順に確認すれば、検索の失敗の大部分を数秒で解決できます。

よくある原因と修正方法は次のとおりです：

• 左端列の制限 - 列を並べ替えるか、INDEX-MATCH / XLOOKUPを使用します
• col_index_numの間違いまたは範囲超過 - 範囲の左端から数え、範囲を広げます
• FALSEの省略 - IDには必ず完全一致を設定します
• スペースとテキスト・数値の違い - TRIMで整え、VALUEまたは&""で変換します
• 表が固定されていない - $を使って範囲を固定します

エラーコードを読み、原因と照らし合わせて修正してください。これで、確実な検索を行うためのツールがすべてそろいました。