Excel関数で文字列の一部を入力して、

Ａ1セルに『港区』と入力するとします。

別シートにある住所一覧から『港区』を含む住所を【複数抽出】して
Ｂ1～のセルに合致した住所一覧を表示するにはどのようにしたらできますか？

・住所一覧は、あらかじめ『区』で分けた列を作らず
　　　　　　　　　　　　『東京都港区台場1-○○-○』の様な形で管理。
・検索結果は、1つでも２０個以上でも表示したい。
・マクロは使いたくない。
・フィルタのこまごました作業は極力避けたい。
・入力⇒エンター⇒一覧表示が理想。

つまり、Ａ1セルに入力した住所の一部を含むものを一覧表示したい・・・という事です。

宜しく御願い致します。

No.1 回答者： CoalTar 回答日時： 2017/06/03 16:09

別シート：Sheet2、1行目はタイトル行として

Sheet2!B1セルに 0
B2セルに =SUM(FIND(Sheet1!$A$1,A2&Sheet1!$A$1)<=LEN(A2),INDEX(B:B,ROW()-1))
フィルハンドルダブルクリック

Sheet1!B1セルに
=IF(ROW()>LOOKUP(7^7,Sheet2!B:B),"",INDEX(Sheet2!A:A,MATCH(ROW()-1,Sheet2!B:B)+1))
下へオートフィル

No.2 回答者： mike_g 回答日時： 2017/06/03 17:44

「フィルタ」嫌いのようなので、「こんな方法もあるよ」という別解です。

「住所一覧」の例が添付図の Sheet2 で、範囲 A1:A9 (あるいは将来のデータ追加を見越して、 A1:A1000 でも構わない)を選択して、[名前ボックス]に『住所』と入力して Enterキーを「エイヤッ！」と叩き付け。

以下は Sheet1 における操作です。
１．貴方の指示通り「Ａ1セルに『港区』と入力」
２．セル B1 に『住所』と入力
３．初回の操作の前にセル B2 に『これ以降適合住所抽出』とでも入力して
￣￣おいてください。(後で自動消去され、それ以降は入力不要です)
４．[データ]→[並べ替えとフィルター <詳細設定>]→[OK]
５．[抽出先]として"指定した範囲"に目玉入れ
￣￣[リスト範囲]ボックス内を『住所』に上書き
￣￣[検索条件範囲]ボックス内にマウスカーソルを移して、範囲 C1:C2 を
￣￣撫で撫で
￣￣[抽出先]ボックス内にマウスカーソルを移して、セル B1 をチョーン
６．クライマックスは、単に[OK]ボタンをツンするだけ

No.3 回答者： tom04 回答日時： 2017/06/03 20:28

こんばんは！

↓の画像のように住所一覧はSheet12にあるとし、Sheet1のB1セル以降に表示するとします。

画像ではSheet1のB1セルに
=IF(A$1="","",IFERROR(INDEX(Sheet2!A$1:A$1000,SMALL(IF(ISNUMBER(FIND(A$1,Sheet2!A$1:A$1000)),ROW(A$1:A$1000)),ROW(A1))),""))

配列数式なので、Ctrl＋Shift＋Enterで確定！
B1セルのフィルハンドルで下へコピーすると
画像のような感じになります。m(_ _)m

No.4 回答者： d-q-t-p 回答日時： 2017/06/05 17:34

データベースクエリを使えばいいでしょう。

「別シート」の A列先頭から住所データがあるとして

まず Microsoft Queryか ODBC接続で適当な接続を作成し
作った接続のコマンド文字列を

SELECT 住所
FROM [別シート$A:A]
WHERE 住所 Is Not Null
AND 住所 Like '%' + ? + '%'

と変更。

パラメータを 任意のセルにして そこに「港区」と入力すれば勝手に
抽出された結果が随時表示されるようになります。