エクセルで、複数ヒットする条件を一覧表示する関数

質問

調べてもいまいち私の条件にあうサイトが見つからなかったので質問させて頂きます。

エクセルにて例えばＡ列1～100行にお菓子の名前が並んでいます。
Ｂ1セルにお菓子の名前を入力する事で、Ａ列から条件にヒットする名前をピックアップしてＣ列に表示させたいのですが、
そのお菓子の名前の一部を入力する事で、その条件に当てはまるお菓子を一覧表示したいのです。

つまり例えばＢ1セルに「ポテチ」と入力しても、Ａ列には「ポテチのりしお」「ポテチこんそめ」「ポテチバター味」などが該当するので、それらを全て表示したいです。

この条件を満たす様にするには、Ｃ列にはどう関数を入力すれば宜しいでしょうか。

回答No.4

bunjii · ベストアンサー

基本的には回答No.3と同じですが配列数式（Ctrl+Shift+Enterの打鍵が必要）にならないようにする方法として次の数式を提言します。
C1=IFERROR(INDEX(A:A,LARGE(INDEX((FIND(B$1,A$1:A$100&B$1)<LEN(A$1:A$100))*ROW(A$1:A$100),0),COUNTIF(A$1:A$100,"*"&B$1&"*")-ROW()+1)),"")
または
C1=IF(COUNTIF(A$1:A$100,"*"&B$1&"*")<ROW(),"",INDEX(A:A,LARGE(INDEX((FIND(B$1,A$1:A$100&B$1)<LEN(A$1:A$100))*ROW(A$1:A$100),0),COUNTIF(A$1:A$100,"*"&B$1&"*")-ROW()+1)))
C1セルを下へ必要数コピーします。

お礼

早いコメントありがとうございました！一番希望にそった答えだったのでベストアンサーとさせて頂きます。

回答No.3

msMike

添付図参照
C1: =IFERROR(INDEX(A$1:A$100,SMALL(IFERROR((FIND(B$1,A$1:A$100)>0)*ROW(A$1:A$100),""),ROW(A1))),"")
【お断り】上式は必ず配列数式として入力のこと

お礼

早いコメントありがとうございました！私の説明足らずが原因なのですが、一番希望にそった答えとしてNo4さんをベストアンサーとさせて頂きます。

回答No.2

aokii

C1セルに以下の式を入れて下にドラッグコピーしてみてください。
=IF(IFERROR(FIND($B$1,A1),"")="","",A1)

お礼

早いコメントありがとうございました！私の説明足らずが原因なのですが、一番希望にそった答えとしてNo4さんをベストアンサーとさせて頂きます。

回答No.1

k-josui

C1に=IF(COUNTIF(A1,"*"&$B$1&"*"),A1,"")
これを一番下までドラッグ。
この場合「ポテチ」が前でも後ろでもヒットします。

お礼

早いコメントありがとうございました！私の説明足らずが原因なのですが、一番希望にそった答えとしてNo4さんをベストアンサーとさせて頂きます。