XLOOKUPで見つからない場合は空白・0に｜第4引数と空白セルの違い

XLOOKUPで検索値が見つからないときは、第4引数に表示したい内容を指定します。「該当なし」なら "該当なし"、空白表示なら ""、数値の0なら 0 です。ただし、「検索結果のセルが空白で0になる場合」は別の対策が必要です。

第4引数に「見つからない場合」を指定する

A2:A4に商品名、B2:B4に価格、D2に検索する商品を入力します。

=XLOOKUP(D2,A2:A4,B2:B4,"該当なし")

D2が「りんご」なら120、「ぶどう」なら「該当なし」です。第4引数を省略し、完全一致で見つからなければ #N/A になります。XLOOKUPはMicrosoft 365・Excel 2024・2021が対象です。

空白・0・ハイフンにする数式

"" は長さ0の文字列です。何も入っていないセルとは異なり、ISBLANKではTRUEになりません。0を指定すると、本来の価格0と未登録を区別できなくなるため、確認用の一覧では「未登録」などの表示が便利です。

検索セルが未入力なら何も表示しない

第4引数は未一致時の指定なので、検索セルD2が空白かどうかまでは制御しません。入力前の表示を消すにはIFで確認します。

=IF(D2="","",XLOOKUP(D2,A2:A4,B2:B4,"該当なし"))

これで、未入力は空白表示、入力済みで見つからない場合は「該当なし」と分けられます。XLOOKUPとIFの組み合わせでは、検索結果による表示切り替えも説明しています。

見つかったのに結果が0になる場合

対応する商品が見つかっても、価格セルが本当に空白だとXLOOKUPは0を返すことがあります。この場合は「見つからない場合」の第4引数では直りません。

数値の0を残し、空白セルだけを空白表示にしたい場合は、戻り範囲の空白を先に変換します。

=XLOOKUP(D2,A2:A4,IF(B2:B4="","",B2:B4),"該当なし")

例えばB2が空白なら空白表示、B2が数値0なら0、B2が120なら120です。検索値が存在しなければ「該当なし」が返ります。結果は元の値に応じて数値または文字列になります。

氏名など文字列だけを返す表では、戻り範囲に &"" を付ける方法も使えます。

=XLOOKUP(D2,A2:A4,B2:B4&"","該当なし")

こちらは数値も文字列になるため、金額計算に使う戻り範囲には前のIF式が向いています。XLOOKUPの外側に &"" を付けるだけでは、すでに返された0が文字の「0」になるだけで、元の空白を判別できません。

IFERROR・IFNAとの違い

例えば検索範囲と戻り範囲の大きさが違う場合や、戻り先セル自体がエラーの場合は、単なる未一致とは異なります。IFERRORで全部空白にする前に、原因を確認してください。

Excel 2019・2016でVLOOKUPを使う場合は、次のようにIFNAで未一致の #N/A を処理できます。

=IFNA(VLOOKUP(D2,A2:B4,2,FALSE),"該当なし")

表にあるのに「該当なし」になるとき

商品コードの前後に空白がないか、数値と文字列が混在していないか、参照範囲に追加した行が含まれているかを確認します。全角・半角の違いにも注意してください。表が増えるたびに見つからなくなる場合はテーブルで検索範囲を自動拡張する方法が役立ちます。

名前の一部で探したい場合は部分一致とワイルドカードを使います。第4引数を変更しても、完全一致が部分一致に変わるわけではありません。

仕様の確認：Microsoft公式・XLOOKUP関数