shikumika’s diary

日々の事務作業で手間なことを簡単にできる仕組み(自動化、方法など)、困ったことの解決方法を調べた備忘録

【Excel】VLOOKUP関数で#REF!エラーの原因と対応

ExcelのVLOOKUP関数で#REF!エラーが表示されることがあります。

この原因は、

VLOOKUP 関数の構文
=VLOOKUP(検索値, 範囲, 列番号, [検索の型])

で、数式の作成時に「範囲」を超えて列番号を指定したことによるエラーが多いです。

なお、数式の作成後、検査値に指定していたセルを削除した場合も、次のように数式の検査値箇所が#REF!となり、エラーになります。

例) =VLOOKUP(#REF!,$B$3:$C$8,2,FALSE) と数式内に#REF!がある

ただし、このケースは数式作成後に削除しており、原因が比較的わかりやすいと思います。

今回は数式の作成時に発生しやすい、「範囲」を超えて列番号を指定した#REF!エラーの事例と対策としてXLOOKUPの活用です。

内容:

VLOOKUP関数で#REF!エラーの発生例

下図のセルF4と、F5にVLOOKUP関数を用いた数式が入力されています。

数式内容の違いは「列番号」が“ 2 ”と“ 3 ”の違いです。

その結果、セルF5は#REF!エラーが発生しています。

数式内容は、「列番号」が“2”と“3”の違いで範囲外の”3”は#REF!エラーの発生

VLOOKUP関数で#REF!エラーの発生例

上記は、セル範囲$B$3:$C$8(マスタ表に該当)を「範囲」として指定しています。

しかし、この範囲には2列しかなく、列番号3が存在しません。

#REF!エラーの原因は、存在しない列番号の指定であるため、存在する列番号に修正します。

このように、#REF!エラーは数式が無効なセルを参照している場合に表示されます。

VLOOKUP関数で#REF!エラーとなる作業

#REF!エラーの原因は、存在しない列番号の指定ですが、「範囲」のセルを削除し、参照範囲がずれたときにも発生します。

例えば、次のような事例です。

「範囲」のセルを削除したことで、参照範囲が自動修正($B$3:$D8 ⇒ $B$3:$C$8)されるが、 「列番号」は“ 3 ”のままで#REF!エラーが発生(下図)。

なお、#REF!エラーとならない場合も、数式が取得する値は異なります。

同じ結果を取得するためには、「列番号」の修正が必要です

C列を削除。数式内の「列番号」は“3”のままで#REF!エラーの発生

「範囲」のセルを削除し、参照範囲が変更となる作業例

「範囲」のセルを削除してもエラーへの影響が少ないXLOOKUP関数

VLOOKUP関数の改良版であるXLOOKUP関数は、取得する値を「列番号」ではなく「セル参照」で指定する関数のため、セルを削除してもエラーへの影響が少ない関数です。

ただし、Excel 2016 および Excel 2019 では使用できず、新しいバージョンの Excelから使用可能です。

XLOOKUP関数は、次の構文です。

=XLOOKUP(検索値, 検索範囲, 戻り配列, [見つからない場合], [一致モード], [検索モード])

XLOOKUP 関数 - Microsoft サポート

VLOOKUP関数と同じ結果を得るXLOOKUP関数の作成事例

以下は、前述の事例について、VLOOKUP関数と同じ結果を得るXLOOKUP関数の作成事例です。

  • VLOOKUP関数の「範囲」左端列に相当する検索範囲$B$3:$B$8
  • VLOOKUP関数の「列番号」に相当する戻り配列を$D$3:$D$8

で指定し、VLOOKUP関数と同様の結果を得ています。

セルG9の数式内容:

=XLOOKUP(F9,$B$3:$B$8,$D$3:$D$8,"",0)

=XLOOKUP(F9,$B$3:$B$8,$D$3:$D$8,"",0) と数式に入力

VLOOKUP関数と同じ結果を得るXLOOKUP関数の作成事例

VLOOKUP関数とXLOOKUP関数でセル削除による数式変化

数式で参照していた表の列を削除した場合の事例が下図です。

C列を削除した場合、VLOOKUP関数はXLOOKUP関数の戻り配列は、$D$3:$D$8 ⇒  $C$3:$C$8 に変化し、計算結果への影響はありません。

XLOOKUP関数の戻り配列は、$D$3:$D$8⇒ C$3:$C$8に変化

参照する表でセル削除した場合のVLOOKUP関数とXLOOKUP関数の数式変化

以上、ExcelのVLOOKUP関数で#REF!エラーの原因と対応でした。

(初心者向け)VLOOKUP関数の基本的な使い方とミス防止のポイント

ミス防止のポイントを記載しています。

shikumika.org