いきなり答える備忘録

Google Workspace・Microsoft 365・LibreOfficeなどに関するメモ

(Excel)XMATCH関数で複数条件(AND,OR)を指定する方法

 ExcelのXMATCH関数でAND条件(かつ)やOR条件(または)を指定して値を取得する方法についてです。
 考え方はXLOOKUP関数の例と同じです。

  • XMATCH関数の第2引数(検索範囲)として複数の列の値を&でつないだものを指定すれば、複数の列に対するAND条件にすることができます。
  • 第2引数内で「*」や「+」を使った論理演算を用いて任意のAND/OR条件を指定することができます。

AND条件(かつ)の場合

 次の画像内の左側に2列の表があります。B列に建物名が、C列に部屋番号が記録されていますが、どちらか一方の値だけでは該当する行の位置を特定できず、両方の値が揃ってはじめて特定できるようになっています。
 一方でE列からG列は検索欄で、条件としてE3セルには建物名が、F3セルには部屋番号が入力されています。
 そしてG3セルではXMATCH関数を使って、両方の値に該当する行の位置(B3:C8の範囲内で上から5番目の行が該当するので「5」)を取得しています。

 G3セル

=XMATCH(E3&F3,B3:B8&C3:C8)

 第1引数(検索値)を「E3&F3」とし、建物名と部屋番号の2つの値を連結した1つの値を指定しています。
 第2引数(検索範囲)も「B3:B8&C3:C8」と、2つの列を連結したものを指定します。これにより建物名と部屋番号がE3,F3セルの値と(両方とも)一致する行の位置を取得できます。


 注意点として、この方法だと「建物名が『A』で部屋番号が『AA』の行」と、「建物名が『AA』で部屋番号が『A』の行」を区別できないという問題があります(どちらも連結すると「AAA」になるため)。正直あまり気にする必要もないと思いますが、そのような事態を防ぎたい場合は次のような式にするのが1つの方法です。

=XMATCH(E3&"_"&F3,B3:B8&"_"&C3:C8)

 


 次の画像はもう1つのやり方によるものです。
 G3セルに上記とは異なる式を入力して、同じ結果(5)を得ています。

 G3セル

=XMATCH(1,(B3:B8=E3)*(C3:C8=F3))

 第2引数にて2つの条件を「*」演算子で連結することで、両方の条件に該当する行を「1」、そうでない行を「0」に変換しています。そこから第1引数の「1」を検索することで、該当する行の位置を取得しています。

OR条件(または)の場合

 指定した条件のいずれかを満たすものの位置を取得する例です。
 次の画像のデータは上記の例と全く同じものですが、こちらでは指定した建物名と部屋番号のうちどちらかを満たすもの(のうち最も上にあるもの)の位置を取得しています。

 G3セル

=XMATCH(1,SIGN((B3:B8=E3)+(C3:C8=F3)))

 こちらでは「+」を使ってOR条件による判定を行っています。両方の条件に該当する行は「2」、一方のみに該当する行は「1」、どちらにも該当しない行については「0」に変換されます。このようにOR条件の場合は2以上の値になることがあるので1以上の値はすべてSIGN関数で「1」に変換し、そこから「1」を検索して対応する行の位置を取得しています。
 いずれかの条件に該当する行は4つありますが、XMATCHの仕様により最も上にある行の位置(2)が取得されています。