GAS 別シートを参照し結果を抜き出すには

Question

検索しても条件にあう内容が見つけれなかったので質問させていただきました。

下記のシート１はフォームから入力され、シート１の名前に入力された内容をシート２から探し、一致があればシート２のB列部分を抜き出し、一致がなければ「一致なし」とシート１のC列に書き出す方法がわかりません。

スプレッドシート の関数を使って似たような動作は可能ですが、フォーム入力をした際に新しい行が追加された関係か、C列に関数が反映されなかったため、GASを使って同様の操作を行えないかと思い質問いたしました。

（シート1）フォームから入力される

名前	個数	種類
リンゴ	2
さんま	1
みかん	5
きゅうり	1
白菜	2
レタス	２
マグロ	２
鯖	２

（シート2）

名前	種類
リンゴ	果物
さんま	魚
みかん	果物
きゅうり	野菜
白菜	野菜
レタス	野菜
マグロ	魚
サーモン	魚

（シート1）結果

名前	個数	種類
リンゴ	2	果物
さんま	1	魚
みかん	5	果物
きゅうり	1	野菜
白菜	2	野菜
レタス	２	野菜
マグロ	２	魚
鯖	２	一致なし

Comments

[sawa]

別の質問でも指摘されてたかと思いますが、javaは関係ないのでタグを外しましょう。GASはjavascriptをベースとしてますが、javaとjavascriptはまったく別物です。

で、ご質問の件は関数で解決できそうなので、関数だとどうやったかを追記ください。

[nezumimuzen]

Sawa様
回答ありがとうございます。
教えていただいたjavaとjavascriptの指摘について、今に至るまで違うものと認識しておらず誤解を生むようなタグづけをしてしまいました。
申し訳ありません。

関数上で動かす際ですが、シート1のC2部分に下記の関数を入れ下までコピーすることで似たような動作をさせることはできました。

=IF(COUNTIF('シート2'!A2:A,D2)>0,'シート2'!B2,"一致なし")

本当はarrayformula関数でいっぺんに下まで書き出したかったのですが、arrayformula関数はCOUNTIFなどの範囲を示すものでは使えないため下までコピーをするという荒技をすることとなりました。
GASもしくはGASを使わずにスプレッドシート関数上で解決ができる方法が思い浮かばないため、ご存知でしたら是非ご教授願います。

Answer 1

arrayformula を使うってのは考え方としてはあってます。
ただ使ってる式が countif では、「一致があればシート２のB列部分を抜き出し」になりません。

提示されてるサンプルだと、シート1の鯖の下にサーモンがきても、種類の欄に「魚」は表示されず空欄となるかと思います。

あと、このケースなら countifの条件部分を動かせばいいので、arrayformula 使えますよ。

=ARRAYFORMULA(IF(COUNTIF('シート2'!$A$2:$A,D2:D)>0,'シート2'!B2:B,"一致なし"))

ただ書いた通り、COUNTIFでは条件に一致した際に B列を返すみたいなことはできません。

関数で普通に検索、一致を使うならVLOOKUPです。見つからなかった時はエラーとなるので、IFERRORで一致なしを返すのと、IFでD列が空欄なら空という処理を合わせれば良いです。
式を入れたいのはF列でしょうか？ それならF2に以下の式を入れると希望の動きになるかと。

=ARRAYFORMULA(IF(D2:D="",,IFERROR(VLOOKUP(D2:D,'シート2'!$A$1:$B,2,FALSE),"一致なし")))

勉強のためにGASでやりたいという場合は、フォーム送信時をトリガーとして indexOf()あたりで 該当するインデックスを取得して B列の値を返すような記述をする形になります。（もしくは filterあたりを使う）

Comments

[nezumimuzen]

Sawa様

回答ありがとうございました。
IFERRORで一致なしを返すという発想はなかったため目から鱗でした。
大変助かりました！！