それぞれエクセルとGoogleスプレッドシートで、XLOOKUP関数に「複数の条件」を指定してデータ抽出する方法をご紹介します。また行と列の2つの条件を指定してクロス集計する方法についても解説しているので、ぜひ参考にしてみてください。
XLOOKUP関数の基本的な使い方やオプション要素、VLOOKUP関数との違いについては「XLOOKUP関数とは？」で解説しています。
XLOOKUP関数に複数条件を指定する方法
XLOOKUP関数で複数の検索範囲と、対応する検索値を指定する方法をご紹介します。Googleスプレッドシートでは、配列を使用していることを明示する必要がある点に注意してください。
エクセルで複数条件を指定する方法
XLOOKUP関数の検索範囲は、単一の行または列と定められています。そのため以下の手順で、それぞれの「検索値」に対応する単一の「検索範囲」を指定してください。
- 複数の検索値をアンパサンド「&」で結合します。例：(E2&F2)
- それぞれの検索値に対応する検索範囲を「&」で結合します。例：(A2:A6&B2:B6)
- 結果を抽出する範囲を指定します。例(C2:C6)
例では検索範囲1は「A」、検索範囲2は「B」という2つの条件を指定しています。
両方の条件を満たすのは3行目のみなので、XLOOKUP関数は結果の範囲から「5」を返します。
Googleスプレッドシートで複数条件を指定する方法
XLOOKUP関数の検索範囲は単一の行または列と定められているため、以下の手順で検索値と検索範囲を指定します。またスプレッドシートの場合、ArrayFormula関数を追加する必要があります。
- 複数の検索値をアンパサンド(&)で結合します。例：(E2&F2)
- それぞれの検索値に対応する検索範囲を「&」で結合し、中括弧({})で囲います。例：({A2:A6&B2:B6})
- 結果を抽出する範囲を指定します。例(C2:C6)
最後に[Ctrl]+[Shift]+[Enter]キーを押すと、先頭にArrayFormula関数が追加されます。
[Enter]キーで確定すると、2つの条件を満たす結果の範囲から「5」が抽出されました。
ArrauFormula関数：配列数式から返された値を複数行または複数列に表示させます。今回のように、非配列関数で配列を使用したい場合にも使えます。
XLOOKUP関数でクロス抽出する方法
XLOOKUP関数で「行と列の2つの条件を指定」する場合、XLOOKUP関数を入れ子します。
例：XLOOKUP(F2, B1:C1, ①XLOOKUP(E2, A2:A5, B2:C5))
例では行が「A」、列が「Y」の2つの条件を満たす値「5」を抽出しています。数式が少し複雑なので、入れ子したXLOOKUP関数を①として考えて見ましょう。
XLOOKUP関数の基本構文：(検索値, 検索範囲, 結果の範囲)
まず①のXLOOKUP(E2, A2:A5, B2:C5)は、検索範囲(A2:A5)から検索値(A)を検索します。
結果の範囲は「B2:C5」なので、抽出されるのはA行の「1, 5」です。
次にXLOOKUP(F2, B1:C1,①)は、検索範囲(B1:C1)から検索値(Y)を検索します。
結果の範囲は①(１, 5)なので、抽出されるのはY列の「5」です。
①の中身をXLOOKUP関数に戻すと、
XLOOKUP(F2, B1:C1, XLOOKUP(E2, A2:A5, B2:C5))になります。
