XLOOKUPで空白が0になるときの対処法｜本当の0を残して空白を返す

2026年9月21日2026年11月3日

XLOOKUPで未入力のセルが0になる場合は、戻り値に使うデータの空白を、先に空文字列に置き換えます。実際に入力されている数値の0は残せるので、「未入力」と「金額0」を区別できます。

XLOOKUP の基本（書き方・一致モード・下から検索・VLOOKUP との違い）は、XLOOKUP関数の使い方にまとめています。

この記事の基本の式

数式=XLOOKUP(E2,$A$2:$A$4,$B$2:$B$4,"未登録")

練習用Excelファイル（無料ダウンロード）

対応：Microsoft 365・Excel 2021・Excel 2024開くシートは「まず試す」。最初はこの3ステップです。

無料Excelサンプルファイルダウンロード（.xlsx・5KB）

Excelファイル（.xlsx）／登録不要／数式・練習データ入り

保護ビューで入力できない場合は、入手元を確認して「編集を有効にする」を選びます。別の練習を始める前に、ファイルのコピーを保存しておくと初期状態に戻せます。

「空白」「数値の0」「見つからない」を区別する

価格表では、未入力は「金額がまだ決まっていない」、0は「金額が0で確定している」というように意味が違います。検索結果をすべて空白にしてしまう前に、どの状態を表示したいか確認しましょう。

商品ID	元の金額	表示したい結果
P001	未入力のセル	空白
P002	数値の0	0
P003	数値の1200	1,200
P999	商品ID自体がない	未登録

ポイントXLOOKUPの第4引数は「検索値が見つからない場合」の指定です。商品IDが見つかり、その行の金額が未入力だった場合とは別の処理です。見つからない場合の指定と混同しないようにします。

実際に試すと、金額が未入力の P001 を =XLOOKUP(E2,$A$2:$A$4,$B$2:$B$4,"未登録") で返したところ、空白ではなく 0 と表示されました（③）。本当に 0 を登録した P002 と見分けがつきません。下の手順で作る検索用の C 列から返すと、P001 は空白、P002 は 0 のままになりました（④）。

エクセルのXLOOKUPで未入力のセルを返すと0になり検索用の列から返すと空白になる例

まず動かしてみよう｜この記事のサンプルをその場で試せます

練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。

手順1：空白を保つ検索用の列を用意する

サンプルのA9:A11が商品ID、B9:B11が金額です。C9に次の式を入れ、C11までコピーします。配布ファイルには入力済みです。

C9に入力（C11までコピー）：空白を保つ検索用の値を作る

数式=IF(LEN(B9)=0,"",B9)

ひとことで金額が未入力なら空文字列、入力があれば（0も含めて）そのまま返す

LEN(B9)=0

B9の文字数が0（未入力）かを確認します

""

未入力のときに返す、空文字列です

B9

入力があれば、その値をそのまま返します。数値の0は、LENが1なので、0のまま返ります

データの流れ

① P001は未入力、P002は0、P003は1,200結果

C9（空白）C100C111,200

ポイントLENで文字数を確認し、0文字なら空文字列の""を返します。金額が0ならLENの結果は1なので、数値の0をそのまま返します。C列は見た目がB列と似ていますが、未入力セルを「空白表示になる数式の結果」に置き換えた列です。

▶ この式（C9〜C11）を、上の練習ツールで試す

空白のセルは空白のまま返す（IFとLEN）式 =IF(LEN(B9)=0,"",B9) の解説図。C9に入力する — C9に =IF(LEN(B9)=0,"",B9) を入力。B9 は空欄なので 0 ではなく空白を返す

手順2：XLOOKUPの戻り範囲をC列にする

B4に検索する商品IDを入れ、E4に次の式を入力します。

E4に入力：戻り範囲を、検索用のC列にする

数式=XLOOKUP(B4,A9:A11,C9:C11,"未登録",0)

ひとことで元の金額（B列）ではなく、整えたC列を返して、空白と0を区別する

B4

検索値です。探したい商品IDです

A9:A11

検索範囲です。商品IDの列です

C9:C11

戻り範囲です。空白を保つように整えた、C列を指定します

"未登録"

商品ID自体が見つからないときの表示です

補足第4引数は「商品IDが見つからない場合」の指定です。商品IDが見つかり、その行の金額が未入力だった場合とは、別の処理です。

B4：検索する商品ID。
A9:A11：商品IDを探す範囲。
C9:C11：空白を保つように整えた戻り範囲。
「未登録」：IDが見つからないときの表示。最後の0は完全一致。

ポイントP001、P002、P003を順に入力し、空白・0・1,200になることを確認します。E4は数値を返すケースでは数値のままなので、P003の結果を計算に利用できます。

▶ この式（E4）を、上の練習ツールで試す

補助列を使わず、1つの式で書く場合

ポイント仕組みが分かったら、戻り範囲をIFで直接加工する書き方も使えます。次の式はB9:B11を配列として判定します。サンプルの初期状態は、途中経過を確認しやすいC列を使う式です。

E5に入力：補助列を使わず、1つの式で書く

数式=XLOOKUP(B4,A9:A11,IF(B9:B11="","",B9:B11),"未登録",0)

ひとことで戻り範囲を、XLOOKUPの中でIFで整えて、空白と0を区別する

IF(B9:B11="","",B9:B11)

戻り範囲です。B9:B11を配列として判定し、未入力は空文字列、数値の0や金額は、そのまま返します

補足結果に &”” を付ける方法は、数値も文字列に変わるので、計算に使う値には向きません。

注意「結果が0なら空白」という後処理は、P002の本当の0まで隠します。また、結果に&""を付ける方法は数値も文字列に変わるため、後で集計する金額には使い分けが必要です。

▶ この式（E5）を、上の練習ツールで試す

空白にならないときの確認点

症状	確認すること
P001でも0が出る	戻り範囲がB9:B11のままになっていないか。練習用の式ではC9:C11を指定。
見た目は空白なのに判定されない	元データにスペースや改行が入っていないか。LENで文字数を確認。
未登録と表示される	商品IDの前後の空白や文字の違いを確認。
#NAME?になる	XLOOKUP対応版か確認。Excel 2019・2016では利用不可。