AND条件の一種といえるクロス抽出についても併せて紹介します。
- XLOOKUP関数の第2引数（検索範囲）で複数の列の値を&でつないだものを指定することで、複数の列に対するAND条件にすることができます。
- 第2引数内で「*」や「+」を使った論理演算を用いることで任意のAND/OR条件を指定できます。
- XLOOKUP関数を使ってクロス抽出を行うことができます。
AND条件（かつ）の場合
次の画像の表ではB列の建物名だけでも、C列の部屋番号だけでも入居者を特定できず、両方の値が揃ってはじめて入居者を特定できるようになっています。
一方でF列からH列は検索欄になっており、F3セルには建物名が、G3セルには部屋番号が入力されています。XLOOKUP関数を使って該当する入居者を抽出するにはどうするかというのが問題です。
XLOOKUP関数では検索値が1つしか指定できないのでちょっと厄介ですが、次のようにして抽出できます。
H3セル
=XLOOKUP(F3&G3,B3:B8&C3:C8,D3:D8)
第1引数（検索値）として「F3&G3」と、建物名と部屋番号の2つの値を連結した1つの値を指定します。第2引数（検索範囲）でも「B3:B8&C3:C8」と、2つの列を連結したものを指定します。これで建物名と部屋番号がF3,G3セルの値と（両方とも）一致する入居者を抽出できます。
注意点として、この方法（式）だと「建物名が『A』で部屋番号が『AA』の部屋」と、「建物名が『AA』で部屋番号が『A』の部屋」を区別できません（どちらも連結すると「AAA」になるためです）。意図しない限り起こらないような事態なのであまり気にする必要はありませんが、これを防ぐ場合には次のような式にするのが1つの方法です。
=XLOOKUP(F3&"_"&G3,B3:B8&"_"&C3:C8,D3:D8)
次はもう1つのやり方です。
H3セルに上記とは異なる式を入力して、全く同じ結果を得ています。
H3セル
=XLOOKUP(1,(B3:B8=F3)*(C3:C8=G3),D3:D8)
第2引数で「*」演算子を使い、AND条件による判定を行っています。両方の条件に該当する行を「1」、そうでない行を「0」に変換した配列を生成し、そこから「1」を検索して対応する入居者の氏名を抽出しています。
かなり意表を突く内容ですが論理的に完成されたやり方で、具体的には次のようなメリットがあります。
- 関数が使える（〇文字以上、〇曜日、などを条件にできる）
- 1つの列にAND条件を設定できる
- OR条件（後述）との複合条件を設定できる
- 文字列の結合に頼らないので、意図しない値を偶然抽出すること（上記注意点参照）が絶対にない
OR条件（または）の場合
指定した条件のいずれかを満たすものを抽出する例です。
次の画像のデータは上記の例と全く同じものですが、こちらでは指定した建物名と部屋番号のうちどちらかを満たすもの（のうち最も上にあるもの）を抽出しています。
H3セル
=XLOOKUP(1,SIGN((B3:B8=F3)+(C3:C8=G3)),D3:D8)
こちらでは「+」を使ってOR条件による判定を行っています。両方の条件に該当する行については「2」、一方のみに該当する行については「1」、どちらにも該当しない行については「0」が生成されます。このようにOR条件の場合は2以上の値ができることがあるので1以上はすべてSIGN関数で「1」に変換し、そこから「1」を検索して対応する入居者の氏名を抽出しています。
いずれかの条件に該当する行は4つありますが、XLOOKUPの仕様により最も上にある値が抽出されています。
クロス抽出について
AND条件の一種であるクロス抽出についてです。
次の画像内の表には縦横の2つの見出しがあり、店舗名と月の組み合わせで売上高を特定できるようになっています。
そこでJ3セルにXLOOKUP関数を使った式を入力し、H3セルの店舗名とI3セルの月に対応する売上高を抽出しています。
J3セル
=XLOOKUP(I3,C2:F2,XLOOKUP(H3,B3:B7,C3:F7))
2つのXLOOKUP関数をネスト（入れ子）しています。
まず内側のXLOOKUPで「C3:F7の範囲内の2行目」が抽出されます（B3:B7の範囲内で「池袋店」が2行目にあるから）。これにより「9440,10423,9031,12510」という4つの数値の配列が得られます。
さらにこの結果に対してXLOOKUP関数を適用することにより「9440,10423,9031,12510」の4つの数値の中の3列目が抽出されます（C2:F2の範囲内で「6月」が3列目にあるから）。
4つの数字のうち3列目（3番目）の値は「9031」ですので、最終的にこの値が抽出されます。
文章にすると相当面倒な内容ですが、内側のXLOOKUPだけ実行してみればその先もわかりやすいと思います。
なお次の記事では他の関数を使う方法を含めて、クロス抽出のやり方をいくつか紹介しています。
備考
「*」や「+」を使った条件設定について
「*」や「+」演算子を使ったAND/OR条件の設定は見慣れないと奇妙に思えますが、FILTER関数ではAND/OR抽出をする方法が事実上これしかないため多用されます。
また、SUM/IF関数やFILTER関数と組み合わせて条件付きのカウントや集計にも応用できます。特にOR条件において多重カウント（計上）の心配がなく条件を3つ以上に増やすのも簡単なので、スピルが導入されている現在ではCOUNTIF(S)やSUMIF(S)に頼るよりも圧倒的に有利です。
これらについては次の記事で紹介しています。