エクセル IF計算式？でしょうか？

エクセル365　を使用しています。

sheet1の一覧に入力されている都道府県や住所から
sheet2の各都道府県ごとの担当者を表示したいです。

sheet1
Aセル　　　　　　Bセル　　　　　　　Cセル　　　　Ｄセル
新宿区〇〇･･･　　空白　　　　　　　　1　　　　　　田中
空白　　　　　　　北海道〇〇市･･･　　空白　　　　大谷
茨城県　　　　　　　DEF　　　　　　　333　　　　伊藤
空白　　　　　　　空白　　　　　　　　ABC　　　　森山
富山県○○市･･･　　空白　　　　　　　群馬県　　　　
・
・
・

sheet2
Aセル　　Bセル
北海道　　田中
青森県　　佐藤
岩手県　　佐々木
・
・
・
茨城県　　大谷
富山県　　伊藤
群馬県　　森山
・
・
・


sheet1のA～Cセル内に、都道府県のみもしくは住所がバラバラに載っている、住所・都道府県以外の記載もある一覧があります。

sheet2にはAセルに都道府県、Bセルは担当者となっています。

sheet1のA～Cセルの中の都道府県や住所から、各都道府県ごとの担当者をsheet1のDセルに空白を開けずに一覧として表示させたい。

上記の例から
A1セル　→新宿区〇〇･･･　→都道府県がない　市区町村から始まる住所→Dセルに摘出しない
B2セル　→北海道〇〇市･･･　の住所から→sheet2一覧のBセルから北海道担当の田中を→sheet1　Dセルに表示
A3セル　→茨城県のみの表記から→sheet2一覧のBセルから茨城県担当の大谷を→sheet1　Dセルに表示
4行目はA～Cセルに都道府県・住所の表記がない→何も抜き出さない
A5セル　→富山県〇〇市･･･　の住所→C5セル　→群馬県のみの表記→sheet2一覧のBセルから富山県担当の伊藤と、群馬県担当の森山を→sheet1　Dセルに表示(伊藤・森山の順番はどちらが先でもOK)
※4行目は都道府県・住所もないが該当しないから空白とはせず、Dセルに表示する担当者名はDセル内で詰めて表示したい。

といったことがやりたいのですが、どのような式を組めばできるのか分からず･･･
教えて頂けると助かります。
よろしくお願いします。

No.6 回答者： mike_g 回答日時： 2024/08/27 15:27

》 sheet1のA～Cセルの中の都道府県や住所から、

》 各都道府県ごとの担当者をsheet1のDセルに
》 空白を開けずに一覧として表示させたい。

がどうやら全体的なルールを説明したツモリかもだけど、
sheet2なる言葉は登場してないので、「何じゃこりゃｯ？」と戸惑っています。
sheet2の全データがSheet1にも登場する表を再作成しtくれませんか？そうすれば、より明確なルールの説明になるはずだから。

No.5 回答者： mike_g 回答日時： 2024/08/24 11:00

Sheet2 の Sheet1 への照合がシッチャカメッチャカです。

よくもまあｯ！
やり直してください。

No.4 回答者： fujillin 回答日時： 2024/08/23 18:56

No2です。

どうしても、そのままでなさりたいのなら・・

Sheet1のどこかのセルに
　=IF(ISNUMBER(FIND(Sheet2!A1:A47,A1)),Sheet2!B1:B47,"")
を入力すると、A1セルの内容に含まれる都道府県の担当者名が表示されます。
365バージョンの場合は、配列計算されるので47行分スピルされます。
（該当しない県は空白表示、該当するものだけ担当名が表示されます）

このままでは扱いにくいでしょうから、
　=CONCAT( 上記の関数式 )
としておけば、結果は1セルに表示されるようになります。
（該当がない場合は空白表示になります）

上記はNo2の最初の、「1セル対応のIF文」の場合の例です。
これを応用すれば、ご質問の内容は可能であることは理解できると思います。
もっと要領よく行いたいのなら、後半に述べた要領で計算すれば良いでしょう。


※ Sheet2は固定でしょうから、Ａ列の範囲には空白セルは無いものと仮定しています。（空白セルがあると、必ずヒットしてしまいます）
空白セルが含まれていても無視するようにすることは可能ですが、無用に煩雑になるので省略しました。

No.3 回答者： oshienejp 回答日時： 2024/08/23 16:36

行番号振って

ソートしまくって
各列の属性揃えて編集して

ifの話はそれからだ

No.2 回答者： fujillin 回答日時： 2024/08/23 15:54

こんにちは

ご質問にある「IF計算式」でもできると思いますよ。
1行目で言えば、
Ａ1セルの値にSheet2のA列が含まれていたら、Ｂ列の値を返し、なければ空白
と言う式をＣセルまで3回繰り返して、結果を文字列連結すれば可能でしょう。

365環境とのことなので・・・
FILTER関数を使えばもう少しコンパクトになると思いますし、Ａ:C列を連結した文字列からSheet2のＡ列の値を探す方式にすれば、さらにコンパクトにできるでしょう。
（連結の際には適切な区切り文字を入れておいた方が安全です）

最初の方法の場合は、都道府県が重複していると同じ名前を重複して表示する可能性があります。
後者の連結して検索する方法ならそのようなことは起きません。


とは言え、No1様のご指摘にもあるように、あまり良い検索方法とは思えませんので、表の作り方を再考なさった方が宜しそうに思います。

No.1 回答者： angkor_h 回答日時： 2024/08/23 13:54

sheet1というデーターベースの作り方の失敗です。

規則性を持ったデーターベースに作り直したほうが良いです。