XLOOKUPで検索値が見つからないときは、第4引数に表示したい内容を指定します。「該当なし」なら "該当なし"、空白表示なら ""、数値の0なら 0 です。ただし、「検索結果のセルが空白で0になる場合」は別の対策が必要です。
練習用Excelファイル（無料ダウンロード）
商品名・価格の入力用データです。D2に検索商品を入れ、本文の式をE2などへ入力して試してください。
Excelファイル（.xlsx）／登録不要／数式・練習データ入り
目次
まず動かしてみよう｜この記事のサンプルをその場で試せます
練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。
XLOOKUP の基本（書き方・一致モード・下から検索・VLOOKUP との違い）は、XLOOKUP関数の使い方にまとめています。
第4引数に「見つからない場合」を指定する
A2:A4に商品名、B2:B4に価格、D2に検索する商品を入力します。
| 行 | A：商品 | B：価格 |
|---|---|---|
| 2 | りんご | 120 |
| 3 | みかん | 80 |
| 4 | バナナ | 150 |
ポイントD2が「りんご」なら120、「ぶどう」なら「該当なし」です。第4引数を省略し、完全一致で見つからなければ #N/A になります。XLOOKUPはMicrosoft 365・Excel 2024・2021が対象です。
空白・0・ハイフンにする数式
| 見つからないときの表示 | 数式 |
|---|---|
| 空白表示 | =XLOOKUP(D2,A2:A4,B2:B4,"") |
| 数値の0 | =XLOOKUP(D2,A2:A4,B2:B4,0) |
| ハイフン | =XLOOKUP(D2,A2:A4,B2:B4,"-") |
ポイント"" は長さ0の文字列です。何も入っていないセルとは異なり、ISBLANKではTRUEになりません。0を指定すると、本来の価格0と未登録を区別できなくなるため、確認用の一覧では「未登録」などの表示が便利です。
▶ 空白・0・ハイフンの式（E3〜E5）を、上の練習ツールで試す
検索セルが未入力なら何も表示しない
ポイント第4引数は未一致時の指定なので、検索セルD2が空白かどうかまでは制御しません。入力前の表示を消すにはIFで確認します。
ポイントこれで、未入力は空白表示、入力済みで見つからない場合は「該当なし」と分けられます。XLOOKUPとIFの組み合わせでは、検索結果による表示切り替えも説明しています。
見つかったのに結果が0になる場合
注意対応する商品が見つかっても、価格セルが本当に空白だとXLOOKUPは0を返すことがあります。この場合は「見つからない場合」の第4引数では直りません。
ポイント数値の0を残し、空白セルだけを空白表示にしたい場合は、戻り範囲の空白を先に変換します。
ポイント例えばB2が空白なら空白表示、B2が数値0なら0、B2が120なら120です。検索値が存在しなければ「該当なし」が返ります。結果は元の値に応じて数値または文字列になります。
ポイント氏名など文字列だけを返す表では、戻り範囲に &"" を付ける方法も使えます。
注意こちらは数値も文字列になるため、金額計算に使う戻り範囲には前のIF式が向いています。XLOOKUPの外側に &"" を付けるだけでは、すでに返された0が文字の「0」になるだけで、元の空白を判別できません。
IFERROR・IFNAとの違い
| 方法 | 処理する場面 |
|---|---|
| XLOOKUPの第4引数 | 検索で一致が見つからない場合 |
| IFNA(数式,"該当なし") | 式の結果が#N/Aの場合 |
| IFERROR(数式,"エラー") | #N/A以外も含めてエラーになった場合 |
注意例えば検索範囲と戻り範囲の大きさが違う場合や、戻り先セル自体がエラーの場合は、単なる未一致とは異なります。IFERRORで全部空白にする前に、原因を確認してください。
ポイントExcel 2019・2016でVLOOKUPを使う場合は、次のようにIFNAで未一致の #N/A を処理できます。
▶ IFNA＋VLOOKUPの式（E9）を、上の練習ツールで試す
表にあるのに「該当なし」になるとき
注意商品コードの前後に空白がないか、数値と文字列が混在していないか、参照範囲に追加した行が含まれているかを確認します。全角・半角の違いにも注意してください。表が増えるたびに見つからなくなる場合はテーブルで検索範囲を自動拡張する方法が役立ちます。
ポイント名前の一部で探したい場合は部分一致とワイルドカードを使います。第4引数を変更しても、完全一致が部分一致に変わるわけではありません。
練習用Excelファイル（無料ダウンロード）
商品名・価格の入力用データです。D2に検索商品を入れ、本文の式をE2などへ入力して試してください。
Excelファイル（.xlsx）／登録不要／数式・練習データ入り
仕様の確認：Microsoft公式・XLOOKUP関数
別のシートの表から検索するときは、XLOOKUPで別シート・複数シートから検索する方法もあわせてご覧ください。
コメント