はじめに
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! エラーを返して1 VALUE("1 ") は数値を返すことを利用します。
VLOOKUP 結果末尾に半角スペースを連結して VALUE に渡すと結果が空だった場合のみ #VALUE! エラーになるため2、それを 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 にしたかったので考えた方法です。どなたかのお役に立てば幸いです。