【GoogleSheets】query関数を使用したデータベースからの抽出を，１行の数式でまとめたい

Question

環境

GoogleSheets
query
arrayformula
concatenate

数式の目的

データをから一部のデータを入力するだけで，
関連・該当するデータを必要なデータをすぐに抽出できることを目的とする

質問により達成したいこと

数式のコピペを減らし，１行への記入でまとめたい
エラーが発生したのでどのように数式を書き換えれば良いか

すでに達成できていること

改めてよろしくお願いいたします．画像に沿ってご相談させてください．

上半分は，質問前の状態にて，達成できた部分を示しています．
'=query($A$6:$C$10, CONCATENATE("select B,C where A like '",F6,"' "))
数式のF6は相対参照で，
database（左グレー部分）から，すぐに必要なデータを抽出できることを目的とし，
黄色のinput列を記入するだけで
output automatically部分のcategoly列，password列が表示されるようになっています

達成するために試したこと

下半分は，質問させていただくことで達成したいことを試験したものです．
arrayformula関数の使用により，前述の達成したいことをもくろみましたが，
図のようにinput列が複数に渡る6,7,8とエラーが発生します．

①G19へのみの記入
②6,7,8,,,,とinput列に入力することで，質問前と同じ状態を達成する

これを達成するために，数式をどのように書き換えればよろしいでしょうか．
お手数おかけしますが，よろしくお願いいたします．

Answer 1

ほぼ gas.engineさんの回答通りで、Query関数はArrayformulaがききません。
QueryやFilterは配列を返す関数なので、どちらかというと条件に合致する複数のカラムを抽出したり、集計する用で、今回のような一意のIDから抽出する場合はvlookupが適していると思います。

• vlookupの最後の引数の部分は TRUEで近似値一致、FALSEで完全一致なので、今回はFALSEを入れるべき（わかりにくいですね)
• arrayformulaと組み合わせると第3引数のreturnする列番号を複数指定可能
• 質問者さんがコメントに書かれている通り ifと組み合わせて範囲の最後を指定しない方法もアリ

ということで、以下のような式が良いと。

=arrayformula(if(F28:F="","", vlookup(F28:F, A28:C,{2,3},false)))

Comments

[Por]

補足いただきありがとうございます．
第3引数についてもありがとうございます．なかなかこの方法は知らなかったのでとても助かります．
またコメントどおりで書けそうで良かったです．

ありがとうございます．

Answer 2

arrayformula関数とquery関数の併用はできないようです。

参考：How to use ARRAYFORMULA with QUERY in Google Sheets

こちらのサイトによれば、代わりにarrayformula関数とvlookup関数の併用を勧めています。

G列
=arrayformula(vlookup($F$10:$F, $A$10:$C,2,true))
H列
=arrayformula(vlookup($F$10:$F, $A$10:$C,3,true))

上記のようにすればとりあえず動きます。
行数はあわせてください。

Comments

[Por]

別の形からも回答いただきありがとうございます．
「データベースから抽出」「①行で」こちらを達成できればそれで大丈夫ですので，非常にありがたいです．

一点ご質問させてください．
inputに敢えて存在しない200のidを入力したところ，
id=10と同じ回答が返ってきました．
10以上のidはすべて10の回答，
おそらくデータベース最下部の回答を引っ張ってくるようです．

こちらはvlookupの性質でしょうか．
こちらがおっしゃっていた「行数を合わせる」という意味でしょうか．
可能であれば
データベースの行数追加を考えなくても良いように，
`=arrayformula(if(F28:F="","", vlookup($F$28:$F, $A$28:$C,2,true))`
の形で書けたらありがたいですが

ご存知でしたでしょうか．

[gas.engine]

1　sawaさんのおっしゃる通り、vlookupの最後の引数はfalseでした。こちらがより適切です。
　trueですと一番近い数になってしまいますね。

2　「行数を合わせる」は、範囲の設定を調整してほしいという意味でした。

3　エラー表示になるところの修正は、ぱっと思いつかなかったのでそのままにしていました。すみません。

4　sawaさんのvlookup関数の第3引数に{2,3}をいれて1回で済ませる方法のほうがきれいにまとまっていますね。あちらを参考になさってください。

[Por]

ありがとうございます．

今回のベストアンサーについては基本軸を先にご提示頂いたgas.engine様につけさせてください．
またgas.engine様からsawa様に，お手数でなければ，高評価をつけていただけたら何よりです．

お二方ありがとうございました．
引き続きよろしくお願い申し上げます．

[gas.engine]

承知しました。
こちらこそ、ありがとうございます。