Excel VLOOKUP関数で結果が0になる場合の対処方法

2020年10月14日

VLOOKUP関数の検索結果が空白の場合、空白ではなく0が表示されてしまいます。

VLOOKUP関数の検索結果(空白)が0になる状態の図解
VLOOKUP関数の検索結果(空白)が0になる例

このような場合に、空白または0以外を表示する方法を紹介します。

XLOOKUP関数でも同様の方法で対応可能です。

対処方法

対処方法は二つあります。

  1. 文字列結合
  2. IF関数

比較(メリット・デメリット)

基本的には文字列結合を使えば良いのですが、

少し問題もあるので両方の方法のメリット・デメリットを比較します。

方法メリットデメリット
文字列結合・記述がシンプル・数値を取得する場合、データの種類が問題になる場合あり
・裏技に近く、この方法を知らない人が見ると何を意図した文字列結合か分かりづらい。
IF関数・作成者以外が見ても数式の意図が掴みやすい
・データの種類が問題にならない
・任意の表示が可能
・同じVLOOKUP関数を2重に記述する必要があり数式が複雑になる

文字列結合での対応手順

VLOOKUP関数に空文字を文字列結合するだけのシンプルな方法です。

=VLOOKUP関数&""
VLOOKUP関数に空文字を文字列結合する場合の図解
VLOOKUP関数に空文字を文字列結合

検索結果が実際に0の場合は表示も0になります。

ただしデータ型が文字列になっているので左寄せの表示になります。

検索結果が実際に0の例(文字列扱いのため左寄せになっている)
検索結果が実際に0の例(文字列扱いのため左寄せになっている)
ISNUMBER関数でFALSE。文字列として認識されている
ISNUMBER関数でFALSE。文字列として認識されている

これが日付の場合、特に問題がありシリアル値から日付表示に出来なくなってしまいます。

その場合、次に記載する方法を使用すると問題なく処理できます。

IF関数での対応手順

データの型を変えたくない場合や「-」や「※空白」など

空白以外を表示したい場合はIF関数を使用します。

=IF(VLOOKUP関数="","※空白の時に表示したい文字列",VLOOKUP関数)

2つのVLOOKUP関数は全く同じ指定をします。

空白の場合、指定の文字を表示
空白の場合、指定の文字を表示
空白以外の場合、検索結果を表示
空白以外の場合、検索結果を表示

この方法であれば結果が数値の場合、そのまま数値として扱われます。

数値は数値として扱われる
数値は数値として扱われる

先述の文字列結合の方法で不都合がある場合、

※空白の時に表示したい文字列"を空白にすれば解決します。

空白を空白で表示
空白を空白で表示

数式の効率化(LET関数が使える場合)

2020年10月頃にMicrosoft(Office)365で追加された新機能のLET関数を利用すれば

VLOOKUP関数の重複なしに数式を組むことが可能です。

(いつ更新が反映されるかは時間差があります)

LET関数は一定の数式に任意の名前を付けて再利用可能にする関数です。

(マクロやプログラミングの変数のようなもの)

数式は以下のようになります。

=LET(任意の名前,VLOOKUP関数,IF(任意の名前="","※空白の時に表示したい文字列",任意の名前))

今回の例では以下のようになります。VLOOKUP関数をvlという名前に置き換えます。

=LET(vl,VLOOKUP(C2,B6:C8,2,FALSE),IF(vl="","",vl))

LET関数を使わない場合は以下のような数式です。

=IF(VLOOKUP(C2,B6:C8,2,FALSE)="","",VLOOKUP(C2,B6:C8,2,FALSE))

これにより以下のようなメリットがあります。

  • 数式の重複(冗長)がなくなり、数式の変更が簡単になる
  • 意味のある名前を付けられるため数式の意図を把握しやすくなる(ただしLET関数を知っている必要あり)
  • 数式の実行回数が減るのでExcelの計算が早くなる(今回の例ではVLOOKUP関数が2回→1回)

VLOOKUP関数XLOOKUP関数は計算が重い部類の関数なので

数式を使うセルが多い場合は特に効果が大きいでしょう。

LET関数の利用例

関連記事