QUERY関数の抽出条件にセル（範囲）を指定したい。

Question

実現したいこと

QUERY関数を利用してデータの抽出（フィルタ）を行いたいが、その条件にセルに入力されている値を利用したい。
また、それが可能な場合、複数条件を指定するときに「or」や「and」を利用して羅列するのではなく、セルを範囲指定して条件としたい。

詳細

Googleスプレッドシート上で、あるイベントの参加者データ管理をしており、シート1枚目には過去すべての参加者データ（1000行以上）が、シート2枚目には抽出したい条件(参加者ID)がある。
※参加者IDは個人固有のものであるが、過去複数回イベントに参加している場合は、その回数分データが残っている。

今回、シート2枚目にある参加者IDの過去データをすべて抽出しまとめたい。その際、IDを関数内に直接打ち込まず、IDが入力されているセルを指定したい。

また、その抽出したい参加者IDは縦に100行ほど羅列されており、その範囲を選択し条件とすることで、100件の参加者IDすべての過去データを抽出したい。

該当のソースコード

=QUERY('シート1'!A:O,"where A = '参加者ID①' or A = '参加者ID②' ",1)

このように参加者IDを直接打ち込むのではなく

=QUERY('シート1'!A:O,"where A = 'シート2'!A1 or A = 'シート2'!A2 ",1)

のようにセルを指定することは可能ですか？
また、以下のように

=QUERY('シート1'!A:O,"where A = 'シート2'!A1:A100 ",1)

セルを範囲指定して条件にしたいです。

Comments

[muro_chan]

セルの値を条件とする方法を見つけることはできました。

=QUERY('シート1'!A:O,"select * where A like '%"&'シート2'!A1&"%'")

以下のようにセルを範囲指定できる方法を知りたいです。

=QUERY('シート1'!A:O,"select * where A like '%"&'シート2'!A1:A100&"%'")

Answer 1

下記の要件であるとします。

要件

・シート2のA1～A100の範囲に、抽出したい参加者IDを書く（複数）
・シート1のA列から、上記抽出条件に該当（完全一致）する参加者IDをすべて探し出し、
参加者IDが一致している行のA列～O列を、数式を書いた部分以降に展開したい。
・数式の記入セル（＝結果の展開先）は、シート1ともシート2とも異なる、別のシートとする。（検索範囲と展開先範囲が重複するとエラーになるため）
・参加者IDは、シート2に書いた抽出条件、シート1にある元データともに型が同一であること。（一方が数字形式でもう一方が文字列形式とかではない）

数式

質問に書いてあるような条件指定は、QUERYではできません。代わりに、FILTERとLAMBDAとMATCHを使用します。

上記の前提の場合、下記のような数式で質問に書いてある要件は満たせると思います。

=FILTER('シート1'!A:O, BYROW('シート1'!A:A,LAMBDA(x, MATCH(x, 'シート2'!A1:A100, 0))))

一般化すると下記のようになります

=FILTER(表示する元データの範囲, BYROW(元データの抽出条件比較先列,LAMBDA(x, MATCH(x, 抽出条件（複数）の記入範囲, 0))))

注意：この場合、QUERYと異なり、先頭のラベル（1行目）は自動で出ません。
ですので、もしラベルを表示したければ、1行目のラベルはシート1からコピーし、上記の数式は2行目に入力してください

（なお、上記のFILTERとLAMBDAとMATCHによって出力された結果範囲を、さらに QUERY関数で加工することは、もちろん可能です）

Answer 2

強引な方法です

where A = '1' or A = '2' or A = '3'

みたいな文字列を生成させます

=QUERY('シート1'!A:O, "where A = '" & TEXTJOIN("' or A = '",true, 'シート2'!A:A) & "'")

結果は同じですが、重複を排除するなら

=QUERY('シート1'!A:O, "where A = '" & TEXTJOIN("' or A = '",true, unique('シート2'!A:A)) & "'")

Answer 3

FILTER関数を使ったシンプルな方法

=FILTER('シート1'!A:O, COUNTIF('シート2'!A:A, 'シート1'!A:A)>0)

このような方法もありました

=query({'シート1'!A:O, ARRAYFORMULA(XLOOKUP('シート1'!A:A,'シート2'!A:A,'シート2'!A:A))},"where Col16 is not null")

{'シート1'!A:O, ARRAYFORMULA(XLOOKUP('シート1'!A:A,'シート2'!A:A,'シート2'!A:A))} の部分については、
以下の解説を参照ください
https://support.google.com/docs/answer/6208276?hl=ja
https://qiita.com/sakaimo/items/b572b2e35a7f72fa710b