はじめに

Excel や google スプレッドシートの VLOOKUP 関数で数値列を参照する際、検索キーは存在するのに値が空である場合に既定値を返す方法です。検索キーが存在しない場合 とか IF に VLOOKUP を 2 回書く方法 は検索で引っかかりますが、以下の方法は見つからなかったので備忘を兼ねて書いておきます。

やりかた

適当にこんな表で "b" を検索キーとして値を取得する場合を仮定します。

	A	B
1	key	value
2	a	11
3	b	(空白)

数値として取得する場合
=IFERROR(VALUE(VLOOKUP("b",A:B,2,FALSE)&" "), NA())
文字列として取得する場合
=IFERROR(VALUE(VLOOKUP("b",A:B,2,FALSE)&" ")&"", NA())

になります。ここでは NA() を既定値にしていますが好きな値を IFERROR の第 2 引数に指定してください。

VALUE(" ") は #VALUE! エラーを返して¹ VALUE("1 ") は数値を返すことを利用します。
VLOOKUP 結果末尾に半角スペースを連結して VALUE に渡すと結果が空だった場合のみ #VALUE! エラーになるため²、それを IFERROR で既定値に差し替えています。
VALUE は数値を返すため文字列として取得したい場合は "" を連結して文字列化します。

お呼びでない何か

=IFERROR(VLOOKUP("c",A:B,2,FALSE), NA())
検索キーが存在しない場合用ですが、検索キーが存在する前提の今回のケースでは使えません。
ついでに言えば今回紹介した数式は検索キーが存在しない場合もカバーします。
IF(VLOOKUP("b",A:B,2,FALSE)<>"", VLOOKUP("b",A:B,2,FALSE), NA())
動きますが VLOOKUP を 2 回書かなければならず、数式が長いし、記述や修正が面倒だし、処理も遅そうだしでイマイチです。

おわりに

元は python の pandas に読ませるデータを excel で作る時に、数値列なので空じゃなくて #N/A にしたかったので考えた方法です。どなたかのお役に立てば幸いです。

VALUE("") だと Excel ではエラーになるが、スプレッドシートでは 0 が返りエラーにならない。 ↩
文字列 (空白スペース) を連結せずに (空セルを) VALUE に渡すと 0 が返りエラーにならない。 ↩