XLOOKUPで未入力のセルが0になる場合は、戻り値に使うデータの空白を、先に空文字列に置き換えます。実際に入力されている数値の0は残せるので、「未入力」と「金額0」を区別できます。
無料Excelサンプルで、まず試してみる
XLOOKUPの空白・0判定サンプルをダウンロード（無料・Excel）
.xlsx／登録不要／数式・練習データ入り
対応：Microsoft 365・Excel 2021・Excel 2024
開くシートは「まず試す」。最初はこの3ステップです。
- 「まず試す」シートの黄色いB4を選びます。
- 商品IDをP001 → P002 → P003の順に変更します。
- 緑のE4が空白 → 0 → 1,200と切り替われば成功です。
画像をタップ・クリックして拡大
保護ビューで入力できない場合は、入手元を確認して「編集を有効にする」を選びます。別の練習を始める前に、ファイルのコピーを保存しておくと初期状態に戻せます。
「空白」「数値の0」「見つからない」を区別する
価格表では、未入力は「金額がまだ決まっていない」、0は「金額が0で確定している」というように意味が違います。検索結果をすべて空白にしてしまう前に、どの状態を表示したいか確認しましょう。
| 商品ID | 元の金額 | 表示したい結果 |
|---|---|---|
| P001 | 未入力のセル | 空白 |
| P002 | 数値の0 | 0 |
| P003 | 数値の1200 | 1,200 |
| P999 | 商品ID自体がない | 未登録 |
XLOOKUPの第4引数は「検索値が見つからない場合」の指定です。商品IDが見つかり、その行の金額が未入力だった場合とは別の処理です。見つからない場合の指定と混同しないようにします。
手順1：空白を保つ検索用の列を用意する
サンプルのA9:A11が商品ID、B9:B11が金額です。C9に次の式を入れ、C11までコピーします。配布ファイルには入力済みです。
=IF(LEN(B9)=0,"",B9)LENで文字数を確認し、0文字なら空文字列の""を返します。金額が0ならLENの結果は1なので、数値の0をそのまま返します。C列は見た目がB列と似ていますが、未入力セルを「空白表示になる数式の結果」に置き換えた列です。
手順2：XLOOKUPの戻り範囲をC列にする
B4に検索する商品IDを入れ、E4に次の式を入力します。
=XLOOKUP(B4,A9:A11,C9:C11,"未登録",0)- B4：検索する商品ID。
- A9:A11：商品IDを探す範囲。
- C9:C11：空白を保つように整えた戻り範囲。
- 「未登録」：IDが見つからないときの表示。最後の0は完全一致。
P001、P002、P003を順に入力し、空白・0・1,200になることを確認します。E4は数値を返すケースでは数値のままなので、P003の結果を計算に利用できます。
画像をタップ・クリックして拡大
補助列を使わず、1つの式で書く場合
仕組みが分かったら、戻り範囲をIFで直接加工する書き方も使えます。次の式はB9:B11を配列として判定します。サンプルの初期状態は、途中経過を確認しやすいC列を使う式です。
=XLOOKUP(B4,A9:A11,IF(B9:B11="","",B9:B11),"未登録",0)「結果が0なら空白」という後処理は、P002の本当の0まで隠します。また、結果に&""を付ける方法は数値も文字列に変わるため、後で集計する金額には使い分けが必要です。
画像をタップ・クリックして拡大
空白にならないときの確認点
| 症状 | 確認すること |
|---|---|
| P001でも0が出る | 戻り範囲がB9:B11のままになっていないか。練習用の式ではC9:C11を指定。 |
| 見た目は空白なのに判定されない | 元データにスペースや改行が入っていないか。LENで文字数を確認。 |
| 未登録と表示される | 商品IDの前後の空白や文字の違いを確認。 |
| #NAME?になる | XLOOKUP対応版か確認。Excel 2019・2016では利用不可。 |
よくある質問
空白を返したセルは、完全な空セルですか？
いいえ。E4には数式があり、結果が空文字列になっています。「見た目が空白」と「何も入力していないセル」は異なります。後続の処理で空白判定するときも、この違いを意識してください。
データを追加した場合は？
検索範囲と戻り範囲を同じ行まで広げ、C列の式も追加行へコピーします。頻繁に行が増える表ではテーブル参照を使う方法が便利です。
サンプルで結果を確かめる
本文のセル位置はダウンロードファイルにそろえています。まず黄色いセルを変更して動きを確認し、次に自分の表に合う範囲へ置き換えてください。
XLOOKUPの空白・0判定サンプルをダウンロード（無料・Excel）
コメント