検索値に配列を含め、VLOOKUPみたいな事をしたい

タイトルだとやりたい事が上手く説明出来ないため、以下に具体例を使って説明します。
（タブや空白だと綺麗に整形出来ないため、あえてCSV形式で表しました）

[Sheet1]
A列,B列
aaa,1
bbbb,2
cc,3
ddddd,4
eee,5
fff,6

[Sheet2]
A列,B列
aadddddc123,4
bbfffkkkzzz,6
00aaazzvv,1
zaacc1234,3
※Sheet2のB列は数式で表示されているものとします。

[条件]
1. Sheet1-A列の文字列いずれかがSheet2-A列の各セル毎に含まれるか判定する
2. 1.で該当した時のSheet1-B列の値を、Sheet2-B列に反映する

上記条件を満たす、Sheet2-B列に書く数式をご教示ください。
試しに[=VLOOKUP(Sheet1!$A$2:$A$7,Sheet2!A2,1,TRUE]としてみましたが、
[#N/A]とエラーになったり、Sheet2-A列の内容がそのまま表示されたりと、
想定の動きになりません。
データベース関数を使うと、うまく行けそうな気がするのですが・・・。
ちなみに当方はExcel2007ですが、出来れば他バージョンでも使えるように
汎用的な数式にしたいです。

以上、よろしくお願いいたします。

No.3 ベストアンサー 回答者： CoalTar 回答日時： 2015/11/19 15:55

Sheet2!B1セルに

=INDEX(Sheet1!B:B,MATCH(2,MATCH("*"&Sheet1!$A$1:$A$6&"*",A1,0)))
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式。{ }で挟まれる
下へオートフィル

この回答へのお礼

ご回答ありがとうございます。
ご提示の数式を当てはたところ、想定通りに集計出来ましたので、
こちらをベストアンサーとさせて頂きます。

大変助かりました！

お礼日時：2015/11/19 21:04

No.2 回答者： mike_g 回答日時： 2015/11/19 07:56

Sheet2 の文字列が「aaadddddcc123」の場合は、どうする、どうする？

この回答へのお礼

ご回答ありがとうございます。
今回の実データには、検索値が複数含むものは無いため、
そのようなケースは想定しないものとします。

お礼日時：2015/11/19 21:01

No.1 回答者： tom04 回答日時： 2015/11/18 21:09

こんばんは！

VBAだと簡単にできそうですが、
関数での方法をご希望ですよね？

一例です。
↓の画像のように元データ、Sheet1のA列参照範囲に空白セルを作らないようにしておきます。
画像ではまず使用することはないであろうと思われるアスタリクス（*）を入れて参照範囲に空白がないようにしています。

そしてSheet2のB2セルに
=IF(OR(ISNUMBER(FIND(Sheet1!A$1:A$10,A2))),INDEX(Sheet1!B$1:B$10,SUMPRODUCT(ISNUMBER(FIND(Sheet1!A$1:A$10,A2))*ROW(A$1:A$10))),"")
配列数式になりますので、Ctrl＋Shift＋Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　Sheet2のB2セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl＋Shiftキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。

最後にB2セルのフィルハンドルで下へコピー！
これで画像のような感じになります。

※　データ量が増えてもやり方は同じですが、
配列数式ですので極端にデータ量が多い場合はおススメしません。
（3000行程度なら大丈夫だと思います）

※　別案としてVBAになりますが、今回専用の関数（ユーザー定義関数）
をご自身で作る方法もあります。

まずはこの程度で・・・m(_ _)m

この回答へのお礼

ご回答ありがとうございます。
ただ循環参照を含むためか、実際のテーブルに当てはめると
想定の挙動となりませんでした。

お礼日時：2015/11/19 20:59