【Excel】XMATCH関数でXLOOKUPより柔軟な検索条件を使う方法

XLOOKUP関数は便利な検索機能ですが、単一の検索条件では対応できない場面に直面することもあります。

複数の条件を組み合わせてデータを検索したい場合、XMATCH関数とXLOOKUP関数を組み合わせることで柔軟な検索が実現できます。

この記事では、XMATCHとXLOOKUPを連携させ、複雑な検索条件で目的のデータを抽出する方法を詳しく解説します。

XMATCH関数とXLOOKUP関数の連携で何ができるか

XLOOKUP関数は、指定した検索値に合致するデータを行単位で探し、対応する別の列の値を返す強力な関数です。

しかし、デフォルトでは「この商品名」かつ「この地域」といった複数の条件を同時に指定して検索する機能は持っていません。

そこでXMATCH関数と組み合わせることで、XLOOKUP単体では難しい複雑な検索条件に対応できるようになります。

XLOOKUP関数の基本と限界

XLOOKUP関数は、検索範囲と戻り範囲を指定するだけで簡単にデータを探せる利便性があります。

検索モードや一致モードも細かく設定でき、従来のVLOOKUP関数やHLOOKUP関数よりも柔軟です。

しかし、検索値が1つに限定されるため、「商品A」の「関東支店」での売上といった複数の情報を組み合わせた検索には工夫が必要です。

XMATCH関数で複数条件の位置を特定する仕組み

XMATCH関数は、指定した検索値が範囲内のどこにあるか、その相対的な位置番号を返します。

この関数は、検索値を論理式として与えることで、複数の条件を同時に評価できます。

例えば、「(商品列 = “A”) * (地域列 = “関東”)」のような論理式を使うと、両方の条件が真になる行のみが1と評価され、それ以外の行は0となります。

XMATCH関数でこの「1」を検索することで、複数条件に合致する最初の行の位置を正確に特定できるのです。

Excel for Microsoft 365専用の機能

XLOOKUP関数とXMATCH関数は、Excel for Microsoft 365のユーザー向けに提供されている新しい関数です。

Excel 2021でも利用できますが、Excel 2019以前のバージョンでは利用できません。

これらの関数を使うことで、より簡潔で強力な数式を作成できます。

複数の検索条件でデータを抽出する手順

ここでは、具体的なデータを使って「商品名」と「地域」の2つの条件で「売上」を検索する手順を解説します。

以下のデータを用意して一緒に操作を進めてください。

A列に商品名、B列に地域、C列に売上が入力されていると仮定します。

1. 検索条件の入力セルを用意する
   Excelシート上の任意のセルに、検索したい商品名と地域を入力するセルを用意します。
   例えば、E1セルに「検索商品名」、F1セルに「検索地域」と入力し、E2セルに「りんご」、F2セルに「関東」と入力しておきます。
2. XMATCH関数で複数条件に一致する行を特定する
   売上を表示したいセル(例: G2セル)に、まずXMATCH関数を入力します。
   XMATCH関数で検索対象の範囲全体から、指定された複数の条件に一致する最初の行の位置を特定します。
   =XMATCH(1, (A2:A10=E2)*(B2:B10=F2), 0)と入力します。
   ここで(A2:A10=E2)はA列がE2セルと同じ値の時に真(TRUE)を返し、(B2:B10=F2)はB列がF2セルと同じ値の時に真(TRUE)を返します。
   真は内部的に1、偽は0として扱われるため、両方の条件が真になる行だけが1 * 1 = 1 となり、XMATCH関数はこの「1」を検索します。
   最後の0は、完全一致を指定する引数です。
3. XLOOKUP関数で特定した行番号から結果を抽出する
   次に、上記で作成したXMATCH関数をXLOOKUP関数の検索値(lookup_value)と検索範囲(lookup_array)の代わりに使います。
   G2セルに入力した数式を以下のように完成させます。
   =XLOOKUP(XMATCH(1, (A2:A10=E2)*(B2:B10=F2), 0), ROW(A2:A10)-ROW(A2)+1, C2:C10)
   XLOOKUP関数の第一引数(検索値)にはXMATCH関数の結果(行番号)を直接使います。
   第二引数(検索範囲)には、XMATCHが返す相対的な行番号と一致する数値の配列を作成するため、ROW(A2:A10)-ROW(A2)+1を使用します。
   これはA2からA10までの各行の絶対行番号からA2の絶対行番号を引いて1を足すことで、検索範囲内の相対的な1から始まる連番を作成します。
   第三引数(戻り範囲)には、実際に取得したいデータが格納されているC列(C2:C10)を指定します。
   これにより、XMATCHで特定された条件に合致する行のC列の値が返されます。
4. 数式の確定と結果の確認
   数式をセルに入力後、Enterキーを押して確定します。
   Excel for Microsoft 365の動的配列機能により、特別な操作は不要です。
   E2セルやF2セルの値を変更すると、それに応じてG2セルの検索結果も自動的に更新されます。
   例えば、E2セルを「みかん」、F2セルを「関西」に変更すると、それに対応する売上がG2セルに表示されます。

数式を簡潔にする方法

XLOOKUP関数の第三引数(戻り範囲)は、XMATCH関数が返す行番号から直接値を取得するために使用します。

より簡潔な記述として、XMATCHの結果を直接行番号として使うこともできます。

=INDEX(C2:C10, XMATCH(1, (A2:A10=E2)*(B2:B10=F2), 0)) のようにINDEX関数と組み合わせることで、ROW(A2:A10)-ROW(A2)+1の部分を省略できます。

この方法でも同様に複数条件でのデータ抽出が可能です。

XMATCHとXLOOKUP連携時の注意点と対処法

XMATCH関数とXLOOKUP関数を組み合わせて使う際には、いくつか注意すべき点があります。

ここではよくある問題とその対処法について解説します。

#N/Aエラーが表示される

検索結果が#N/Aエラーとなる場合、指定した条件に一致するデータがテーブル内に存在しない可能性が高いです。

検索条件に入力した値がデータと完全に一致しているか確認してください。全角半角の違いや余分なスペースも原因となります。

また、数式内の参照範囲が正しいか、入力ミスがないかも確認が必要です。

XLOOKUP関数の[見つからない場合]引数を活用すると、エラーの代わりに任意のメッセージを表示できます。

例えば、=XLOOKUP(XMATCH(…), …, …, "データなし")のように設定できます。

論理式を組み合わせる際の注意

複数の条件を組み合わせる際には、AND条件を*(乗算)、OR条件を+(加算)で表現します。

(条件1)*(条件2)は両方が真の場合に1を返しますが、(条件1)+(条件2)はどちらか一方が真の場合に1以上を返します。

XMATCH関数でOR条件を使いたい場合は、=XMATCH(TRUE, (A2:A10=E2)+(B2:B10=F2)>0, 0)のように記述すると、どちらかの条件が真であれば一致と判断できます。

条件の数が多く複雑になる場合は、カッコを適切に使い、評価の優先順位を明確にすることが重要です。

Excelのバージョン制限

XLOOKUP関数とXMATCH関数は、Excel for Microsoft 365およびExcel 2021でのみ利用できます。

Excel 2019以前のバージョンではこれらの関数は存在しないため、数式を入力しても#NAME?エラーが表示されます。

古いバージョンをお使いの場合は、INDEX関数とMATCH関数を組み合わせて同様の複数条件検索を実現できます。

具体的には、=INDEX(C2:C10, MATCH(1, (A2:A10=E2)*(B2:B10=F2), 0)) のように数式を入力し、Ctrl+Shift+Enterで配列数式として確定する必要があります。

XMATCH・XLOOKUP連携と従来の検索関数の比較

項目	XMATCH・XLOOKUP連携	INDEX・MATCH連携	VLOOKUP関数
主な機能	複数条件でのデータ検索と抽出	複数条件でのデータ検索と抽出	単一条件でのデータ検索と抽出
複数条件検索	XMATCHで論理式を組み合わせて柔軟に対応	MATCHで論理式を組み合わせて対応	直接は不可、補助列が必要
数式の簡潔さ	動的配列で自動展開し、記述が比較的簡潔	配列数式としてCtrl+Shift+Enterが必要	条件が増えると数式が複雑化しやすい
エラー処理	XLOOKUPの[見つからない場合]引数で容易に設定	IFERROR関数で別途設定が必要	IFERROR関数で別途設定が必要
互換性	Excel for Microsoft 365, Excel 2021のみ	Excel 2007以降で利用可能	ほぼ全てのExcelバージョンで利用可能
検索方向	XLOOKUP, XMATCH共に左右、上下どちらも可能	INDEXは範囲指定、MATCHは上下のみ	左から右へ単方向のみ

XMATCH関数とXLOOKUP関数の組み合わせは、特にExcel for Microsoft 365環境であれば、これまでのINDEX関数とMATCH関数の組み合わせよりも直感的で簡潔な数式を作成できます。

まとめ

この記事では、XMATCH関数とXLOOKUP関数を組み合わせて、複数の検索条件でデータを抽出する方法を解説しました。

XMATCH関数の論理式を使った位置特定機能と、XLOOKUP関数の柔軟な検索機能を活用することで、複雑なデータ検索も簡単に行えるようになったはずです。

業務で複数の条件に合致するデータを探す場面に遭遇した際は、ぜひこのXMATCHとXLOOKUPの連携テクニックを試してみてください。

さらに、XLOOKUP関数はワイルドカード検索にも対応しているため、部分一致を条件に含める応用も検討できます。

📊

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

🏆

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