セル内にリストに当てはまる文字列がある場合に文字列の抽出をしたい

A列にデータがあり、D列にリストがあります。
A列のデータには様々な文字列が入っていますが、
D列のリストと一致する文字列があればB列に
その文字列を返したいのですが
色々関数を組み合わせてみたのですが上手くできません。

当てはまる関数を教えてもらえないでしょうか。
ちなみにD列のリストは最大40個程になる予定です。

No.4 回答者： ExcelDevTool 回答日時： 2017/03/29 22:55

下記方法をご参考ください。

http://superdbtool.com/blog/archives/111
?list=UU6a8VJcVsqk-4k2uqPZJ6cg

この回答へのお礼

ありがとうございました！
今後の参考にさせて頂きます。

お礼日時：2017/03/31 15:56

No.3 ベストアンサー 回答者： yuji3690 回答日時： 2017/03/28 21:28

個人的に配列は極力避けたいので、次のような作業列で説明します。

1行目のE列以降にリストを横に並び替えます。
（邪魔であればもっと右でもいいし、別のシートでもいいです）
例えば、E1にりんご、F1にもも…となります。
E1=INDIRECT("D"&COLUMN()-COLUMN($D1)+1)
これで、E1ならCOLUMN()はEが5番目なので5、COLUMN(D1)はDが4番目なので4、5-4+1=2
D&2なのでD2、INDIRECT(D2)となるので、D2のデータが表示されます。
リストの数（つまり40列？）右にコピーしてください。

まず、りんご(E列)との交点に「その行のデータの中に"りんご"が含まれるかを確認し、含まれているなら"りんご"、含まれてなければ""（空白）」を表示します。
E2=IF(COUNTIF($A2,"*"&E$1&"*")=1,$A2,"")
ただし、この場合A2に「りんご飴」とあっても「りんご」が含まれると判定されますが。
それを避けるのであれば、
E2=IF(ISERROR(FIND(","&E$1",",","&$A2&","))=TRUE(),"",E$1)
これはデータの区切りが","で統一されていること前提ですが、
データの前後及びリストの前後に","を加えることによって、前後が","で区切られた状態で、リストに一致するものの検索を行っています。
FINDを使っての検索になりますので、エラーの場合に空白、該当があれば1行目のデータを表示させるようにしています。
これをデータの数に合わせて下へコピーしてください。

一致するリストが複数あった場合の為に、F列以降は少し式を変形させます。
一致に該当せず、左のセルが空白であったなら空白、空白でなかったならそれと同じデータを表示。
一致に該当し、左のセルが空白であったならリストの内容、空白でなかったならそれと同じデータに","とリストの内容を加える。
とします。なのでデータの全てがリストに一致した場合は、右端のセルはデータと同じものになっています。
一致しなかった場合は空白、一部一致した場合はその一致したデータだけ表示されます。
F2=IF(ISERROR(FIND(","&F$1",",","&$A2&","))=TRUE(),IF(E2="","",E2),IF(E2="",F$1,E2&","&F$1))
これをデータとリストの数だけ下＆右にコピーしてください。

40番目の列が何か分からない(数えれば分かりますが…)ので、仮にAZとします。
AZの列には40個のリスト全てにおいて一致した結果が表示されていることになります。
つまり、それをB列に表示させればいいのです。
B2=AZ2
(このAZを該当する最終列の列名に置き換えてください）
これをデータの数だけ下にコピーすれば完了です。

この回答へのお礼

ありがとうございました！
何とかいけました！
私も配列を避けたかったのでベストアンサーとさせて頂きます。

お礼日時：2017/03/31 15:56

No.2 回答者： mike_g 回答日時： 2017/03/28 17:18

冗長過ぎる嫌いはあるけど、次式で如何かと。

B2: =IF(MAX(IF(LEN(A2)-LEN(SUBSTITUTE(A2,D$2:D$99,"")),ROW(D$2:D$99),0)),INDEX(D$1:D$99,MAX(IF(LEN(A2)-LEN(SUBSTITUTE(A2,D$2:D$99,"")),ROW(D$2:D$99),0))),"")
【お断り】上式は必ず配列数式として入力のこと。

この回答へのお礼

ありがとうございました！
今後の参考にさせて頂きます。

お礼日時：2017/03/31 15:55

No.1 回答者： tom04 回答日時： 2017/03/28 16:55

こんにちは！

一例です。
＞D列のリストは最大40個程になる予定です。
というコトですので、やり方だけ・・・

↓の画像のようにD列の「リスト」は空白セルがないようにアスタリクス「*」でつぶしています。
とりあえず「リスト」が10行目までの場合の数式です。

B2セルに
=IF(OR(ISNUMBER(FIND(D$1:D$10,A2))),INDEX(D$1:D$10,SUMPRODUCT(ISNUMBER(FIND(D$1:D$10,A2))*ROW(D$1:D$10))),"")

配列数式ですので、Ctrl＋Shift+Enterで確定し、フィルハンドルで下へコピーしています。

※　A列データに「リスト」は一つしか存在しない！という前提の数式です。
複数存在する場合は別の方法を考える必要があります。m(_ _)m

この回答へのお礼

ありがとうございました！
今後の参考にさせて頂きます。

お礼日時：2017/03/31 15:57