【Excel】XLOOKUP関数でワイルドカード検索をする!Excelの部分一致ルックアップ手順

Excelで特定の文字列を含むデータを検索したい場面は多いものです。例えば、商品コードの一部だけが分かっている場合に、該当する商品名を探し出すといったケースが考えられます。

ExcelのXLOOKUP関数は、このような部分一致検索を簡単に行える強力な機能です。従来のVLOOKUP関数では難しかった、柔軟な検索が可能になります。

この記事では、XLOOKUP関数を使ったワイルドカード検索の方法を、具体的な手順とともに解説します。Excelでのデータ検索効率を格段に向上させることができるでしょう。

XLOOKUP関数におけるワイルドカード検索の仕組み

ExcelのXLOOKUP関数は、指定した検索値に基づいて、検索範囲から対応する値を検索し、結果を返す関数です。従来のVLOOKUP関数と比較して、検索方向の自由度が高い、エラー処理が容易などのメリットがあります。

特に、XLOOKUP関数が持つ「match_mode(検索モード)」引数を利用することで、ワイルドカード文字を使った部分一致検索が容易になります。これにより、完全一致だけでなく、「前方一致」「後方一致」「任意の文字列が含まれる」といった柔軟な検索が可能となります。

ワイルドカード文字とは、特定の文字の代わりに任意の文字列や文字を表す特殊な記号のことです。Excelでは、アスタリスク(*)と疑問符(?)が主にワイルドカード文字として使用されます。

ワイルドカード文字の種類と役割

XLOOKUP関数で利用できる主なワイルドカード文字は以下の2種類です。

アスタリスク(*): 0文字以上の任意の文字列を表します。例えば、「*りんご」と指定すると、「りんご」はもちろん、「ふじりんご」や「王林りんご」のように、先頭に何らかの文字列が付く「りんご」を検索できます。

疑問符(?): ちょうど1文字の任意の文字を表します。例えば、「あ?え」と指定すると、「あいうえお」の「あ」と「え」の間が1文字であれば、「あいえ」や「あええ」といった文字列を検索できます。

これらのワイルドカード文字を検索値に含めることで、XLOOKUP関数は部分一致検索を実行します。この機能は、データが不完全な場合や、曖昧な条件で検索したい場合に非常に役立ちます。

XLOOKUP関数でワイルドカード検索を行う手順

XLOOKUP関数でワイルドカード検索を行うには、関数の構文を理解し、適切な引数を指定する必要があります。ここでは、具体的な手順を解説します。

XLOOKUP関数の基本的な構文は以下の通りです。

XLOOKUP(検索値, 検索範囲, 戻り範囲, [if_not_found], [match_mode], [search_mode])

この中で、ワイルドカード検索に不可欠なのが「match_mode」引数です。

match_mode引数の指定方法

「match_mode」引数には、以下のいずれかの数値を指定します。

• 0: 完全一致(デフォルト)
• -1: 完全一致(検索値より小さい値)
• 1: 完全一致(検索値より大きい値)
• 2: ワイルドカード文字による一致

ワイルドカード検索を行う場合は、この「match_mode」引数に「2」を指定します。これにより、検索値に含まれるアスタリスク(*)や疑問符(?)が特殊文字として認識され、部分一致検索が実行されます。

1. 検索値にワイルドカード文字を含める
   例えば、「*東京」のように、検索したい文字列の一部とワイルドカード文字を組み合わせて入力します。
2. XLOOKUP関数を入力する
   セルに「=XLOOKUP(」と入力し、数式バーに表示される構文ガイドに従って各引数を入力していきます。
3. 検索値、検索範囲、戻り範囲を指定する
   最初の3つの引数(検索値、検索範囲、戻り範囲)は必須です。検索値にはワイルドカードを含む文字列を、検索範囲と戻り範囲には対応するセル範囲を指定します。
4. match_mode引数に「2」を指定する
   4番目の引数「if_not_found」は省略可能ですが、5番目の引数「match_mode」には必ず「2」を入力します。
5. 数式を確定する
   Enterキーを押して数式を確定します。指定した条件に一致する値が検索範囲に見つかれば、戻り範囲から対応する値が表示されます。

具体的な数式例

例えば、A列に都道府県名が入力されており、B列にその都道府県の県庁所在地が入力されているとします。ここでは、「県庁所在地が「東」で始まる都道府県名」を検索したい場合を考えます。

検索値が「東京」という文字列を含む場合、検索値セル(例: D1)に「*東京」と入力します。そして、以下の数式を別のセルに入力します。

=XLOOKUP("*東京", A1:A10, B1:B10, "該当なし", 2)

この数式では、

• "*東京": 検索値。県庁所在地が「東京」で終わるものを検索します。
• A1:A10: 検索範囲。都道府県名が入力されている列です。
• B1:B10: 戻り範囲。検索値に対応する県庁所在地が入力されている列です。
• "該当なし": 検索値が見つからなかった場合に表示する値です。
• 2: match_mode引数。ワイルドカード文字による一致を指定します。

この数式により、A列の都道府県名で、県庁所在地が「東京」で終わるものが検索され、B列の対応する県庁所在地が表示されます。

もし、県庁所在地が「東京」で始まるものを検索したい場合は、検索値を「東京*」とします。

=XLOOKUP("東京*", A1:A10, B1:B10, "該当なし", 2)

このように、ワイルドカード文字を検索値のどこに配置するかで、検索の仕方が変わります。

XLOOKUP関数でワイルドカード検索ができない場合の対処法

XLOOKUP関数でワイルドカード検索を行おうとしても、期待通りの結果が得られない場合があります。そのような場合に確認すべき点と対処法を解説します。

match_mode引数が正しく指定されていない

最も一般的な原因は、「match_mode」引数が正しく指定されていないことです。ワイルドカード検索を行うためには、この引数に必ず「2」を指定する必要があります。

• 確認点: 関数入力時に「match_mode」引数に「2」が入力されているか確認してください。デフォルト値は「0」(完全一致)です。
• 対処法: 数式を編集し、「match_mode」引数に「2」を明示的に入力してください。

検索値のワイルドカード文字が正しくない

検索値に含めるワイルドカード文字(*や?)が、Excelの仕様と異なっている可能性も考えられます。

• 確認点: ワイルドカード文字としてアスタリスク(*)と疑問符(?)以外を使用していないか確認してください。
• 対処法: Excelで認識されるワイルドカード文字のみを使用してください。

検索範囲と戻り範囲の指定が不適切

検索範囲と戻り範囲のセル指定が間違っていると、当然ながら正しい結果は得られません。

• 確認点: 検索したいデータが含まれる列が「検索範囲」に、表示したいデータが含まれる列が「戻り範囲」に正しく指定されているか確認してください。また、両範囲の行数が一致しているかも重要です。
• 対処法: 数式を編集し、各範囲を正しく指定し直してください。

検索したい文字自体がワイルドカード文字である場合

検索したい文字列そのものが、アスタリスク(*)や疑問符(?)である場合、そのまま入力するとExcelはワイルドカードとして解釈してしまいます。例えば、「*」という文字そのものを検索したい場合などです。

• 確認点: 検索値に「*」や「?」を含める必要がある場合、Excelがそれをワイルドカードとしてではなく、通常の文字として認識させる必要があります。
• 対処法: ワイルドカード文字の前にチルダ(~)を付けることで、その文字をリテラル(通常の文字)として扱うことができます。例えば、「*」を検索したい場合は、検索値を「~*」とします。

例えば、A列に商品名がリストされており、商品名に「*」が含まれる商品を探したい場合は、以下の数式を使用します。

=XLOOKUP("~*", A1:A10, B1:B10, "該当なし", 2)

Excelのバージョンによる制限

XLOOKUP関数は、比較的新しいバージョンのExcelで利用可能な関数です。古いバージョンのExcelでは利用できない場合があります。

• 確認点: ご利用のExcelのバージョンがXLOOKUP関数に対応しているか確認してください。XLOOKUP関数はMicrosoft 365のExcel、Excel 2021以降で利用可能です。
• 対処法: 古いバージョンのExcelをご利用の場合は、VLOOKUP関数とワイルドカードを組み合わせる、あるいはPower Queryなどの別の機能を利用する必要があります。

XLOOKUP関数とVLOOKUP関数のワイルドカード比較

XLOOKUP関数が登場する以前は、Excelで部分一致検索を行う場合、主にVLOOKUP関数とワイルドカード文字を組み合わせていました。ここでは、両者の違いを比較します。

項目	XLOOKUP関数	VLOOKUP関数
部分一致検索の方法	match_mode引数に「2」を指定する	検索値にワイルドカード文字(“*”, “?”)を付与し、range_lookup引数に「TRUE」を指定する(または省略)
検索方向の自由度	左右どちらの列でも検索可能	左端の列しか検索できない
戻り値の列指定	戻り範囲を指定するだけでよい	列番号で指定する必要がある
エラー処理	if_not_found引数で容易に設定可能	IFERROR関数などと組み合わせる必要がある
利用可能なバージョン	Microsoft 365, Excel 2021以降	比較的古いバージョンから利用可能

XLOOKUP関数でワイルドカード検索を行うメリット

XLOOKUP関数でワイルドカード検索を行う最大のメリットは、その簡潔さと柔軟性にあります。VLOOKUP関数のようにrange_lookup引数を「TRUE」にする必要がなく、match_mode引数に「2」を指定するだけで、意図した通りの部分一致検索が可能です。

また、検索方向の制約がなく、戻り値の列指定も直感的であるため、数式の作成や管理が容易になります。エラー処理も関数内で完結できるため、より効率的なデータ検索が実現できます。

これらの点から、新規でExcelファイルを作成する場合や、Excelのバージョンが対応している環境では、XLOOKUP関数を利用することが推奨されます。

XLOOKUP関数は、Excelでのデータ操作の可能性を大きく広げる関数です。ワイルドカード検索を使いこなすことで、より複雑で柔軟なデータ抽出が可能になります。

この記事では、XLOOKUP関数でワイルドカード検索を行う基本的な手順と、問題が発生した場合の対処法を解説しました。ワイルドカード文字の種類やmatch_mode引数の指定方法を理解することで、様々な状況で部分一致検索を実行できるようになります。

今後は、XLOOKUP関数と他の関数(例: SUMIFS、FILTER関数など)を組み合わせて、より高度なデータ集計や分析に挑戦してみてください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】