ExcelのVLOOKUP関数で#REF!エラーが表示されることがあります。
この原因は、
VLOOKUP 関数の構文
=VLOOKUP(検索値, 範囲, 列番号, [検索の型])
で、数式の作成時に「範囲」を超えて列番号を指定したことによるエラーが多いです。
なお、数式の作成後、検査値に指定していたセルを削除した場合も、次のように数式の検査値箇所が#REF!となり、エラーになります。
例） =VLOOKUP(#REF!,$B$3:$C$8,2,FALSE) と数式内に#REF!がある
ただし、このケースは数式作成後に削除しており、原因が比較的わかりやすいと思います。
今回は数式の作成時に発生しやすい、「範囲」を超えて列番号を指定した#REF!エラーの事例と対策としてXLOOKUPの活用です。
内容：
- VLOOKUP関数で#REF!エラーの発生例
- VLOOKUP関数で#REF!エラーとなる作業
- 「範囲」のセルを削除してもエラーへの影響が少ないXLOOKUP関数
- （初心者向け）VLOOKUP関数の基本的な使い方とミス防止のポイント
VLOOKUP関数で#REF!エラーの発生例
下図のセルF4と、F5にVLOOKUP関数を用いた数式が入力されています。
数式内容の違いは「列番号」が“ 2 ”と“ 3 ”の違いです。
その結果、セルF5は#REF!エラーが発生しています。
上記は、セル範囲$B$3:$C$8（マスタ表に該当）を「範囲」として指定しています。
しかし、この範囲には2列しかなく、列番号3が存在しません。
#REF!エラーの原因は、存在しない列番号の指定であるため、存在する列番号に修正します。
このように、#REF!エラーは数式が無効なセルを参照している場合に表示されます。
VLOOKUP関数で#REF!エラーとなる作業
#REF!エラーの原因は、存在しない列番号の指定ですが、「範囲」のセルを削除し、参照範囲がずれたときにも発生します。
例えば、次のような事例です。
「範囲」のセルを削除したことで、参照範囲が自動修正（$B$3:$D8 ⇒ $B$3:$C$8）されるが、 「列番号」は“ 3 ”のままで#REF!エラーが発生（下図）。
なお、#REF!エラーとならない場合も、数式が取得する値は異なります。
同じ結果を取得するためには、「列番号」の修正が必要です。
「範囲」のセルを削除してもエラーへの影響が少ないXLOOKUP関数
VLOOKUP関数の改良版であるXLOOKUP関数は、取得する値を「列番号」ではなく「セル参照」で指定する関数のため、セルを削除してもエラーへの影響が少ない関数です。
ただし、Excel 2016 および Excel 2019 では使用できず、新しいバージョンの Excelから使用可能です。
XLOOKUP関数は、次の構文です。
=XLOOKUP(検索値, 検索範囲, 戻り配列, [見つからない場合], [一致モード], [検索モード])
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)
VLOOKUP関数とXLOOKUP関数でセル削除による数式変化
数式で参照していた表の列を削除した場合の事例が下図です。
C列を削除した場合、VLOOKUP関数はXLOOKUP関数の戻り配列は、$D$3:$D$8 ⇒ $C$3:$C$8 に変化し、計算結果への影響はありません。
以上、ExcelのVLOOKUP関数で#REF!エラーの原因と対応でした。
（初心者向け）VLOOKUP関数の基本的な使い方とミス防止のポイント
ミス防止のポイントを記載しています。
