excelの条件付き書式でこれをやりたい！！！

質問

すみません、助けてください。

Excelの条件付き書式で（マクロとかではなくて条件付き書式で）やりたいことがありますが、
成功しません。お知恵を借りたいので、どうぞよろしくお願いします。

図の　B7：B10　のように、西崎、田中、鈴木、木村　という人の名前が入っています。
B13：C17に、営業部は誰と誰、人事部は誰と誰、女性は誰と誰…というように、所属人員表があります。
お気づきのように、営業部の西崎は営業部の区分にも名前があり、女性なので女性の区分にも入っています。

やりたいことは、
B3に営業部という区分を入力したとき、B13：C17の所属人員表によれば営業部なので西崎と鈴木が該当。B7：B10にあるリスト中の該当者は、条件付き書式で塗りつぶしがかかる。。。
女性という区分を入力したのなら、それに該当する西崎,鈴木木村,に塗りつぶしがか。。。
ということをやりたいのです。
（B3は、入力規則を使ってリスト選択できるようにしようと思っています）

条件付き書式を「数式で」にし、関数などを駆使してこれを実現したいのですが、
うまくできません。

条件付き書式内で、下記のような数式を書いてみました。
=$B7=VLOOKUP($B$3,$B$14:$C$17,2)
こうすると、B13：C17の所属人員表において、所属人員が１名ならば成功します。
しかし、ここでは複数人いるため、ワイルドカード「*」を使えないか？と思ってみましたが、

=*$B7*=VLOOKUP($B$3,$B$14:$C$17,2)
="*"＆$B7＆"*"=VLOOKUP($B$3,$B$14:$C$17,2)
=("*"&$B7&"*")=VLOOKUP($B$3,$B$14:$C$17,2)

こんな風に書いてみても、うまくいっていません。

VLOOKUPにこだわらないので、ほかの関数でできるとか、
所属人員を１セルにカンマ区切りで全員書くのではなく、右方向（D列やE列）に一名ずつセルを分けて書いてもいいです。

どうにか成功する知恵を授けてください。
よろしくお願いします…

回答No.1

Cupper-2 · ベストアンサー

確認する手順を整理してみましょう。

いきなり条件付き書式に関数式を書くのではなく、
空いているセルに関数式を書いて条件によって正しく
　ＴＲＵＥ　または　ＦＡＬＳＥ
になることを確認してから条件付き書式として書き込むと良いでしょう。
（基本的にセルに書き込む関数式がそのまま使えます）

ＶＬＯＯＫＵＰ関数で「営業」とか「女性」の欄に該当する「氏名一覧」を検索するのは大丈夫ですね。
検索できたら、その「氏名一覧」の中に「該当する名前があるか」を調べましょう。
　※質問者さんはこれができていないんですね。
文字列の中から特定の文字列を探すには・・・ＳＥＡＲＣＨ関数を使えばＯＫ。
ＶＬＯＯＫＵＰ関数まではＯＫですので、これにＳＥＡＲＣＨ関数を組み合わせて

　　=SEARCH($B7,VLOOKUP($B$3,$B$14:$C$17,2,0),1)

とでもしてみましょう。
どこか空いてるセルにコピペして動作を確認してみてください。
（当方、Ｅｘｃｅｌを起動できる環境にありませんｗ）
　　条件に合えば１以上の数値
　　合わなければエラー
が返ってきます。
エラーの場合、条件付き書式では条件不成立として扱われるので深く考えなくてＯＫ。
コレで良ければそのまま条件付き書式の数式に入れちゃいましょう。

ＶＬＯＯＫＵＰ関数で
　VLOOKUP($B$3,$B$14:$C$17,2,0)
と最後に「，０」を追加していますが、
これは昇順に並んでいない不特定の文字列を検索しているので検索条件にＦＡＬＳＥを設定するためです。
数値や昇順に並んでいる文字列ならＴＲＵＥ（１）でも良いんですけどね。
でもって、この検索条件を省略するとＴＲＵＥとして検索しちゃうんです。
以外と見落としがちなんです。これ。

お礼

早くの回答、ありがとうございます。!(^^)!

アドバイスいただいた
=SEARCH($B7,VLOOKUP($B$3,$B$14:$C$17,2,0),1)
という式で、一致すれば１以上が返ってくるのが確認できました。( *´艸｀)すごいです！！

条件付き書式はB7からの人名リストに適用するので、
=SEARCH($B7,VLOOKUP($B$3,$B$14:$C$17,2,0),1)
だけだと人名と1が一致しないのでできませんから、
返ってくる値が0以上なら一致したとみなす、という形にするため、

=0<SEARCH($B6,VLOOKUP($B$3,$B$12:$C$15,2,0),1)

このようにしました。
サーチは知っていましたが、VLOOKUPにこだわりすぎていたのか、思いつきませんでした・・・・
本当にとっても助かりました！！
ベストアンサーにさせていただきます(^_-)-☆
ありがとうございました～～