【Excel】ワイルドカードで抽出した複数結果を、別表でリストにしたい

あれこれ悪戦苦闘して、うまくいったのでメモしておきます。

（でも検索したらもっとうまいやり方はたくさんあると思う…。）

◇

Excelで、vlookupを使い検索をかけるのは、データ抽出の定番なのだが、ひとつしか抽出できないという難点がある。

この検索キーワードを、ワイルドカードで探し出し、結果が複数あるならば、それをすべてリスト形式で抽出したい。

題名のとおりなのだが、具体的に何をしたかったか、まずご説明しよう！

自分のもっているExcelの児童書リスト。

（味もそっけもないが、☝こういうやつ）

ある児童書があったとして、この児童書リストの中に既にリストアップされているかどうかを検索したいと思った。
例えば、「メアリー・ポピンズ」が既にあるかどうかを調べたい。

そしてメアリー・ポピンズはシリーズものなので、複数冊ある。
その複数冊をすべて、検索で複数結果リストにしたい。と考えた。

遠い昔、会社でやってみて、うまくいった記憶があるのだが、確か調べてみて、COUNTIFとVLOOKUPを組み合わせる方式だったような気がする。

だがその時は、ある程度カウントする検索値は固定で決まっていた。
今回はワイルドカードだ。

◇

試しながらやってみる。
まず、検索文字を入れる枠を「I１」に作り、ちょっとよけてJ列にCOUNTIFを入れた。
これを、ワイルドカードで抽出する。
ワイルドカードは、アスタリスク「*」なのだがこれを「””」でくくり、前後につける。そして「＆」で結ぶ。

ワイルドカード部分＝「"*"&$I$1&"*"」

=COUNTIF(C$2:C2,"*"&$I$1&"*")

ワイルドカードを使って、C列のデータに「メアリ」の文字列があるデータがいくつあるかをカウントする。

…しまった。「メアリ」ではダメだ。
「メリー・ポピンズ」の場合が抽出されていない。
検索を「ポピンズ」に変更する。

うまくいったかもしれない。
さすがに「Mary Poppins」までは検索できないが、これはもう仕方がない。あきらめる。

そして、J列のおとなりのK列で、だぶっている、つまり「４，４，…」となっている、非該当箇所のカウント数を消す。

=IF(J1=J2,"",J1)

これで、カウントされたものだけ、数字が残るはずだっ！

K列を見るかぎり、うまくいっている。
最初のカウントが１じゃなくて０になってしまっているのが気になるけど、結果的に検索できればいいのだから目をつぶる。
（多分もっとスマートなやり方が…略）

これで、K列に数値があるものだけをVLOOKUPで…って、ここで気が付いた。

VLOOKUPは、一番左はじにないと、動いてくれない。
L列に「=C2」などと入れて、C列を表示させることも出来るけど…。

ここは、A列に１行追加。
=IF(J1=J2,"",J1)

リストアップするセルを作る。

M列は、単純に０からはじまってオートフィルで入力しただけ。

N列に
=VLOOKUP(M2,A:C,3,FALSE)
と入力。

できたーっ！！

試しに「トム」で検索。

なぜシートンが検索されたのだろうか？と思ったら、シートンのフルネームは「トムソン・シートン」だった。へぇ～知らなかった。

ここで、見た目にこだわるなら、エラー値を消す。今日はあきらめた。

ちょっと嬉しかったので、ひさびさにnote更新です。