ExcelのXLOOKUP関数は非常に使い勝手のよい関数ですが、
「部分一致の検索はできないの？」
と困ってしまったことはありませんか？
「XLOOKUP 部分一致 できない」と検索してこの記事にたどり着いた方も多いと思います。
この記事では、
・XLOOKUP関数で部分一致する基本形
・「含む」「前方一致」「後方一致」の考え方
・よくある勘違いと実務での最適解
を、実務目線でそのまま使える数式に絞って解説します。
「XLOOKUP 含む」「XLOOKUP あいまい検索」といったキーワードで調べている方にも役立つ内容です。
XLOOKUPは部分一致できない？【結論】
まず前提として、XLOOKUP関数は完全一致が基本の関数です。
そのため、何も設定しない状態では部分一致検索はできません。
例えば、以下のようなリストがあったとします。
この表から、
・部署 → 人事部
のデータを、「人事」という検索ワードを使い、社員名を検索します。
すうしきを以下のように入力してみます。
=XLOOKUP("人事",C2:C14,B2:B14)
すると、ご覧のとおり、エラーとなってしまいました。
では、あいまい検索で活用するワイルドカード「*」を使用してみるとどうでしょう。
以下のように、数式を変更してみます。
=XLOOKUP("*人事*",C2:C14,B2:B14)
やはりこれでもエラーが発生してしまいます。
上記のようにワイルドカードを使っても、「人事」を含む値は検索されません。
※XLOOKUPでは、第5引数（match_mode）を指定しない限り、ワイルドカードは有効になりません。
しかし、検索配列側を論理式で作ることで、部分一致検索が可能になります。
XLOOKUPで部分一致する基本形（最重要）
XLOOKUP関数で部分一致を行うには、SEARCH関数を使うことで可能となります。
SEARCH関数を使うことで、検索する文字が何番目に入っていても検索することができます。
=XLOOKUP(TRUE,IFERROR(SEARCH("人事",C2:C14)>0,FALSE),B2:B14)
■仕組みを分解して解説
この数式のポイントは、検索値そのものではなく「TRUE」を探している点です。
・SEARCH（”人事”,C2:C14）>0
→”人事”が見つかればTRUE
→”人事”が見つからなければエラー
を返します。
つまり、部分一致すればTRUE → 検索値をTRUEで指定しているため、部分一致を検索することが可能になります。
「>0」としているのは、検索文字が何番目に存在するか分からないためです。
SEARCH関数は、指定した文字列の開始位置を返す関数です。
つまり、先頭に対象の文字列があれば「1」、3文字目にあれば「3」と返すため、検索値を数値で指定することが困難になります。
そのため、あえて論理値（TRUE or FALSE）で返すことにより、確実に検索できるようになるということです。
※SEARCH関数は見つからないとエラーを返すため、IFERROR関数でFALSEに変換して安定させています。
前方一致・後方一致はできる？
部分一致と似たニーズとして、
・先頭が一致する（前方一致）
・末尾が一致する（後方一致）
場合を検索したいというケースもあります。
前方一致（先頭一致）
前方一致は、文字数が決まっている場合であればLEFT関数で対応できます。
例えば、左から2文字が「経理」のデータを検索するには、以下のように入力します。
=XLOOKUP(TRUE,LEFT(C2:C14,2)="経理",B2:B14)
ただし、
・文字数が一定ではない
・接頭語として含まれているかだけを判定したい
といった場合、LEFT関数では対応できません。
その場合は、SEARCH関数で「先頭にあるか」を判定します。
例えば、「法務」というワードが先頭にあるデータを検索するには、以下のように入力します。
=XLOOKUP(TRUE,SEARCH("法務",C2:C14)=1,B2:B14)
SEARCH関数は、文字列の開始位置を返すため、 先頭にある場合は必ず「1」になります。
そのため、文字数の指定ができない場合はSEARCH関数を活用してください。
後方一致（末尾一致）
後方一致も同様に、文字数が決まっている場合はRIGHT関数で対応できます。
例えば、社員名の後ろ一文字が「田」の人の部署名を検索するときは、以下のように入力します。
=XLOOKUP(TRUE,RIGHT(B2:B14,1)="田",C2:C14)
一方、文字数があやふやな場合は、SEARCH関数とLEN関数を組み合わせて判定します。
例えば、部署の末尾が「術部」のデータを検索するには、以下のように入力します。
=XLOOKUP(TRUE,SEARCH("術部",C2:C14)=LEN(C2:C14)-LEN("術部")+1,B2:B14)
この式では、
・文字列全体の長さから
・検索語の文字数を引き
・1を足す
ことで、「末尾に出現する文字位置」を算出し、後方一致しているかを判定することができます。
含む検索＝SEARCH関数、 位置が決まっている一致＝LEFT / RIGHT関数
と使い分けることで、シンプルに考えることができます。
該当データがない場合のエラー対策
該当するデータが存在しない場合、XLOOKUP関数は #N/Aエラーを返します。
実務では、第4引数を必ず指定しておくのがおすすめです。
=XLOOKUP(TRUE,SEARCH("情報システム",C2:C14)>0,B2:B14,"該当なし")
これだけで、エラーによる見づらさを防げます。
よくある勘違いと注意点
ここでは、XLOOKUP関数に関するよくある勘違いと注意点をまとめてみました。
ワイルドカード（*）は使えない？
XLOOKUPは完全一致が基本ですが、実はmatch_modeに「2」を指定すればワイルドカード検索も可能です。
例えば、冒頭で記載した数式では、エラーが発生してしまいましたね。
=XLOOKUP("*人事*",C2:C14,B2:B14)この数式を、以下のように変更します。
第5引数（match_mode）を2にすることでワイルドカードを使用した部分一致を検索することができます。
VLOOKUP関数と少し勝手が違うので、戸惑いやすいポイントです。
=XLOOKUP("*人事*",C2:C14,B2:B14,,2)
ただし、SEARCH関数で位置を判定する方法を活用するほうが応用が効きます。
単純に検索ワードを含んでいる場合や、前方（先頭）・後方（末尾）一致でも活用できるので、XLOOKUP関数の活躍の幅が広がります。
複数条件で部分一致したい場合は？
「部署に○○を含み、かつ役職が△△」のように、 部分一致 × 複数条件で検索したいケースもあります。
その場合は、今回紹介したSEARCH関数の考え方を応用し、 論理式を掛け算（AND条件）・足し算（OR条件）で組み合わせることで可能となります。
👉 XLOOKUP関数の複数条件検索はこちらで詳しく解説しています。
こちらもぜひチェックしてみてください。
複数件を一覧で取得したい場合は？
XLOOKUP関数は、最初に見つかった1件のみを取得する関数です。
条件に合うデータを一覧で取得したい場合は、FILTER関数を使う方が適しています。
👉 FILTER関数の使い方はこちらで詳しく解説しています。
こちらもぜひチェックしてみてください。
XLOOKUPの「部分一致」「含む検索」「ワイルドカード検索」は、使い分けを理解すれば決して難しくありません。
まとめ｜XLOOKUP部分一致はこの形を覚えればOK
XLOOKUPでも、工夫すれば部分一致検索は十分に可能です。
★ポイント
✅手軽に部分一致するなら → match_mode=2＋ワイルドカード
✅柔軟に制御したいなら → SEARCH関数を組み合わせる
✅前方・後方一致は、文字数固定ならLEFT/RIGHT関数
✅文字数が不定の場合は、SEARCH関数で位置判定
✅1件取得ならXLOOKUP関数、一覧取得ならFILTER関数
部分一致検索は、実務で使う場面が非常に多いテクニックです。
まずは今回紹介した基本形を押さえ、 必要に応じて 複数条件検索 や FILTER関数 へステップアップしてみてください。
最後まで読んでいただき、ありがとうございました。
コメント