ExcelのXLOOKUP関数は、従来のVLOOKUPやHLOOKUPの課題を解消した、柔軟で高機能な検索関数として注目を集めています。特に検索条件の精度を左右するのが「一致モード」の指定です。
「検索値が確かにあるはずなのに一致しない…」
「近い値を拾いたいのに、なぜか空白になる」
このような現象の多くは、XLOOKUPの一致モードが適切に設定されていないことが原因です。
この記事では、XLOOKUP関数の一致モードの使い方を、実務例を交えながらわかりやすく解説します。完全一致・近似一致・ワイルドカード一致の違いと使い分け方を理解し、Excel業務の正確性と効率をアップさせましょう。
目次
✅ XLOOKUP関数とは？一致モードは第5引数で指定
XLOOKUP関数の基本構文は以下の通りです：
=XLOOKUP(検索値, 検索範囲, 戻り値範囲, [見つからなかったときの値], [一致モード], [検索モード])
・ 一致モード（第5引数）とは？
一致モードとは、「検索値と検索範囲をどのように照合するか」を指定するオプションです。
一致モードには以下の4種類があります：
| 値 | 種類 | 説明 |
|---|---|---|
| 0（省略時） | 完全一致 | 一致する値を正確に検索 |
| -1 | 次に小さい値（近似一致） | 検索値以下の最大値を検索（昇順前提） |
| 1 | 次に大きい値（近似一致） | 検索値以上の最小値を検索（昇順前提） |
| 2 | ワイルドカード一致 | *や?などを含む曖昧検索が可能 |
✅ 【一致モード0】完全一致（デフォルト）
XLOOKUPのデフォルトの一致モードは「0（完全一致）」です。
指定しなくても、自動的に完全一致になります。
■ 使用例：
=XLOOKUP("A001", A2:A10, B2:B10)
→ A2:A10から「A001」を探し、一致した行のB列の値を返します。
■ 特徴：
- 一致する値が見つからなければ
#N/Aを返す - 値の正確性を重視する業務に最適（顧客ID・商品コードなど）
■ 活用シーン：
- 商品コードから商品名を取得
- 社員番号から部署名を取得
- 顧客IDに基づく取引履歴の照会
【Excel】【トラブル解決】XLOOKUPが使えない原因と対処法とは？バージョン制限・エラー対策・代替関数も解説！
✅ 【一致モード-1】次に小さい値を返す（近似一致）
「検索値と完全に一致しない場合でも、それ以下の最大値を返したい」ケースで使います。
ただし、検索範囲が昇順に並んでいる必要があります。
■ 使用例：
=XLOOKUP(70, A2:A10, B2:B10, "なし", -1)
→ A列が「点数」、B列が「評価」の場合、「70点以下の最大点数」に該当する評価を返します。
■ 特徴：
- データは昇順でソートされていることが前提
- 価格帯・評価などの範囲判定に便利
■ 活用シーン：
- 点数に応じたランク判定（90以上＝S、80以上＝Aなど）
- 金額に応じた割引率の自動表示
- 税率や送料の自動適用
✅ 【一致モード1】次に大きい値を返す（逆近似一致）
「検索値と完全一致しない場合、それ以上の最小値」を取得したいときに使います。
こちらも検索範囲が昇順で並んでいる必要があります。
【Excel】XLOOKUP関数で複数条件を扱う方法とは？INDEX・FILTERとの違いも解説【実務対応】
■ 使用例：
=XLOOKUP(50, A2:A10, B2:B10, "該当なし", 1)
→ 50以上の最小値に一致するB列の値を返します。
■ 特徴：
- -1とは逆方向の検索
- 在庫最小数・納期指定・価格上限などで有効
■ 活用シーン：
- 必要数量以上の在庫を持つ商品を抽出
- 最短納期に該当する出荷スケジュール
- 価格条件を満たすプランの提案
✅ 【一致モード2】ワイルドカード一致
部分一致やパターン一致をしたい場合は、一致モードを2にしてワイルドカード検索を行います。
| 記号 | 意味 |
|---|---|
* | 任意の文字列（0文字以上） |
? | 任意の1文字 |
■ 使用例：
=XLOOKUP("*田*", A2:A10, B2:B10, "該当なし", 2)
→ 「田」が含まれるセルを検索して、対応するB列の値を返す。
■ 注意点：
- 完全一致との併用不可
- ワイルドカードを含む検索値を文字列として指定すること
■ 活用シーン：
- 氏名のあいまい検索（例：「田中」「中田」など）
- 商品名に特定のキーワードが含まれるデータの取得
- メールアドレスのドメイン判定（
*@gmail.comなど）
✅ 一致モードを省略したときの動作に注意！
XLOOKUP関数は、第5引数（＝一致モード）を省略すると、**自動的に「0：完全一致」**になります。
=XLOOKUP(A2, A2:A10, B2:B10)
→ これは「完全一致検索」として処理されます。
■ 意図的に近似一致をしたい場合は、必ず明示的に -1 や 1 を指定する必要があります。
■ よくあるエラーとその原因・対処法
| エラー | 原因 | 対処法 |
|---|---|---|
#N/A | 完全一致できない | 第5引数に「-1」や「2」を検討する。あるいはIFNAでエラー処理を追加 |
#VALUE! | 引数の不一致 | ワイルドカード使用時に一致モードの指定漏れがないか確認 |
| 意図しない結果になる | ソートされていないのに近似一致を使っている | 昇順に並べ直すか一致モードを見直す |
【Excel】複数シートをPDFに変換する方法【すべてのシートを1つのPDFにまとめる！】
■ 一致モード別・おすすめ使用ケース早見表
| 一致モード | 内容 | おすすめシーン |
|---|---|---|
| 0（完全一致） | 正確な一致のみ取得 | 商品コード検索、ID管理、マスタ参照 |
| -1（次に小さい値） | 検索値以下の最大値を取得 | 点数評価、割引判定、税率 |
| 1（次に大きい値） | 検索値以上の最小値を取得 | 最短納期、在庫検索、プラン選定 |
| 2（ワイルドカード） | パターン一致 | あいまい検索、名前検索、ドメイン判定 |
■まとめ：XLOOKUPの一致モードを使い分けて柔軟な検索を実現しよう
XLOOKUP関数は、ただ使うだけでも便利ですが、「一致モード」の理解を深めることで検索の精度・柔軟性が一気に向上します。
- デフォルトは 完全一致（0）
- 近似一致には 昇順ソートが必須（-1, 1）
- 曖昧検索には ワイルドカード（2） を活用
一致モードを使いこなすことで、「なぜかヒットしない」「間違った値が返る」といったトラブルを避けることができ、日常業務の検索処理が格段にスムーズになります。
ぜひ本記事を参考に、XLOOKUPをもっと自由に活用してください。