VLOOKUPのNAと空白と日付不一致を切り分ける

LWP | VLOOKUPのNAと空白と日付不一致を切り分ける

LWP TECHNICAL ARTICLE | 195

VLOOKUPのNAと空白と日付不一致を切り分ける

見つからない、0になる、同じ日付なのに一致しない原因を順番に診断する

本資料は、出典を明記いただければ、商用・非商用を問わず、ご自由に複製・改変・再配布していただけます。なお、著作権表示は改変せず、そのまま記載してご利用くださいますようお願いいたします。

記事要約

VLOOKUPで起きる「#N/A」「空白セルが0になる」「見た目が同じ日付なのに一致しない」は、同じ問題ではありません。#N/Aは検索が成立していない状態、0は検索後の返却値の扱い、日付不一致は検索値と検索列の内部値・型の違いが主な論点です。

本記事では、最初からIFERRORで隠すのではなく、検索範囲、完全一致、型、余分な文字、時刻、小数部、返却先の空白を順番に確認します。原因を見えるまま切り分けた後で、IFNAやLETを使って利用者向けの表示を整える方法を説明します。

本記事の対象とゴール

想定読者

VLOOKUPの#N/Aを原因不明のままIFERRORで消している方
マスターの空白セルが0と表示されて困っている方
日付が同じに見えるのに完全一致検索が失敗する方

本記事で得られること

検索失敗と返却値表示の問題を分けて診断できます。
日付の表示、シリアル値、時刻、文字列の違いを確認できます。
エラーを隠しすぎず、利用者に分かりやすい完成式を作れます。

はじめに　三つの現象を混ぜない
第1章　VLOOKUPの成立条件を確認する
第2章　#N/Aは検索側から診断する
第3章　空白が0になるのは返却側の問題である
第4章　日付不一致は表示ではなく内部値を見る
第5章　IFNAとIFERRORを使い分ける
第6章　実務で壊れにくい完成式を作る
第7章　診断チェックリスト
まとめ　表示を整える前に原因を分ける

はじめに　三つの現象を混ぜない

0.1　症状ごとに確認場所が違う

VLOOKUPのトラブルは、次の三段階に分けると整理できます。

段階	主な症状	主な確認対象
検索	#N/A	検索値、検索列、完全一致、型、余分な文字
返却	0、意図しない空文字	戻り列の空白、数式の空文字、表示形式
表示・内部値	日付が同じに見えるのに不一致	シリアル値、時刻、小数部、文字列日付

最初からすべてをIFERRORで包むと、どの段階で問題が起きたか見えなくなります。

0.2　共通例

検索値をA2、マスターをF2:H100、戻り値をH列とします。

=VLOOKUP(A2,$F$2:$H$100,3,FALSE)

この最小式の結果を確認してから、空白処理やメッセージを追加します。

VLOOKUPの成立条件を確認する

1.1　四つの引数

VLOOKUPの構文は次のとおりです。

=VLOOKUP(検索値,範囲,列番号,[検索方法])

Microsoftの公式説明では、検索値は指定範囲の先頭列に存在する必要があり、列番号はその範囲の左端を1として数えます。

1.2　業務のコード検索ではFALSEを明示する

完全一致検索では、第4引数にFALSEを指定します。

=VLOOKUP(A2,$F$2:$H$100,3,FALSE)

第4引数を省略すると近似一致が既定となります。近似一致は検索列の並び順も前提にするため、商品コード、社員番号、伝票番号などの検索では、意図しない値を返す危険があります。

1.3　列番号はシート列ではなく範囲内の番号

範囲がF:Hなら、F列が1、G列が2、H列が3です。

F列  G列  H列

 1    2    3

H列だから8ではありません。範囲の左端から数えます。

1.4　検索列は範囲の左端に置く

VLOOKUPは指定範囲の先頭列を検索します。検索キーがG列、戻り値がF列にある場合、F:Gをそのまま指定して左方向へ返すことはできません。

表の設計を変えられない場合は、XLOOKUPやINDEXとMATCHの利用を検討します。

第2章　#N/Aは検索側から診断する

2.1　#N/Aは「該当なし」を表す

完全一致のVLOOKUPで#N/Aが返る基本的な意味は、検索値と一致する値を検索列で見つけられなかったことです。

原因は、値が本当に存在しない場合だけではありません。

数値の100と文字列の"100"が混在している
前後に空白がある
改行や制御文字が含まれている
全角・半角などの表記が違う
日付に時刻が含まれている
検索列が範囲の左端ではない

2.2　まず存在件数を数える

検索列がF列なら、COUNTIFで完全一致候補の件数を確認します。

=COUNTIF($F$2:$F$100,A2)

0なら一致候補がありません。1なら一意、2以上なら重複があります。VLOOKUPは最初に見つかった値を返すため、重複は別の品質問題になります。

2.3　型を確認する

TYPE関数で数値か文字列かを確認できます。

=TYPE(A2)

主な結果は、数値が1、文字列が2です。

見た目が100でも、片方が数値、もう片方が文字列なら、完全一致検索で一致しないことがあります。セルの配置や表示形式だけで型を推測せず、実際の値を確認します。

2.4　余分な文字を確認する

文字数を比較します。

=LEN(A2)

前後の通常スペースはTRIM、印刷されない一部の制御文字はCLEANで除去できます。

=TRIM(CLEAN(A2))

ただし、すべての種類の空白やUnicode文字をTRIMとCLEANだけで除去できるわけではありません。データの発生元と実際の文字コードを確認します。

第3章　空白が0になるのは返却側の問題である

3.1　検索は成功している

マスターで該当行が見つかり、戻り先セルが空白のとき、VLOOKUPの結果が0に見えることがあります。

これは#N/Aとは異なります。

#N/A ：検索行が見つからない

0    ：検索行は見つかったが、戻り値が空白等として扱われた

3.2　空白、空文字、0は別の値

Excelでは、次の状態を区別する必要があります。

状態	例	意味
真の空白	何も入力されていない	値が存在しない
空文字	数式が""を返す	長さ0の文字列
数値0	0	数値として存在する

見た目が同じでも、ISBLANKなどの結果は異なります。

3.3　検索結果を一度だけ計算する

LETが利用できるExcelなら、検索結果を変数に入れて空白判定できます。

=IFNA(

    LET(

        x,VLOOKUP(A2,$F$2:$H$100,3,FALSE),

        IF(x="","",x)

),

    "未登録"

この式は、検索できた場合は戻り値をxへ入れ、空白相当なら空文字を表示します。検索結果が#N/Aなら、外側のIFNAが「未登録」を返します。

IFNAをLETの外側へ置く点が重要です。VLOOKUPが失敗した時点のエラーも確実に受け止められます。

3.4　文字列連結による空白処理の注意

次の書き方は空白を空文字に見せられますが、数値や日付も文字列化するため、戻り値が文字列の場合に限ります。

=VLOOKUP(A2,$F$2:$H$100,3,FALSE)&""

第4章　日付不一致は表示ではなく内部値を見る

4.1　Excelの日付は連続した数値である

通常の1900日付システムでは、Excelの日付は日数を表すシリアル値として保持されます。たとえば2023年10月27日は45226です。

表示形式をyyyy/m/dにしても、内部値は数値のままです。

4.2　時刻は小数部に入る

2023年10月27日0時は45226ですが、正午は45226.5です。どちらも表示形式によっては2023/10/27と見えます。

完全一致では、45226と45226.5は別の値です。

=MOD(A2,1)

結果が0なら時刻部分はありません。0以外なら時刻を含みます。

4.3　時刻を無視して日付だけを照合する

時刻を無視する業務ルールなら、検索値と検索列の両方で整数部を比較します。

マスター側に補助列を作ります。

=INT(F2)

検索側も日付部分へそろえます。

=VLOOKUP(INT(A2),$J$2:$L$100,3,FALSE)

J列にはマスター日付のINT結果を置きます。片側だけをINTにしても、比較対象がそろわない場合があります。

4.4　文字列の日付は変換が必要

"2023/10/27"という文字列と、日付シリアル45226は見た目が同じでも別の型です。

=TYPE(A2)

文字列日付をDATEVALUEで変換できる場合があります。

=DATEVALUE(A2)

ただし、DATEVALUEの解釈は地域設定や文字列形式の影響を受けます。CSVや外部システムから取り込む場合は、年・月・日を別々に取得してDATE関数で組み立てるなど、入力形式を明示する方が安全です。

4.5　表示形式は値を変えない

セルの表示形式を日付に変えても、文字列が必ず日付シリアルへ変換されるわけではありません。逆に、日付シリアルへ標準表示を適用すれば数値に見えます。

表示の見た目と、内部値の型・値は分けて確認します。

第5章　IFNAとIFERRORを使い分ける

5.1　IFNAは#N/Aだけを処理する

検索結果が見つからない場合だけ表示を変えるなら、IFNAが適しています。

=IFNA(VLOOKUP(A2,$F$2:$H$100,3,FALSE),"未登録")

列番号の誤りによる#REF!など、別の設計ミスはそのまま見えます。

5.2　IFERRORはすべてのエラーを処理する

=IFERROR(VLOOKUP(A2,$F$2:$H$100,3,FALSE),"確認要")

IFERRORは#N/A、#REF!、#VALUE!などをまとめて処理します。利用者向け画面では便利ですが、式の欠陥やデータ異常まで同じ表示にすると原因を追えません。

5.3　診断中はエラーを隠さない

安全な順序は次のとおりです。

VLOOKUP単体で結果を確認する。
#N/Aなら検索値、検索列、型、文字、時刻を確認する。
正しい検索結果が得られてから空白表示を整える。
最後にIFNAまたはIFERRORを付ける。

エラー処理は原因究明の代わりではなく、原因を解決した後の表示設計です。

第6章　実務で壊れにくい完成式を作る

6.1　検索失敗と空白返却を分ける

次の式は、検索失敗を「未登録」、検索成功後の空白を空欄、それ以外を実際の値として返します。

=IFNA(

    LET(

        result,VLOOKUP(A2,$F$2:$H$100,3,FALSE),

        IF(result="","",result)

),

    "未登録"

同じVLOOKUPを二度実行しないため、式の意味が明確になります。

6.2　診断用の列を残す

複雑なデータでは、ひとつの巨大な式ですべてを変換せず、補助列を使います。

補助列	式の例	確認目的
型	=TYPE(A2)	数値か文字列か
文字数	=LEN(A2)	余分な文字がないか
日付整数部	=INT(A2)	時刻を除いた日付
時刻部分	=MOD(A2,1)	小数部の有無
存在件数	=COUNTIF($F$2:$F$100,A2)	未登録・重複の確認

補助列は冗長に見えても、データ品質を目で追える利点があります。

6.3　XLOOKUPへ置き換えても型問題は残る

新しいExcelではXLOOKUPを使うと、左方向検索や未検出時の値を指定しやすくなります。

=XLOOKUP(A2,$F$2:$F$100,$H$2:$H$100,"未登録",0)

ただし、数値と文字列、日付と時刻、余分な空白が一致しない問題は、検索関数を変えただけでは解決しません。検索前に値を正規化する考え方は同じです。

第7章　診断チェックリスト

7.1　#N/Aの場合

第4引数はFALSEか
検索列は指定範囲の左端か
COUNTIFの結果は0か、1か、2以上か
検索値と検索列のTYPEは一致しているか
LENに不自然な差がないか
前後空白、改行、制御文字、全角・半角の差がないか
日付に時刻が含まれていないか

7.2　0が表示される場合

検索自体は成功しているか
マスターの戻り先は真の空白か、空文字か、数値0か
0を空欄表示してよい業務項目か
&""で数値を文字列化していないか

7.3　日付が一致しない場合

両方とも数値型の日付か
一方が文字列日付ではないか
MODで時刻部分がないか
1900日付システムと1904日付システムの違いが関係していないか
片側だけでなく両側を同じ規則で正規化したか

まとめ　表示を整える前に原因を分ける

#N/Aは検索が成立していない状態です。
空白が0に見える問題は、検索成功後の返却値の扱いです。
日付不一致では、見た目でなくシリアル値、型、時刻部分を確認します。
完全一致の業務検索ではFALSEを明示します。
IFNAは#N/Aだけ、IFERRORはすべてのエラーを処理します。
診断中はエラーを隠さず、原因を解決してから表示を整えます。
LETや補助列を使い、同じ検索や変換を重複させない構造にします。

VLOOKUPの問題を「関数が壊れた」とひとまとめにせず、検索、返却、内部値の三段階に分ければ、修正箇所を短時間で特定できます。

参考資料

Microsoft Support: VLOOKUP function: https://support.microsoft.com/en-us/excel/functions/vlookup-function
Microsoft Support: IFNA function: https://support.microsoft.com/en-us/office/ifna-function-6626c961-a569-42fc-a49d-79b4951fd461
Microsoft Support: IFERROR function: https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611
Microsoft Support: Date systems in Excel: https://support.microsoft.com/en-us/office/date-systems-in-excel-e7fe7167-48a9-4b96-bb53-5612a800b487

出典メモ

主素材: C:\Users\hoehoe\Downloads\記事ネタmd\VLOOKUP関数の注意点_記事元ネタ.md
素材として使用した範囲: #N/A、空白が0になる現象、日付シリアルと時刻、IFNAとIFERROR、診断用関数。
公式確認: Microsoft SupportでVLOOKUPの構文、先頭列検索、列番号、完全一致と近似一致の指定を確認した（2026-08-23閲覧）。
補正: 元素材のLET例は、VLOOKUPの#N/Aを確実に受け止めるため、IFNAをLETの外側に置く構造へ修正した。

VLOOKUPのNAと空白と日付不一致を切り分ける

VLOOKUPのNAと空白と日付不一致を切り分ける

記事要約

本記事の対象とゴール

想定読者

本記事で得られること

目次

はじめに 三つの現象を混ぜない

0.1 症状ごとに確認場所が違う

0.2 共通例

VLOOKUPの成立条件を確認する

1.1 四つの引数

1.2 業務のコード検索ではFALSEを明示する

1.3 列番号はシート列ではなく範囲内の番号

1.4 検索列は範囲の左端に置く

第2章 #N/Aは検索側から診断する

2.1 #N/Aは「該当なし」を表す

2.2 まず存在件数を数える

2.3 型を確認する

2.4 余分な文字を確認する

第3章 空白が0になるのは返却側の問題である

3.1 検索は成功している

3.2 空白、空文字、0は別の値

3.3 検索結果を一度だけ計算する

3.4 文字列連結による空白処理の注意

第4章 日付不一致は表示ではなく内部値を見る

4.1 Excelの日付は連続した数値である

4.2 時刻は小数部に入る

4.3 時刻を無視して日付だけを照合する

4.4 文字列の日付は変換が必要

4.5 表示形式は値を変えない

第5章 IFNAとIFERRORを使い分ける

5.1 IFNAは#N/Aだけを処理する

5.2 IFERRORはすべてのエラーを処理する

5.3 診断中はエラーを隠さない

第6章 実務で壊れにくい完成式を作る

6.1 検索失敗と空白返却を分ける

6.2 診断用の列を残す

6.3 XLOOKUPへ置き換えても型問題は残る

第7章 診断チェックリスト

7.1 #N/Aの場合

7.2 0が表示される場合

7.3 日付が一致しない場合

まとめ 表示を整える前に原因を分ける

参考資料

出典メモ

はじめに　三つの現象を混ぜない

0.1　症状ごとに確認場所が違う

0.2　共通例

1.1　四つの引数

1.2　業務のコード検索ではFALSEを明示する

1.3　列番号はシート列ではなく範囲内の番号

1.4　検索列は範囲の左端に置く

第2章　#N/Aは検索側から診断する

2.1　#N/Aは「該当なし」を表す

2.2　まず存在件数を数える

2.3　型を確認する

2.4　余分な文字を確認する

第3章　空白が0になるのは返却側の問題である

3.1　検索は成功している

3.2　空白、空文字、0は別の値

3.3　検索結果を一度だけ計算する

3.4　文字列連結による空白処理の注意

第4章　日付不一致は表示ではなく内部値を見る

4.1　Excelの日付は連続した数値である

4.2　時刻は小数部に入る

4.3　時刻を無視して日付だけを照合する

4.4　文字列の日付は変換が必要

4.5　表示形式は値を変えない

第5章　IFNAとIFERRORを使い分ける

5.1　IFNAは#N/Aだけを処理する

5.2　IFERRORはすべてのエラーを処理する

5.3　診断中はエラーを隠さない

第6章　実務で壊れにくい完成式を作る

6.1　検索失敗と空白返却を分ける

6.2　診断用の列を残す

6.3　XLOOKUPへ置き換えても型問題は残る

第7章　診断チェックリスト

7.1　#N/Aの場合

7.2　0が表示される場合

7.3　日付が一致しない場合

まとめ　表示を整える前に原因を分ける