ExcelのVLOOKUP関数で別シート参照がうまくいかない場合

この記事では、ExcelのVLOOKUP関数で別シート参照がうまくいかない場合の原因と対処法をご紹介しています。

別シートを参照している場合は、結果が取得できない原因に気づきにくいのでひとつずつ丁寧に確認していくことが大切です。

以下では、別シートを参照する際に起こりやすい操作ミスなどをご紹介しているので参考にしてみてください。

以下では、ExcelのVLOOKUP関数で別シート参照がうまくいかない場合の原因を3つに分けてご紹介します。

別シートを参照する際、同じブック内に複数のシートが存在すると、本来参照するシートとは別のシートを参照してしまうことがあります。

上記の表は、VLOOKUP関数で求めたD4セル、E4セル、F4セルの検索結果に#N/Aエラーが表示されています。

#N/Aエラーは検索値が指定した検索範囲内に存在しない場合に表示されるエラーです。

なぜエラーが発生したのか、参照元のシート（例：価格テーブル(2)）を確認してみましょう。

上記の画像の右側にある表は引数に指定した価格テーブル(2)です。表を確認すると、検索値（例：1001）が存在しないので#N/Aエラーが表示されることは正しい検索結果であることが分かります。

上記の画像の左側にある表は同じブック内にある価格テーブル(1)です。表を確認すると、検索値（例：1001）がB4セルに存在しています。

価格テーブル(1)を参照するはずが、誤って価格テーブル(2)を参照していたことが分かりました。第2引数の範囲を「'価格テーブル(1)'!$B:$E,」に修正することで正しい結果が得られます。

同じブック内に複数のシートがある場合、参照するシートを間違えると誤ったデータやエラーが表示されることがあるので、必要なシートだけを同じブックに保存しておくことをおすすめします。

第3引数に指定する列番号がずれると正しい結果が得られない場合があります。

上記の表は、「種類」で果物、「商品名」でりんご、「単価」で98と抽出したいところ、1列ずつずれていてF4セルはエラーになっています。

「受注シート」では「種類」の項目は左から3列目になりますが、VLOOKUP関数で引数に指定する列番号は、抽出したい列が検索範囲内の左から何列目であるかを数えます。

検索範囲に指定している価格テーブル(1)の列を確認してみましょう。

価格テーブル(1)では「受注日」の項目がないため、「種類」で２列目、「商品名」で3列目、「単価」で４列目を引数の列番号に指定する必要があります。

抽出する項目がずれていたり#REFエラーなどが表示されたりする場合は、列番号を確認しましょう。

数式をコピーする際、検索範囲を固定していないと参照がずれてエラーが表示されることがあります。

上記の表は、D4セルの「種類」は正しく抽出できていますが、E4セルの「商品名」とF4セルの「単価」がエラーになっています。

E4セルとF4セルはD4セルの数式をコピーしていますが、D4セルの数式バーを見ると範囲が固定されていないことが分かります。

検索範囲に指定している価格テーブル(1)を確認してみましょう。

上記の表を見ると、B列からE列の間に検索範囲となるデータが入力されているので、検索範囲がずれないように固定する必要があることが分かります。

列番号の前に「$」をつけて「'価格テーブル(1)'!$B:$E,」と入力することで、検索範囲を固定することができます。

数式をコピーする際は適切な位置を固定することが大切なので、検索値や検索範囲などがずれていないかを確認しましょう。

参照範囲を指定する際、列全体ではなくデータが入力されている表だけを選択すると、後でデータを追加する際に検索範囲から漏れてエラーが表示されることがあります。

検索範囲にデータが入力されているセル範囲だけを指定している場合、データを追加したらどのようになるのか試してみます。

検索範囲に指定している価格テーブル(1)のB10セルからE10セルにデータを追加します。

「受注シート」の商品コードに追加した1007を入力するとエラーが表示されました。

引数で指定している検索範囲は価格テーブル(1)のB3セルからE9セルなので、データを追加したB10セルからE10が検索範囲から漏れていることが分かります。

データを追加するたびに参照範囲を修正するのは手間なので、セル範囲でなく「'価格テーブル(1)'!$B:$E,」のように列全体を指定することをおすすめします。

#N/Aエラーになる場合

別シートを参照している場合に限りませんが、VLOOKUP関数を使うと#N/Aエラーが表示されることがよくあります。

データが存在しない場合は正しい結果が得られていますが、検索範囲が漏れていたり検索値を探す列の位置を間違えたりしても表示されてしまうので注意が必要です。

エラーを解消する方法の詳細は以下の記事でご紹介しています。

別シートを参照している場合に限りませんが、VLOOKUP関数の検索方法で「完全一致」を指定している場合は、文字列の表記や表示形式の違いにより完全一致していないと判定されてしまうことがあります。

数式は合っているのに、#N/Aエラーが表示されてしまう場合はセル内に入力した文字列や数値を確認してみましょう。

詳細は以下の記事でご紹介しています。