エクセルで「含む」を条件にlookupを実行できないでしょうか？

質問

質問させていただきます。

下記例の表のように
Ｂ1に検索ワードを入れ、
その下にその単語を含むものを抽出してデータを返したいと考えています。
また可能であれば近いもの上位3位くらいが一緒に抽出できればと思っています。

Ａ～Ｅは検索窓といった感じで
Ｇ～は検索に使うデータになります。

やはり関数だけでは無理でしょうか？
マクロはまだよくわかりませんのでできれば関数だけでできればうれしいです。

文が悪く分かりづらいとは思いますが、
ご教授よろしくお願いします。

例
　　1　　 2 3 4 5 6
A　検索　
B　相
C　名前　番号 ファックス メアド ・　・
D　相川　TEL FAX MAIL ・　・
E　相沢　TEL FAX MAIL ・　・
F　
G 相川　TEL FAX MAIL ・　・
H　相沢　TEL FAX MAIL ・　・
I　麻生　TEL FAX MAIL ・　・
J　勝木　TEL FAX MAIL ・　・
K　加藤　TEL FAX MAIL ・　・

回答No.2

tom04 · ベストアンサー

こんばんは！
色々方法はあるかと思いますが・・・
一例です。

↓の画像で説明させていただきます。
データ量が多くてもいいように、表示させるセルと元データのセルの列は変えています。

作業用の列として、J列を使わせてもらっています。

まず、J2セルに
=IF($A$2="","",IF(COUNTIF(F2,"*"&$A$2&"*"),ROW(A1),""))
という数式を入れて、オートフィルで下へコピーします。
数式が1000行まで対応できるようにしていますので、1000行くらいまでコピーしても構いません。

そして、データを表示させたいセル
A4セルに
=IF(COUNT($J$2:$J$1000)>=ROW(A1),INDEX($F$2:$F$1000,SMALL($J$2:$J$1000,ROW(A1))),"")

B4セルに
=IF($A4="","",VLOOKUP($A4,$F$2:$I$1000,COLUMN(B1),0))
としてB4セルの数式をメールアドレスの列のD4セルまでオートフィルでコピーします。

最後にA4～D4セルを範囲指定し、D4セルのフィルハンドルでオートフィルで下へコピーすると
画像のような感じになります。

これで、A2セルに検索したい名前の一部でも入力すると
氏名にA2セルの文字を含む全ての人が表示されるはずです。

以上、参考になれば幸いですが、
他に良い方法があれば読み流してくださいね。m(__)m

お礼

求めていた回答はこれです！
大変たすかりました！いろいろ教えていただき本当にありがとうございました！

回答No.4

a987654

関数だけでやってみました。
ただしデータはＡ７から始まり昇順ソートされていることが条件です。
（本当は７行目は空白にしてデータとの区切りをつけたい所ですが
質問に書かれている配置に合わせました。）
質問の書き方は、行と列が紛らわしいので列方向にABC・・・行方向に
1,2,3・・・として欲しい所です。

１．Ａ４セルに
=IF(LEFT(OFFSET(A6,MATCH(A$2&"*",A$7:A$12,),0))=A$2,OFFSET(A6,MATCH(A$2&"*",A$7:A$12,),0),"")

２．Ｂ４セルに
=IF($A4<>"",OFFSET(B6,MATCH($A$2&"*",$A$7:$A$12,),0),"")

３．Ｂ４セルを列方向に必要分コピー

４．Ａ４～ｘ４（ｘ４は最終列を設定して下さい）を５，６行に
　　コピー

これで所要の表示が出ると思います。
ただし７行目からデータになっていることから冒頭で書きました
「７行目は空白にしてデータとの区切りをつけたい所」となります。
いっそ８行挿入をして４．の処理を行えば上位３位と言わず
上位１０位まで表記出来ますが・・・・

お礼

ありがとうございます！
教えていただいた関数でできました！
ただこの式を使うとデータの頭からしか検索してくれないようでした。
含むとはちょっと違うのかぁと思いましたが、
また別の機会に参考にしたいと思います。
助かりました。

回答No.3

noname#204879

[回答番号：No.1この回答へのお礼 ]へのコメント、

》 上の通りやったと思うのですが、、、

　　[抽出範囲]　　→ $A$3:$F$5
とするところを、$A$3:$F$3 と間違えたでしょ？

お礼

ご指摘通りでした！
こんなやりかたもあるんですねぇ
勉強になります。ありがとうございました！

回答No.1

noname#204879

A　　 B　　 C　　　D　　　E　　　F
1　 検索　　　　　　　名前
2　 相　　　　　　　　相*
3　 名前　TEL　 FAX　 MAIL　 OTHa　 OTHb
4　 相川　TEL1　FAX1　MAIL1　OTHa1　OTHb1
5　 相沢　TEL2　FAX2　MAIL2　OTHa2　OTHb2
6
7　 名前　TEL　 FAX　 MAIL　 OTHa　 OTHb
8　 相川　TEL1　FAX1　MAIL1　OTHa1　OTHb1
9　 相沢　TEL2　FAX2　MAIL2　OTHa2　OTHb2
10　麻生　TEL3　FAX3　MAIL3　OTHa3　OTHb3
11　勝木　TEL4　FAX4　MAIL4　OTHa4　OTHb4
12　加藤　TEL5　FAX5　MAIL5　OTHa5　OTHb5
13

１．３行目を７行目にコピー＆ペースト
　　８行目以降が「検索に使うデータになります」
２．セル A3 をセル D1 にコピー＆ペースト
３．セル D2 に式 =A2&"*" を入力
４．次の条件で[データ]→[フィルタ]→[フィルタオプションの設定]を実行
　　[抽出先]　　　→“指定した範囲”
　　[リスト範囲]　→ $A$7:$F$1000
　　[検索条件範囲]→ $D$1:$D$2
　　[抽出範囲]　　→ $A$3:$F$5

お礼

早速のお返事ありがとうございます。

しかし、上の通り実行しましたがうまくいきませんでした。
A7~F12までの「検索に使うデータ」がすべて消えてしまいました。
上の通りやったと思うのですが、、、