EXCEL表の文字列が対象セル内に含まれるか

質問

シートAのB2の文字列が000-みかん_東京_001とあるとして、
シートBのB2：りんご、B3：みかん、B4：いちご
C2：横浜、C3：東京、C4：名古屋
D2：赤、D3：青、D4：白
という表があるとします。
シートAのB2セルにシートBのB列とC列から両方含まれる行を探し、D列の値を取得したいのですがどのような関数を利用するのが望ましいでしょう？
ちなみに、シートAのB2が調査文字でC2を答えとして、調査対象の行を追加していきたいと思ってます。
お知恵をおかしいただければ幸いです（＾＾）

回答No.3

msMike

SheetA!C2: =IFERROR(INDEX(SheetB!D$1:D$9,MAX((SheetB!B$2:B$9=MID(B2,FIND("-",B2)+1,FIND("_",B2)-FIND("-",B2)-1))*(SheetB!C$2:C$9=MID(B2,FIND("_",B2)+1,FIND("_",B2,FIND("_",B2)+1)-FIND("_",B2)-1))*ROW(A$2:A$9))),"")
【お断り】上式は配列数式として入力してください。

回答No.2

imogasi

やりたいことの仕組みがよくわからない。
シートＢのＢ２：Ｄ４は
Ｂ列　Ｃ列　　Ｄ列
りんご 横浜　 赤
みかん 東京　 青
いちご 名古屋 白
とデータがある。これは動かない、データだよね。
これを使って両方含まれる行を探すのだよね。それはどういう風にするの。
＞シートAのB2セルにシートBのB列とC列から両方含まれる行を探し、D列の値を取得したいのです、とはどうするの。
シートＡのＢ２には、「000-みかん_東京_001とある」（固定）のではないのか。
このシートＡのＢ２には、時間的に順次別のデータを入れ替わり、入力されるということか。
＞B2が調査文字でC2を答えとして、調査対象の行を追加していきたいと
言うのもよくわからない。
＾＾
１例でシートＡのＢ２セルがこうでシートＢのＢ－Ｃ列のデータがこうだから、該当し、だから、こういう風にデータを取るという風に、文章で説明してもらえませんか。
＞シートAのB2が調査文字でC2を答えとして、調査対象の行を追加していきたいと
ということはシートＡのＢ２その次にＢ３その次にＢ４のデータを対象にして、調べて
質問者の望む（赤青白など？）結果をＢ２に対してはＣ２に、Ｂ３に対してはＣ３に答えを出したいということか。これはＣ２の関数式がわかれば、式の複写でＣ３，Ｃ４，Ｃ５にも結果が出ると思う。
シートＢのデータをどう「両方含まれる」と調べるのか。
質問の例ではみかんと東京があるのは第3行らしいのでＤ３の「青」を持ってくるというのか？
シートＡのＢ２には、000-みかん_東京_001のように余分な語句（０００や００１）がのあるのをどうせよというの。
アンダーバーを頼りに「区切り位置」機能でも使ってぶんりする？
区切った結果、「みかん」に当たるものは、第2番目に、「東京」は必ず第3番目にあるのか。？
ーー
原データ３例と、関数で出せればその結果はどうなるのか追加して、書いてもらえないか。

回答No.1

kkkkkm

シートBのD列の前に一列挿入して
=B2&C2
とし、下に必要なだけコピーします。D列が目障りでしたら非表示にします。
元のD列はE列になります。
その後
シートAのC2に
=VLOOKUP(MID(B2,FIND("-",B2,1)+1,FIND("_",B2,FIND("-",B2,1)+1)-FIND("-",B2,1)-1)&MID(B2,FIND("_",B2,FIND("-",B2,1)+1)+1,FIND("_",B2,FIND("_",B2,FIND("-",B2,1)+1)+1)-1-FIND("_",B2,FIND("-",B2,1)+1)),シートB!$D$2:$E$4,2,FALSE)

とするのでいかがでしょう。