【Excel】複数の列を参照して、特定の文字列を含む行全体を別シートに抽出する関数

左画面（Book2）のようなシートがあります。

Book2の中で列D・列F・列Iの3列を参照し、別シート（Book4）のA1セルに入力した文字列と一致する文字を含む行全体を、Book4のA3セル以下に抽出するための関数を探しています。

画像の例は、「北海道」という文字列を3列のいずれかに含む行だけを手動で貼り付けたものなのですが、この処理を関数を用いて自動で行いたいのです。

参照したい文字列（北海道）は、3つの列にランダムに含まれている状態です。

元データ（Book2）を更新すれば、別シート（Book4）がリアルタイムに自動で生成されるようにしたいので、関数を使った方法で行いたいと思っています。

ご教示お願い申し上げます。

No.1 ベストアンサー 回答者： tom04 回答日時： 2015/03/18 22:27

こんばんは！

一例です。
元データは「Book2」のSheet1にあり、表示するのは「Book4」のSheet1とします。
Book4・Sheet1のA1セルに検索したい文字列を入力し、そのデータを表示させます。
（今回は画像通り”北海道”と入力しておいてください）

Book2のSheet1のJ列を作業用の列として使用します。
J3セルに
=IF(COUNTIF(D3:H3,[Book4.xlsx]Sheet1!$A$1),ROW(),"")
という数式を入れフィルハンドルでこれ以上データはない！という位まで下へコピーしておきます。

そしてBook4、Sheet1のA4セルに
=IFERROR(INDEX([Book2.xlsx]Sheet1!A:A,SMALL([Book2.xlsx]Sheet1!$J:$J,ROW(A1))),"")
という数式を入れ、列・行方向にフィルハンドルでコピーしてみてください。

※　作業列が目障りであれば遠く離れた列に設けるか
非表示にしておきます。m(_ _)m

この回答へのお礼

tom04さん

早速ご教示いただきありがとうございました。
IFERROR関数を使われているところについて、結果が「#NUM!」となってしまうため、

=IF(ISERROR(INDEX([Book2.xlsx]Sheet1!A:A,SMALL([Book2.xlsx]Sheet1!$J:$J,ROW(A1))),"",INDEX([Book2.xlsx]Sheet1!A:A,SMALL([Book2.xlsx]Sheet1!$J:$J,ROW(A1))))

としたところ成功しました！　非常に助かりました。ありがとうございました!!

お礼日時：2015/03/19 02:28