Excel VLOOKUP関数で日付が正常に表示されない場合の対処方法

2020年6月8日

VLOOKUP関数の結果として日付を表示する場合、

下のキャプチャのように日付ではなく数字が表示される場合があります。

VLOOKUPの結果で日付を表示した場合、シリアル値がそのまま表示される状態のキャプチャ

この記事ではその対処方法を紹介します。

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

原因と対処方法

まずこの事象はバグなど不具合ではなく、シリアル値がそのまま表示されているだけであり

( シリアル値…1900年1月1日から始まる1日毎の連番 )

日付のデータを正常に取得していることに替わりがなく見た目がシリアル値なだけの状態です。

表示形式が原因の場合

VLOOKUP関数の結果を文字列に変化や文字列結合をしていない場合、

表示形式を変更する必要があります。

まず右クリックで「セルの書式設定」を選択します。

セルを右クリックし、セルの書式設定を選択するキャプチャ

表示形式の分類が日付以外になっているので、ここを「日付」に変更します。

セルの書式設定で日付に設定変更するキャプチャ

すると日付として表示されるようになります。

もちろんユーザ定義書式で任意の形式を設定しても構いません。

セルの書式設定が完了し、日付表示が正常になった状態のキャプチャ

検索結果が見つからないか、空の場合に対処する方法

検査値が見つからない場合、#N/Aエラーとなります。

検索値が存在せずVLOOKUP関数の結果が#N/Aエラーとなる状態のキャプチャ

これに対処する場合、IFERROR関数を使用します。

=IFERROR(VLOOKUP(…),"")
IFERROR関数によりエラーに対処した状態のキャプチャ

しかしこの数式では検索値が存在して表示項目が空白の場合、不正な表示になります。

検索値が存在して表示項目が空白の場合に不正な表示となる状態のキャプチャ

これは検索結果の空白が0として扱われ

シリアル値の0である1900年1月0日という

あり得ない日付として表示されるためです。

この場合は更にIF関数も使用する必要があります。

=IF(IFERROR(VLOOKUP(…),"")="","",VLOOKUP(…))
IF関数を使用して検索結果が空の場合に対処

同じVLOOKUP関数を二つ書いてしまい冗長でない数式となってしまいますので

VLOOKUP関数部分を別セルに切り出すと冗長性と長さが解消されおススメです。

セル数式
中間セル=IFERROR(VLOOKUP(…),0)
結果セル=IF(中間セル=0,"",中間セル)

中間セルの列は非表示にしても問題ありません。

IFERRORとVLOOKUPを別セルに切り出して数式の質を高めた例

空白文字を結合している場合

下の記事でも紹介している方法ですが、検索結果が0の場合の対処法で

空白の文字列を結合するものがあります。

=VLOOKUP(…)&""

これを行っている場合、セルが日付ではなく文字列として扱われてしまうため

先述の表示形式変更を行っても表示が改善されません。

そのため空白の結合を削除して表示形式変更を実施します。

もし文字列として扱いたい場合はTEXT関数を利用します。

セル数式
中間セル=IFERROR(VLOOKUP(…)&"","")
結果セル=TEXT(IF(中間セル="","",中間セル),"yyyy/mm/dd")

検索結果が見つからない場合、空の場合にも対処してある数式です。

これにより文字列の状態のまま見た目を正常な日付にすることが可能です。

TEXT関数も使用して結果を文字列にしながら日付の表記にした状態のキャプチャ

関連記事

XLOOKUP(VLOOKUP)関数の使い方・活用方法の記事一覧

IF系関数の使い方・活用方法の記事一覧

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