ある文字を含む際に、値を返す数式についてです

大変お世話になっております。
先ずは写真を添付致します。
左の画像（Sheet1）には、商品名（A列）と名前（B列）の記載があります。
右の画像（Sheet2）には、コード（A列）と商品名　(B列）の記載があります。

※左の画像（Sheet1）のA列（商品名）には、元々、商品名の記載がないものとします。

左の画像（Sheet1）のB列（名前）には、右の画像（Sheet2）のA列（コード）かB列（商品名）の一部の単語（ビールやコーヒー、紅茶等）が含まれています（全く含まれていない場合もあります）。
その際、左の画像（Sheet1）のA列（商品名）に、右の画像（Sheet2）のB列（商品名）を反映させる（値を返す）数式か方法を教えて頂けると本当に有難いです。

お手数ですが、ご回答をお待ちしております。
どうぞ宜しくお願い申し上げます。

質問者からの補足コメント

• 皆様、ご回答を下さいまして本当に有り難うございます。今出先のため、改めてコメントをさせて頂きます。補足があります‥。
  
  左の画像（Sheet1）には、名前（B列）に、『オリジナルビール』等、商品名そのものの記載があった場合や、間に空白が無く『560オリジナルビールドイツ』、『560オリジナルビール』なども値を返したい場合には、どのような数式が良いでしょうか‥。
  
  ご回答を頂けると、本当にとても助かります‥。
  大変申し訳ございませんが、どうぞ宜しくお願い申し上げます‥。

    補足日時：2022/08/29 18:18

No.5 ベストアンサー 回答者： chonami 回答日時： 2022/08/30 11:08

頭にコードが入っているなら空白なくても今の式でそのままその3桁を使って商品名が返りませんか？

一応、商品名がそのまま入っている場合の条件を付けたしましたが、頭にコードがなくて商品名＋他の文字列の場合はこの式ではできないと思います。

=IF(COUNTIF(Sheet2!B:B,B2),B2,IFERROR(VLOOKUP(LEFT(B2,3)*1,Sheet2!A:B,2,0),INDEX(Sheet2!B:B,MATCH("*"&MID(B2,1,FIND("　",B2)-1)&"*",Sheet2!B:B,0))))

オリジナルビール→OK
560オリジナルビールドイツ→OK
オリジナルビールドイツ→NG

この回答へのお礼

chinami 様‥!

大変お世話になっております。
お忙しい中、ご連絡を下さいまして本当に有り難うございました‥!

商品名がそのまま入っている場合の条件をご追加頂きまして心より感謝申し上げます‥‼︎

毎回200から300件程データがありますため、非常に楽になりました‥!

ご教授を頂きまして深く感謝申し上げます‼︎

また是非宜しくお願い申し上げます!
貴重なお時間を下さいまして、本当に有り難うございました‥‼︎

お礼日時：2022/08/30 18:04

No.4 回答者： d-q-t-p 回答日時： 2022/08/29 10:45

数式の回答については最初に Excelのバージョンを書かないと回答した

ものが無駄になる可能性があります。

=CONCAT(XLOOKUP("*"&TEXTSPLIT([@名前],"　")&"*",テーブル2[コード]&テーブル2[商品名],テーブル2[商品名],"",2))

この回答へのお礼

ご連絡を頂きまして、本当に有難うございました。
バージョンの記載がなく大変申し訳ございませんでした…。
office-365です。
ご回答を頂きまして、深く感謝致します。
今後共、どうぞ宜しくお願い申し上げます。

お礼日時：2022/08/29 20:47

No.3 回答者： chonami 回答日時： 2022/08/29 10:34

複数該当はないと仮定し、質問文のデータが前提でなら

=IFERROR(VLOOKUP(LEFT(B2,3)*1,Sheet2!A:B,2,0),INDEX(Sheet2!B:B,MATCH("*"&MID(B2,1,FIND("　",B2)-1)&"*",Sheet2!B:B,0)))

で一応出ました。捻りはないですが。
空白の指定は全角です。

この回答へのお礼

大変お世話になっております。
驚く程、簡単に上手くいきました…!
心より感謝申し上げます…!
大変申し訳ございませんが、補足に記載をさせて頂いた、

”左の画像（Sheet1）には、名前（B列）に、『オリジナルビール』等、商品名そのものの記載があった場合や、間に空白が無く『560オリジナルビールドイツ』、『560オリジナルビール』なども値を返したい場合には、どのような数式が良いでしょうか‥。”　についてです…。

chonami様にお時間がありましたら、是非数式のご教授を頂けるととても嬉しい限りです…!

お手隙の際で差支えございません…。
ご連絡を心より楽しみにお待ちしております!
どうぞ宜しくお願い申し上げます!

お礼日時：2022/08/29 20:41

No.2 回答者： d-q-t-p 回答日時： 2022/08/29 10:31

左の表の B列を「テーブル1」 右の表を「テーブル2」としてそれぞれ

クエリとして読み込み（接続のみ）

空のクエリに下記を貼り付け

let
ソース = テーブル1,
検索用列を追加 = Table.AddColumn(
ソース, "検索用", each Text.Split([名前], "　")
),
コードリスト = List.Buffer(テーブル2[コード]),
商品名リスト = List.Buffer(テーブル2[商品名]),
商品名列を追加 = Table.AddColumn(検索用列を追加, "商品名", each [
検索用 = [検索用],
リスト生成 = List.Generate(
()=>[i=0, cd=null],
each [i]<=List.Count(検索用),
each [
i=[i]+1,
kw=検索用{[i]-1},
lp=try List.PositionOf(コードリスト, kw) otherwise -1,
ls=List.Select(商品名リスト, each Text.Contains(_, kw)),
chck=(try List.Count(ls))[HasError],
cd=if lp<0 then
if chck then [cd] else ls{0}
else 商品名リスト{lp}
],
each [cd]
),
結果 = List.Reverse(リスト生成){0}
][結果]),
列の選択 = Table.SelectColumns(商品名列を追加,{"商品名", "名前"})
in
列の選択

この回答へのお礼

d-q-t-p様
ご連絡を頂きまして、本当に有難うございました。
画像も添付頂きまして、申し訳ございません…。
これから、教えて頂いた方法をさせて頂きます!
貴重なお時間を頂戴致しまして、感謝申し上げます!
引き続き、どうぞ宜しくお願い申し上げます。

お礼日時：2022/08/29 20:51

No.1 回答者： d-q-t-p 回答日時： 2022/08/29 09:12

該当するものが複数あったらどうするのですか？