検索範囲内に、完全一致する検索値が含まれる場合に特定の文字列を返す方法を教えてください。 Excel

検索範囲内に、完全一致する検索値が含まれる場合に特定の文字列を返す方法を教えてください。

Excel2016使用です。

画像のようなデータがあります。

セルC列を検索範囲
セルE列を検索値として、
C列の中にE列が完全に含まれる場合に
D列をA列に返したいです。

INDEX関数+MATCH関数を使ってみましたがうまくいできませんでした。。

VBA、マクロは使ったことがないので関数でのやり方を教えてください。

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

• 重複データがあると一番上の情報を返してしまうことが分かりました。
  ご回答いただきましたお二方、ありがとうございます。
  
  補足というか追加させてください。
  
  C列の中にE列の文字が含まれる場合、
  全ての結果を横並びでD列の内容を返す方法はありますか？
  結果を返す列はA列ではなく、一番右側で問題ありません。

    補足日時：2021/04/01 23:30

• お二方とも、お時間いただきましてありがとうございました。

    補足日時：2021/04/02 23:15

No.6 ベストアンサー 回答者： fujillin 回答日時： 2021/04/02 23:01

No5です

＞ROW関数の中にあるE50も絶対参照でしょうか？
たびたびごめんなさい。再度ミスをしてしまいました。

ご指摘の通り、「E$3:E50」は E$3:E$50 または $E$3:$E$50 が正ですね。
（Row参照のみなので、ここに関しては列はどちらでもOKです）

この回答へのお礼

できました！！
膨大なデータ量なのでものすごく時間がかかりましたが完全しました。
本当に助かりました！
ありがとうございました。

お礼日時：2021/04/02 23:14

No.5 回答者： fujillin 回答日時： 2021/04/02 21:58

No4です

失礼しました。
No4の式は横方向へのフィルコピーで列がずれるのを制御していませんでした。（絶対参照をわすれてました）

すでにお気づきかもしれませんが、一部を絶対参照にして
=IFERROR(INDEX($D:$D,AGGREGATE(15,6,ROW(E$3:E50)/(FIND($E$3:$E$50,$C3)>0)/($E$3:$E$50<>""),COLUMN(A1))),"")
としてください。

この回答へのお礼

ありがとうございます。
試してみました。

ROW関数の中にあるE50も絶対参照でしょうか？

=IFERROR(INDEX($D:$D,AGGREGATE(15,6,ROW(E$3:E$50)/(FIND($E$3:$E$50,$C3)>0)/($E$3:$E$50<>""),COLUMN(A1))),"")

できてきて嬉しいです！

お礼日時：2021/04/02 22:22

No.4 回答者： fujillin 回答日時： 2021/04/02 10:05

No1です。

＞全ての結果を横並びでD列の内容を返す方法はありますか？
ほぼそのままの式で可能です。

横並びにするために、右側の空きセル（例えばH列以降）を使うものと仮定します。
H3セルに
=IFERROR(INDEX(D:D,AGGREGATE(15,6,ROW(E$3:E50)/(FIND(E$3:E$50,C3)>0)/(E$3:E$50<>""),Column(A1))),"")
として、右方、下方にフィルコピーすれば可能です。

ただし、
＞5万件ほどあるデータなのです。
例示の式は配列計算を行っていますので、5万件に拡張するとそれなりの計算負荷になるはずです。

エクセルで使用可能なメモリ量にもよりますが、負荷がかかりすぎる場合は部分ごとに順に計算させたり、作業セルを用いて計算そのものを分割するなどの工夫をなさった方が良いかもしれません。

No.3 回答者： ずんだしらたま 回答日時： 2021/04/02 01:11

重複する最大件数を決めてしまえば、

力技になりますが、関数でもある程度は可能です。

以下、MATCH関数とOFFSET関数で位置を求めて、INDEX関数でコードを表示する例です。

考え方としては、MATCH関数で検索対象の行数を割り出し、次の検索対象は前に割り出した行数の次からを検索範囲にします。
これの繰り返しになります。

①F3にひとつ目の検索結果を表示する式
F3=INDEX(D:D,MATCH(E3&"*",C:C,0))

②G3に2つ目の検索結果を表示する式。
ここからは検索の開始行数をずらしていくので、行番号を指定します。なお、Max行数10の例です。
G3=MATCH(E3&"*",OFFSET(C3,MATCH(E3&"*",C2:C9,0),0,10-MATCH(E3&"*",C:C,0)+1),0)+MATCH(E3&"*",C:C,0)

③以降は、前の式の「MATCH(E3&"*",C:C,0)」の部分に前の式の全てを当て嵌めて式を作ります。
※②の式で既にお分かりかもしれませんが、この方法だと、式はどんどん長くなります。


Excelの関数はその性質上、そのセルに式がセットされていなければ値を表示できません。
また、vbaのようにループを使えないので、数が決まっていないものを個別のセルに表示させるのも苦手です。

重複検出できる（表示を許容できる）個数を予め決めてしまって、「COUNTIF(C:C,E4&"*")」などで重複した本当の数をら一番右の列になどに出力して全て表示できているかの確認をする程度とした方が現実的だと思います。

この回答へのお礼

ありがとうございます。
お教え頂いた数式ですと、E列が含まれるC列の複数検索となってしまいました。C列の中に含まれるE列を複数返したいのです。
VBAが使えるともっと快適に仕事ができそうなのですね。
これを機に勉強しようと思いました。

お礼日時：2021/04/02 22:10

No.2 回答者： ずんだしらたま 回答日時： 2021/04/01 21:20

=IF(E3<>"",IF(ISERROR(VLOOKUP(E3&"*",C:D,2,0)),"",VLOOKUP(E3&"*",C:D,2,0)),"")

但しこの仕様ですと、例えばE3列が「大阪」だけだった場合、その下に「大阪府〜」があったとしても全てD2の「12000267」が検索結果として反映されます。

上のような競合が無いことが保証されていることが前提になります。

この回答へのお礼

ありがとうございます。
分かりづらく申し訳ございません。
A列からC列が１つのデータで、D列とE列が１つのデータなのです。
重複データあると一番上が優先的になるのですね。
補足を入れさせていただきました。
また教えていただけると嬉しいです>_<*

お礼日時：2021/04/01 23:31

No.1 回答者： fujillin 回答日時： 2021/04/01 21:16

こんばんは

範囲がわからないのでテキトーですが・・・
A3セルに
=IFERROR(INDEX(D:D,AGGREGATE(15,6,ROW(E$3:E50)/(FIND(E$3:E$50,C3)>0)/(E$3:E$50<>""),1)),"")
として、フィルコピーとかではいかがでしょうか？

この回答へのお礼

ありがとうございます。
試させていただきました。
5万件ほどあるデータなのです。
部分一致のような検索にすると一番上のデータを返してしまうのですね。

補足を入れさせていただきました。
また教えていただけると嬉しいです>_<*

お礼日時：2021/04/01 23:32