「XLOOKUPで検索しているのに値が見つからない」「#N/Aエラーが返ってしまう」「正しいはずなのに空白になる」
ExcelでXLOOKUP関数を使っていると、このような検索の失敗に直面することがあります。
XLOOKUPは非常に柔軟で使いやすい検索関数ですが、わずかな違いでも検索が失敗することがあるため、使いこなすためには正しい理解が必要です。
この記事では、XLOOKUP関数で「見つからない」となる代表的な原因とその対処法、検索失敗時の代替表示の設定方法、実務での工夫までをわかりやすく解説します。
目次
- ✅ XLOOKUPで「見つからない」とは？代表的な症状
- ✅ XLOOKUP関数の基本構文を再確認
- ✅ 【原因と対処法】XLOOKUPで値が見つからない理由とその解決方法
- ・検索値と検索範囲のデータが一致していない（見た目は同じでも）
- ・一致モード（第5引数）の指定ミス
- ・検索範囲と戻り値範囲のサイズが一致していない
- ・検索値が本当に存在しない
- ✅ 第4引数（見つからなかったときの値）を正しく活用しよう
- ・ 値がなければ「データなし」と表示する方法
- ✅ ワイルドカードを使った部分一致の活用方法
- ■ 実務で役立つ！見つからない場合の代替表示活用法
- ■ FILTER関数との併用で見つからない問題を柔軟に処理する
- ■ よくある質問（FAQ）
- Q. XLOOKUPで検索しても一致しないのはなぜ？
- Q. #N/Aが出ると困る。自動で「なし」などに変えられないか？
- Q. データがあっても空白になるのはなぜ？
- ■まとめ：XLOOKUPで見つからないときの対処法をマスターしよう
- ・ 覚えておきたいチェックポイント
✅ XLOOKUPで「見つからない」とは？代表的な症状
XLOOKUPで値が見つからない場合、次のような現象が発生します。
#N/Aエラーが表示される- セルが空白になる
- 意図した値が表示されない
- 違う行の値が表示されてしまう
✅ XLOOKUP関数の基本構文を再確認
=XLOOKUP(検索値, 検索範囲, 戻り値範囲, [見つからなかったときの値], [一致モード], [検索モード])
- 検索値：探したいデータ（例：商品コード）
- 検索範囲：検索対象の列（A列など）
- 戻り値範囲：一致したときに取得したい列（B列など）
- 第4引数以降は省略可能（オプション）
【Excel】XLOOKUP関数の対応バージョンとは？使える条件・非対応時の対処法まで完全ガイド
✅ 【原因と対処法】XLOOKUPで値が見つからない理由とその解決方法
【Excel】【トラブル解決】XLOOKUPが使えない原因と対処法とは？バージョン制限・エラー対策・代替関数も解説！
・検索値と検索範囲のデータが一致していない（見た目は同じでも）
✔ 主なパターン
- 前後に 半角スペース や 全角スペース が入っている
- データ型が異なる（数値 vs 文字列）
- 全角と半角が混在している（例：「A001」 vs 「Ａ００１」）
⇒【Excel】XLOOKUPで完全一致モードでも一致してしまう理由とは？全角・半角やスペースに潜む落とし穴を徹底解説
✅ 対処法
TRIM()関数で余分なスペースを削除：=XLOOKUP(TRIM(A2), A2:A100, B2:B100)TEXT()でフォーマットを統一（ゼロ埋め）：=XLOOKUP(TEXT(A2,"0000"), A2:A100, B2:B100)
・一致モード（第5引数）の指定ミス
✔ 症状
- 正確な値が存在するのに
#N/Aが返る - 部分一致で検索したいのに完全一致にしている
✅ 対処法
- **完全一致（デフォルト）**の場合は
0を指定または省略 - 近似一致（第5引数に
-1や1） を使うときは、検索範囲が昇順であることを確認 - ワイルドカード（
*や?）を使う場合は2を指定
=XLOOKUP("A*", A2:A100, B2:B100, "見つかりません", 2)
・検索範囲と戻り値範囲のサイズが一致していない
✔ 症状
- 関数を入力しても
#VALUE!エラーや空白になる
✅ 対処法
- 検索範囲と戻り値範囲の行数が一致しているかを確認
=XLOOKUP(A2, A2:A100, B2:B100) ' ← OK
=XLOOKUP(A2, A2:A100, B2:B99) ' ← NG（サイズ不一致）
・検索値が本当に存在しない
✔ 症状
- 単純に、探している値が一覧に含まれていない
✅ 対処法
IFNA()関数でエラー時に別の値を表示する
=IFNA(XLOOKUP(A2, A2:A100, B2:B100), "該当なし")
または、XLOOKUPの第4引数を活用：
=XLOOKUP(A2, A2:A100, B2:B100, "該当なし")
✅ 第4引数（見つからなかったときの値）を正しく活用しよう
XLOOKUPは、検索値が見つからなかった場合の動作を第4引数で柔軟に指定できます。
これにより、#N/Aエラーを回避して、ユーザーにわかりやすい表示が可能になります。
・ 値がなければ「データなし」と表示する方法
=XLOOKUP(A2, 商品コード一覧!A2:A100, 商品名一覧!B2:B100, "データなし")
→ エラーではなく「データなし」と返すことで、業務上の混乱を防止できます。
✅ ワイルドカードを使った部分一致の活用方法
「完全に一致していないが、含まれていれば一致としたい」場合は、一致モードを「2（ワイルドカード一致）」に設定します。
・例：「田」が含まれる名前を検索する方法
=XLOOKUP("*田*", A2:A100, B2:B100, "該当なし", 2)
*：任意の文字列（0文字以上）?：任意の1文字
【Excel】文字列の含有チェックと色分けを行う方法｜SEARCH関数と条件付き書式で業務を見える化！
■ 実務で役立つ！見つからない場合の代替表示活用法
| 使用シーン | 第4引数に設定する内容の例 |
|---|---|
| 商品コードが存在しないとき | "未登録" と表示 |
| 顧客IDが見つからないとき | "要確認" と表示 |
| 検索対象が空欄のとき | ""（空白）を返してスッキリ表示 |
■ FILTER関数との併用で見つからない問題を柔軟に処理する
XLOOKUPは1件のみの検索に特化していますが、条件に一致するデータが複数行ある場合はFILTER関数の利用が有効です。
=IFERROR(FILTER(B2:B100, A2:A100=A2), "該当なし")
→ 検索結果がなければ "該当なし" と表示され、複数件が一致する場合も一括取得できます。
■ よくある質問（FAQ）
Q. XLOOKUPで検索しても一致しないのはなぜ？
A. 見た目が同じでも、スペース・全角/半角・数値と文字列の違いなどが原因で一致しないことがあります。TRIMやTEXT関数を使って調整しましょう。
Q. #N/Aが出ると困る。自動で「なし」などに変えられないか？
A. 第4引数を使えば簡単にできます。
=XLOOKUP(A2, A2:A100, B2:B100, "なし")
Q. データがあっても空白になるのはなぜ？
A. 検索範囲と戻り値範囲のサイズが一致していない、または参照範囲がズレている可能性があります。行数や列の範囲を再確認してください。
■まとめ：XLOOKUPで見つからないときの対処法をマスターしよう
XLOOKUPは非常に便利な関数ですが、わずかな違いが原因で検索が失敗することも多く、適切な対処法を知っておくことが重要です。
・ 覚えておきたいチェックポイント
- スペースや全角・半角の違いに注意
- 一致モード（第5引数）を適切に設定する
- 第4引数で「該当なし」などの代替表示を設定する
- 検索範囲と戻り値範囲の行数をそろえる
- FILTER関数を併用して複数条件にも対応
これらを押さえることで、XLOOKUP関数の検索精度と柔軟性が飛躍的に高まり、実務でのエラーや手戻りを防止できます。ぜひ本記事の内容を業務に活かして、効率的で正確なExcel活用を実現してください。