ExcelのVLOOKUP関数で値があるのに0になる場合

この記事では、ExcelのVLOOKUP関数で値があるのに0になる場合の原因と対処法をご紹介しています。

値があるのに0になる場合は、選択したセル参照を間違えている場合や、表示形式または重複したセルの影響で0が表示されている可能性もあります。

以下では、値があるのに0が表示されてしまう原因と対処法を詳しくご紹介しています。

VLOOKUP関数を使う際、操作ミスなどで値があるのに0が返されることがあります。

上記の画像は、「検索値」1002に紐づく価格が空白になっています。

「検索値」があるのに「紐づく値」が空白の場合は0が表示されますが、これはVLOOKUP関数の標準機能なので、正しい結果が表示されています。

ただし、検索値に紐づく値があるのに0になる場合は、セル参照を間違えていたり表示形式または重複した値が影響している可能性があります。

以下では、原因を分けて詳しく解説していきます。

数式を入力してEnterを押した後、上記の画像のような「Microsoft Excel」ダイアログボックスが表示される場合は循環参照が発生しています。

循環参照とは、主に数式を入力しているセルを直接または間接的に参照することで、計算がループして収束できなくなっている状態です。

上記の画像は循環参照の例です。

C10セルにVLOOKUP関数を入力していますが、引数の検索値でもC10セルを参照しているので循環参照が発生しています。

循環参照の場合は値があっても0が表示されることがあるので、参照する検索値を別のセルに入力することで正しい結果が取得できます。

数式を入力したセルと検索値を入力したセルを別にすることで、C10セルに正しい結果を表示することができました。

上記の画像は、「商品コード」1002に紐づく4列目の「価格」を抽出するために、C10セルの数式をD10セルにコピーして列番号を手動で入れ替えた結果、範囲がずれて0が返されました。

VLOOKUP関数の列番号は数式をコピーしても自動で可変しませんが、検索値と範囲は固定しないと参照がずれてしまいます。

検索値や範囲がずれてしまうと誤った結果が表示される原因になるので、必ず固定しましょう。

C10セルに「=VLOOKUP($B10,$B$4:$E$6,4,FALSE)」を入力することで、適切な位置が固定されて、D10セルに正しい値が表示されました。

検索値は列番号の前に$を付けてB列を固定し、範囲は行番号と列番号の前に$を付けてセルを固定することで、縦横どちらにコピーしても参照がずれることはありません。

上記の画像は、検索値に紐づく値があるのにVLOOKUP関数の結果で0が返されています。

抽出する値が小数点以下の場合は、表示形式に注意が必要です。

①数式を入力したセル、②ホームタブの順に選択して、③小数点以下の表示を増やすボタンを2回押します。

小数点以下の表示を増やすことで、C10セルに正しい結果を表示することができました。

上記の画像は、検索値に紐づく値があるのにVLOOKUP関数の結果で0が返されています。

数式に問題がない場合は、データが重複していて別の結果が表示されている可能性があります。

VLOOKUP関数では、重複する検索値があった場合に1番上の値だけを返すので、データが重複している場合は正しい検索ができません。

以下で、データの重複があるかを確認していきます。

①A列の列番号を選択して、右クリックします。②右クリックメニューから挿入を選択します。

A4セルに「=COUNTIFS($B$4:B4,$B$17)」を入力してEnterを押します。

上記の数式は、B17セルの値に一致するセルがB4セルからB4セルの間にいくつあるのかを数えます。

COUNTIFS関数は指定した数値や文字列と一致するセル数が範囲内にいくつあるのかを数えます。書式は「=COUNTIF(範囲,検索条件)」です。

COUNTIFS関数の詳しい使い方は、以下の記事でご紹介しています。

A4セルに0が表示されました。

A4セルのフィルハンドルをドラッグして数式をコピーします。

A11セル以降に2が表示されたのでデータが2つあることが分かりました。

B7セルに検索値と一致する1008があり、紐づく「価格」が空白になっているので0が返されています。

検索値が重複している場合は正しい検索ができないので、重複しない値に変更しましょう。

「価格テーブル」の商品コードの値を変更することで、C17セルに正しい結果が抽出することができました。