Googleスプレッドシートで検索範囲の中に該当の文字列があったらマスタから反映（検索範囲にワイルドカード使用する方法）

Question

前提・実現したいこと

https://teratail.com/questions/92589
https://excelkamiwaza.com/vlookup_area_wildcard.html

上記ページの内容をGoogleスプレッドシートで実現したいです.

確かにExcelだと上記数式で実現できるのですが、スプレットシートだと上手くいきません。

＜例＞
A列 →検索文字列
B列 →マスタから反映された文字列（＝関数をいれる列）
D:E列 →マスタ

　|A列|B列|C列|D列|E列|
|:--|:--:||:--:--:|
|北海道旭川市|北海道||北海道|北海道|
|東京都品川区|関東||青森|東北|
|福岡県中央区|九州||東京|関東道|
||||福岡|九州|

D:Eのマスタを使ってB列に反映させる方法を教えていただきたいです。
（つまり、検索範囲にワイルドカードを使用したいのです）

よろしくお願いいたします。

Answer 1

リンク先のExcelの場合の以下のやり方ですが、

=IFERROR(LOOKUP(0,0/FIND($C$1:$C$11,A2),$D$1:$D$11),"無し")

FINDの挙動がExcleとスプレッドシートだと違うようで確かに動きませんね。
期待する動きとしてはFINDの結果を配列で返してもらって処理しているようなので、
ARRAYFORMULAを加えて以下のようにしたら動きました。

↓

=IFERROR(VLOOKUP(0,{ARRAYFORMULA(0/FIND($D$1:$D$11,A1)),$E$1:$E$11},2,FALSE),"無し")

なぜかLOOKUPだとうまくいかずだったので、E列を横に結合させた範囲をVLOOKUPで探してます。
こちらでいかがでしょうか？

余談ですが、こういのも まとめて ARRAYFORMULAで処理できんかなと色々トライしたことあって、たどり着いたのが以下のようなやり方です。

先にマスタ列の一番左（質問の表だとC列）に1から連番振っときます。
で、今回のケースなら セルB1に以下を入れる

=ARRAYFORMULA(VLOOKUP(MMULT(ARRAYFORMULA(REGEXMATCH(A1:A,TRANSPOSE(D1:D4))*TRANSPOSE(C1:C4)),ARRAYFORMULA(C1:C4^0)),$C$4:$E$4,3,FALSE))

マスタのD列を横にしたような表を作って、縦横で含まれるかを判定（REGEXMATCH使用）。
TRUEになる列番号を配列で抽出して最後に 元のマスタ表と VLOOKUPって処理です。
スキルある人なら、よりスマートな式にできるかもしれません。